Joins involving Partitioned PIs

Database
Enthusiast

Joins involving Partitioned PIs

Hello Guru's,

I have few queries where in joins one of the col is Partitioned PI and other side of the join is non Partitioned PI.

I understand this is not good for query performance point of view.

What is the impact of that?

How to identify such issue from explain Plan?

Please help me with best way to address/handle this issue?

Thanks!

Tags (2)
5 REPLIES
Enthusiast

Re: Joins involving Partitioned PIs

in the PPI table the rows order with in each AMP is different than the PI table. So even with samp PI join if one of the table is PI and another is PPI it degrades the performance.

If you join on the PPI table you might have given the range or case condition in where clause. In explain plan it shows how many partitions it is considering to pickup the data. If second table is also partitioned it would show the number of partitions. Other wise in the paritioned table with in the involved partitions, it has to scan each record while joining.

In this case generally the PPI table should be huge with where condition on PPI column and Non PPI table should be small in size. in this case having updated stats would help. other wise need to go for modularization approach of deviding the quries based on smaller conditions and involve them in final joins.

Enthusiast

Re: Joins involving Partitioned PIs

If i see PPI column joined to non PPI col.

Whats the effect & how should be resolved?

Enthusiast

Re: Joins involving Partitioned PIs

If non PPI table is also being used very frequently  in such range queries, better propose PPI for that table also.

If range query is occasional and PI usage is more, better live with PPI joined to Non PPI table. Ensure paritions stats are collected.

Enthusiast

Re: Joins involving Partitioned PIs

Not always to possible to change PI to PPI of other tbl. 

In that case, what is the minimum we can do?

Teradata Employee

Re: Joins involving Partitioned PIs

Hello Gian,

For joining between 02 tables using PPI-Non PPI Column, if Teradata use Dynamic Partition Elimination, it will be an indication that the presence of PPI column is being used to select similar "virtual partition" rows of the non-PPI Table. It greatly enhances the joining performance involving PPI Column with non PPI Column.

Kindly refer to the SQL Reference # 02 Guide for understanding the Joining Strategies. Besides Statistics & other user over-sight, Joining Strategy used by Teradata greatly impacts the performance of the query.

Thanks,

Smarak