I understand that a Secondary Index is a 2-AMP or an all-AMP operation, and also the subtable that is created and its contents.
However, I am not sure in what scenarios should a secondary index (unique or non-unique) be defined.
I would really appreciate some help here.
Secondary indexes are typically defined to improve performance with regard to residual conditions in the where clause. There are multiple ways to improve performance of these conditions, including but not limited to row level paritioning, secondary indexes etc...
Secondary indexes will ONLY be used by the opitmizer if they are very selective, nearly (>95%) selective. You can find out if the SI is being used by examining the explain plan of the query (ies) that you are tuning. The first secondary index created on a table will be identified as "index#4", with each subsequent index as a higher mutliple of 4.
One of the more common uses for a Unique Secondary Index arises with the need to maintain uniqueness on a column or columns that are NOT the primary index. The primary index is NOT a primary key. The key function of the PI is to define the most common access path while maintaining acceptable data skew, NOT maintaining uniqueness.
Secondary indexes are mostly used to improve acces on the queries that use a non PI column in search conditions.
Following are some common uses of SI:
I am just curious to know more about your last comment (may be what I have interpreted is wrong).
"NUSI in a PPI table to make a PI access single amp" - would you kindly elaborate more.
As per my understanding, if it is a NUPI table with PPI and we run SQL only with PI column equality check in WHERE predicate it will end up in 1 AMP operation but will scan all partitions. Can NUSI improve this?
Yes, PI will lead you to single AMP but will probe all the partitions. Thats why it is recomended to use NUSI consisting of same solumns as NUPI on top of PPI. and when this NUSI is access via where clause it will avoid scanning all the partitions and will lead you directly to the desired row.
Some considerations for NUSI over PPI are:
Please let me know if you need further guidance :)
Thanks for your quick reply. But when I am trying the same in my TD DB I am getting a different result. I am not able to understand why is it different than the desired result.
Can you please confirm how much data do you have in this table?
The optimizer will always go for the most optimal plan, if it finds that scanning all partitions is more efficient than using NUSI, it will go for PI access scanning all partitions.
The final decision is made by the optimizer :)
Can you please share the demographics of this table?
Here is the detail.
1. There are 12499 data in the table.
2. The typical rows / value for TRANSACTION_DT is 3000
3. Even though it is defined as MULTISET NUPI but the TRANS_ID field contains all unique value
Do you need any other demographic information?
I think you are right, optimizer is deciding to go for 1-AMP all partition scan for this data volume, if not any other reason behind this.
You've partitioned the table on Transaction_Dt, but the query is asking for Trans_id. Regardless of the demographics, it will never give you DPE with that configuration.
As a test, repartition the table by Trans_id. You've only got 12499 rows according to your most recent post, so you could partition the table for each '1' Trans_id. If you run the same query you will see the explain plan pull from one partition.
If you want to pull the transactions by date, you'll need add the Transaction_Dt column in the partitioning.