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.
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.
Partition colum is part of PI in this scenario.
I don't think i can share the DDL (company has secured network)