When you have a NUPI, it's good to have a USI because this can enforce uniqueness on your NUPI.
Just wanted to ask for a particular scenario where you will have a combination of NUPI and USI?
Was thinking that if you can have a USI, then you might as well define it as UPI so there's no need to create a Secondary Index.
What is the practical application for such combination? Avoiding skewed data if in case you want the rows distributed based on a non-unique column?
Please give me examples, thank you!
the criteria for choosing the PI are in order of importance:
#1 WHERE and even more important JOIN access
The logical Primary Key of a table satisfies two out of three, but often not #1.
In fact, if a PK is not used in WHERE/JOIN it's might not be implemeted as a USI, but enforced during the load process.
Btw, an additinal USI will not avoid skewing.
I think when you have de-normalized and big table (over 2 billions) then order of importance should change in my view. #2 (distribution) does move up. With de-normalized table you will never know for certain which predicates (WHERE) will be used most. Your calculation should consider worst case scenario which is full table scan and well distributed tables will pay off in most situations. In addition with big tables you usually end up with partitioning it with multiple levels and PI access with partitioned table is not useful.