Hi, I have seen everywhere that VONUSI can be used as a substitute to PPI... But what really confuse me is the way PPI works... sel * from table where column between a and b; parser will calculate the partition number to which it belongs using the partition expression defined for PPI(range_n,case_n or normal one) After that it will be a all AMP operation scanning base table but only that partition.....thus reducing the cost and time involved in full table scan...
But VONUSI are stored as a sub-table on same AMP.....only difference is that rows are stored on value sequence rather than hash sequence of index value
But still it has to scan whole subtable to find out the particular rows as there is no rowkey type thing is here like in PPI... Can anybody explain how it really works(VONUSI)
Assuming you have a value ordered NUSI on column, the optimizer will determine approximately how many rows lie between a and b. If the number is small enough (c 10%), it will pass the NUSI to get the row-ids of all the qualifying rows. Because it is value ordered, as soon as it gets to b, it can give up on the pass. It will then take all the row-ids and use these to retrieve the base rows. Because it is a NUSI, the index subtable is always located on the same AMP as the base table row, so these are always all amp operations.
For a PPI, where column is the partitioning key (or the partitioning key is a function of column), the base table will be split into a number of subtables. The optimizer will take the a and b values, determine which partitions hold these values and scan only those partitions - across all AMPS.
If column can have a wide range of values, and a and b will usually be very selective, you will scan the index subtable fairly quickly to get to the required rows. (The index subtable is small because of restrictions on what data types can be used in a VO index).
If column (or a function of it) results in a small number of values (max number of partitions is 64K), use a PPI. If you have more than a few dozen partitions, and your queries will usually hit a small number of partitions, it will easily perform better than a VONUSI.
If your column is a date or the year/ month (the usual partitioning key) then a and b will be used to determine which partitions to scan. This will usually be a fraction of the data scanned in the index.
There are other advantages to PPI - maintenance overhead when loading is negligible compared to any NUSI and especially an VONUSI, deleting/ inserting new rows like deleting rows from 5 years ago or inserting this months data can have performance benefits. Personally, I would nearly always go for the PPI!