I have a query :
sel colw,col2 .. from tab1 inner join tab2
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 ?
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.
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:
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 ?
#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".
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.
Adding some more details... the underlines table in v_dsub is MLPPI
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.