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?
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.
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.