All Forums Database
Sudhithra 1 post Joined 01/12
03 Jan 2012
PPI is not working with left joins

 

Hi,

Wish you all a very Happy New Year!

I have the following question regarding PPI and Left joins in Teradata.

I am joining tableA and tableB as below. Both the tables are partitioned on the joining column Col1.

Select tableA.col1, tableB.col3
from
tableA
left join tableB
on tableA.col1 =tableB.col1
and tableA.col2 =tableB.col2
where tableA.col1 in ('X','Y','Z');

From the explain, I see the PPI on TableA is used by optimizer where as partition elimination is not happening on tableB

Is there any limitations on PPI if we use left outer joins? Because the same query with the left join replaced by the inner
join, makes use of the partitions in both the tables. But as per our business requirement, the join cannot be made as inner.
Kindly advise. Please also suggest if there is any better way to re-code the query so that partitions are
used in both the tables.

Thanks in Advance!

 

vidya46 4 posts Joined 09/10
22 Apr 2012

do both table A and Table B have the same PI definitions?

abdulmd 5 posts Joined 12/11
05 Dec 2012

Paste the DDL of both tables.

You must sign in to leave a comment.