PPI is not working with left joins

Database

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!

Tags (2)
2 REPLIES

Re: PPI is not working with left joins

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

Enthusiast

Re: PPI is not working with left joins

Paste the DDL of both tables.