It is taught that both are used for range queries but how to choose between them. Please share your thoughts.
There are different dimensions to consider
1. value ordered NUSI requires additional space and maintenance
2. value ordered NUSI will only be used for selective conditions
3. PPI might impact query performance in case of joins between two tables if one is PPI or MPPI and the other is not.
So the question is not easy to answer and need much more input from your side.
But maybe an example where value ordered NUSI could show positive values.
A transaction table is loaded several times a day and contains some billion rows.
Table contains trx_dt and dwh_job_no.
Analysts query table usually on trx_dt where some different batch processes prepare pre aggregates once a day but independently and these need to use only the deltas which are detected on dwh_job_no. Goal is to get fast access and avoid full table scans.
PPI would be rather defined on trx_dt as this supports many queries for analysts. Range would be more on one partition per month.
MPPI could be added on trx_dt and dwh_job_no but might have some other problems as stated.
Here a value ordered NUSI on dwh_job_no might be sufficient to get fast access to the delta.
There are other designs possible and as stated it will really depend on your detail requirements. So no easy answer possible.
Thanks Ulrich for your information.
So you meant to say that if i am defining a column to be partition column that is being used in the joins , I need to ensure that the other column in join also to be partition?.
OK,let me give you sime example.
There is a transaction table with date column Txn_dt . And i have a calendar table having columns like period_start_date & period_end_date.
My join is always Txn_dt between Period_start_date & period_end_date. In this case, what to do you suggest ? VO NUSI or PPI?
How selective is your condition Txn_dt between Period_start_date & period_end_date?
E.g. what is the % of rows retrieved by this condition?
I can say its highly selective and this condition is accountable to calculate aggregated data across each period, in a sense this condition is going to retrieve almost 100% data .