I have defined PPI on a table.But i am not using PPI in a Where clause,how would be the performance

General
Enthusiast

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

Regards

4 REPLIES
Enthusiast

Re: I have defined PPI on a table.But i am not using PPI in a Where clause,how would be the performance

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.

Enthusiast

Re: I have defined PPI on a table.But i am not using PPI in a Where clause,how would be the performance

Hi raja,

Thank's for your quick response.

Regards,
Purushotham.
Teradata Employee

Re: I have defined PPI on a table.But i am not using PPI in a Where clause,how would be the performance

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.

Thanks

Senior Apprentice

Re: I have defined PPI on a table.But i am not using PPI in a Where clause,how would be the performance

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.