Hello All,
 
Can someone explain on what basis we select indexes for the columns in the field.
Following is my understand:
UPI -> When we have Unique value, which is evenly distributed to all the AMPS. (The column selected for UPI looks like a field Eligible for USI as well. Then how to decide?)

Hi,
In case of a NUSI ordered by ROWHASH the array of row pointers in each NUSI data block contains the orderd ROWID of its rows. Basically (leaving the UNQIUENESS value beside), it is ordered by the ROWHASH.

Hi Experts,
Can you pls  give your suggestions for the below requirement-->

Requirement-->

There is a table which would be having history of 24 months of data at any point of time.

How can we know the space used by an NUSI?

It was a complex Business Objects report with some > 30 joins and instead of coming up with some sort of Join Index recommendation I Wiz is asking me to drop a bunch of NUSI's that are'nt getting used and says it will improve the system performance 15%.

Hi,

Really wonder how these indexes are different, which one to use and where to use. Any help would be greatly appreciated.

Thanks.

Hi,

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 are the sub tables for NUSI created?
What are the columns in these sub tables?

Also NUSI subtables are present in the same AMP in which the data they point to is present(Not so in case of USI ). How is this possible?