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?