self join of table based on PI is spooling out

Database
Enthusiast

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

2 REPLIES
Senior Apprentice

Re: self join of table based on PI is spooling out

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.

Enthusiast

Re: self join of table based on PI is spooling out

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