Not utilizing the PPI....

Database
l_k
Enthusiast

Not utilizing the PPI....

Hi,

The below part of the query is not using the PPI and it's going for the singel partition hash join..Code snippet is geiven below:

INNER JOIN
DP_VALMDM.PARTITION_KEY CBDT
ON
( CBDT.PARTITION_DT
>= TGT.START_DT OR TGT.START_DT IS NULL)
AND
( CBDT.PARTITION_DT
<= TGT.END_DT OR TGT.END_DT IS NULL)
WHERE CBDT.PARTITION_DT_KEY = 8;

In TGT,end_dt is a range PPI.If we hardcode the date as example 2011-04-03' for CBDT.PARTITION_DT then it is going to access only those partitions in the EXPLAIN plan..

Any above code change is required to use PPI for the target table end_dt?

Thanks...
1 REPLY
Junior Contributor

Re: Not utilizing the PPI....

Since it's not an equi-join the best you might get would be an "enhanced by dynamic partition elimination".
But the Ored condition will probably prevent this.

Dieter