NUPI and USI

Database
Enthusiast

NUPI and USI

Hey guys,

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!

Thanks!

Pat

Tags (2)
2 REPLIES
Junior Contributor

Re: NUPI and USI

Hi Pat,

the criteria for choosing the PI are in order of importance:

#1 WHERE and even more important JOIN access

#2 distribution

#3 volatility

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.

Dieter

Enthusiast

Re: NUPI and USI

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.