All Forums Database
sgarlapa 88 posts Joined 03/13
26 Sep 2014
self join of table based on PI is spooling out

Hi,
I've a join requirement between two huge tables which are partitioned on same column and having same PI (composit).
I thought, it would be very much efficient, if i use partition column in where clause and join them on complete PI. But when i see the explain plan it is redistributing based on one column of the join.  and spooling out when i query for more number of partitions.
My question is why it is redistributing when they are joined on compelte PI of both the tables.
 
Thank you
Srilakshmi

dnoeth 4628 posts Joined 11/04
27 Sep 2014

Hi Srilakshmi,
can you show DDL plus query (join conditions) and explain?
To get a direct join on the PI you must also add the partitioning columns to the join, too.

Dieter

sgarlapa 88 posts Joined 03/13
27 Sep 2014

Hi Dieter,
Partition colum is part of PI in this scenario.
I don't think i can share the DDL (company has secured network)
 
Thank you.
Srilakshmi
 

You must sign in to leave a comment.