NUSI and Explain Plan


NUSI and Explain Plan


I am firing a explain plan query on a table (Item) in TD.The third step of the explain plan reads like:

3) We do an All-AMP RETRIEVE step from DBNAME.Item by way of index # 4 "DBNAME.Item.PartKey=328" is built locally with no resisual conditions into Spool 1 (group_amps), which is built locally on the AMPs.The size of spool 1 is....

Note: A Non-Unique Secondary Index (NUSI) is defined on ParKey column of Item table.

The question is: What does index#4 mean in the explain plan.Does it refer to the NUSI?
How does the optimiser decide the index #? Is it always that NUSI will be assigned index#4?

Please help
Tags (2)
Senior Apprentice

Re: NUSI and Explain Plan

You're right.
index #4 is the internal number of the NUSI on ParKey.
Do a "HELP INDEX Item;" to see those numbers.

The PI is always 1 and SIs are 4,8,12,16,...