PPI - Secondary Indexes

General
Enthusiast

PPI - Secondary Indexes

Greetings Experts,

Can you please elucidate on the following.

"If you cannot, or have not, defined a secondary index on the primary index, then having fewer partitions is better than having more partitions, whether achieved by means of the table definition itself or by row partition elimination during query processing."

"Delete operations can be nearly instantaneous when the partitioning column set matches the retention policy, there is no secondary index defined on the partitioning column set, and the delete is the last statement in the transaction."

"You can delete all of the rows in a partition if you want to do so. In this case, there is no journaling of rows if no secondary index is defined on the partitioning column set."

How directly does the secondary index affects the number of partitions.

2 REPLIES
Enthusiast

Re: PPI - Secondary Indexes

Hi Cheeli,

Secondary indexes are used sometimes to improve access over a PPI table. When you can not define a SI on PI, for example in case of NUPI, if your query contains condition on partition columns then the PI access will be much faster, else the PI access will analyse all the partitions on an amp. In that case if you define a SI on the column, it will improve the perfomance by dirctly going to the exact partition.

But in general I dont think there is a relation between a SI and PPI in terms of number of partitions.

Khurram
Enthusiast

Re: PPI - Secondary Indexes

Hi Cheeli,

The above statements are the choices you have when in your design consideration. I have not tested this but it is proven as disadvantages of PPI. 

Yes , when partitioning column set matches the retention timeframe , then it is instantaneous.

As you know that tables having SIs  always have subtables.

Also with the coming of column partition, we are having more options to play with data.

Maybe you can try to test with data for below scenarios and put lesser partitions. I can't do since I am in production:

CT abc(..many fields ....)

PRIMARY INDEX (key1)

PARTITION BY (RANGE_N(key2 BETWEEN 10000

AND 1000000

EACH 1000))

UNIQUE INDEX (key1);

Cheers,

Raja