All Forums General
Purushotham 87 posts Joined 04/14
20 Jun 2014
I have defined PPI on a table.But i am not using PPI in a Where clause,how would be the performance

HI TD Experts,
I have defined PPI on a Table.But i am not using PPI in the where clause, am using PI in the where clause.
Whether it performance decreases or not ?
If it decreases the performance, why it decreases? please tell me the reason.
Thanks in advance..

Raja_KT 1246 posts Joined 07/09
21 Jun 2014

You can run the explain and see it.


Partitioning is done to avoid full table scan.Performance is achieved when a restrictive condition on the partitioning column is placed.If you do not specify a partition then it will scan in all partitions.Beauty of Partitioning can be conceivably seen when we have huge amount of data or tables with billions of rows.

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

Purushotham 87 posts Joined 04/14
21 Jun 2014

Hi raja,

Thank's for your quick response.



Sun_shine_jgd 39 posts Joined 07/13
24 Jun 2014

Hi.if you have where clause on the PI then its better not to have a PPI on the table, it will degrade th eperformance.Why.? because for getting the PI value it has to  go through wach and every partitions.
So its always better not to have PPI if ur query is on PI.

dnoeth 4628 posts Joined 11/04
24 Jun 2014

It's not always better, only if the partitioning column is not part of the PI.
And the performance decrease mainly depends on the number of populated partitions, if it's just a few performance might still be ok.


You must sign in to leave a comment.