Query Tuning:PPI not being used in select

Database
Enthusiast

Query Tuning:PPI not being used in select

Hi,

I have a query :

sel colw,col2 .. from tab1 inner join tab2

on tab1.cola=tab2.colb

and tab1.date1 between tab2.date1 and tab2date2

Here tab1.date1 is a PPI column.

Question : Here although my date1 is a PPI column, partition elimination is not used. This is because my values are coming from join to another table Tab2. If i hard code the value or use current_date, it uses partition elimination.

But is practical scenarios we would get date from another table and not a hard coded value.

So, what is the reason for this and what is an alternate way to code so that PPI is used in these kind of scenarios ?

11 REPLIES
Enthusiast

Re: Query Tuning:PPI not being used in select

Hi,

Can anyone reply on this ?

Thanks

Enthusiast

Re: Query Tuning:PPI not being used in select

the tab2 is a table with just one row containg the date1 and date2 values.

Senior Apprentice

Re: Query Tuning:PPI not being used in select

Partition elimination tries to eliminate partitions which are not used at all from a scan, but for each "tab.cola" there might be another pair of dates in tab2. Thus the best thing you might get is "enhanced by dynamic partition elimination".

You could also try to add

and tab1.date1 between (select min(date1) from tab2) and (select max(date2) from tab2)

and see if this helps.

Dieter

Senior Apprentice

Re: Query Tuning:PPI not being used in select

If it's actually a single row table (doesn't match your join condition: on tab1.cola=tab2.colb and tab1.date1 between tab2.date1 and tab2date2) you can replace it with a view as described here:

http://developer.teradata.com/blog/dnoeth/2011/03/global-and-session-level-parameters-in-sql

Dieter

Enthusiast

Re: Query Tuning:PPI not being used in select

Hi Diether,

Thanks for a quick response.

1. i was not able to understand your first answer fully. ppi is used to help in range queries as in this case. if we replace tab2.date1 and tab2date2 with some finite value either by hard coding or current_date, it uses the elimination. So, why does it use elimination in this case ?

But, in practical scenarios, the value come from another table, as in my eample.

The min and max that you suggested would change the o/p result (correct me if i am wrong here). Not sure if this can be acceptable to business logic.

2. Yeah, my table is a single row table, i understood you view concept. I will try to implement it.

I have two queries to tune one with a general scenario ,case1 ,and other with single row 

3. Additionaly, I tested with a sample table for 2nd approach .With a single row also , using the view approach, it doesn't even give "enhanced by dynamic partition elimination".  Rest all enhancements that you suggested does happen.

Is there not a way to force the optimizer to use partition elimination in these cases ?

Senior Apprentice

Re: Query Tuning:PPI not being used in select

#1: When the query is optimized the actual values are not yet know when they come from another table. Btw, in TD14.10 there's a new feature "Incremental Planning" which will resolve such non-correlated Scalar Subqueries like hard-coded values.

My MIN/MAX subquery will not changed the result as it's supposed to be an additional condition.

#3:  When you use a view as i described you will see hard-coded values within explain, so there's no need for  "enhanced by dynamic partition elimination".

Dieter

Enthusiast

Re: Query Tuning:PPI not being used in select

Hi Dieter,

We are facing a simialr issue... 

SQL contains a table that is MLPPI (multi-level PPI) and being inner and outer joined with other tables. When I use a hard coded value of MLPPI (where cmonth=6) within SQL, it runs faster. When I create a view (say V1_mdata) without cmonth=6 and then run Select * from V1_mdata where cmonth = 6 then query takes too long. From plan, I see that PPI is not used.

Can you please advice, what could be the possible reasons and remifications?

Thanks in advance.

Enthusiast

Re: Query Tuning:PPI not being used in select

Adding some more details... the underlines table in v_dsub is MLPPI

REPLACE  VIEW EBISANDBOX.V_n_fbl_simple

as

SELECT c_code,

       c_month,  

       c_year,

       ,ROW_NUMBER() OVER (PARTITION BY c_code, C_month, C_year) as RN

FROM 

EBISANDBOX.v_dsub A

where C_month = 6

Select * from V_n_fbl_simple -- works fine interms of speed

REPLACE  VIEW EBISANDBOX.V_n_fbl_simple2

as

SELECT c_code,

       c_month,  

       c_year,

       ,ROW_NUMBER() OVER (PARTITION BY c_code, C_month, C_year) as RN

FROM 

EBISANDBOX.v_dsub A


Select * from V_n_fbl_simple where c_month = 6 -- DOESNT works fine, runs very slow

Teradata Employee

Re: Query Tuning:PPI not being used in select

That is because you are asking two different questions. In the first case you are asking for v_dsub to be qualified first before applying the rownumber function is applied. In the second case, the qualification from outside the view is asking for the qualification to be applied after the rownumber function is applied. The two queries will result in different answer sets.