Secondary Index - Access Counts


Secondary Index - Access Counts


After fighting a long battle with system DBAs, we got object logging enabled. This was primarily to assess the usage of NUSIs which were indiscriminately created. I estimated the size occupied by NUSI subtables to be 20% of all our perm space.

Now i can see that there are some counts registered against the indices, i'm perplexed as to whether there is any thumbrule/ metric/ thresholds which can be used to determine if a  nusi is justified.

For eg, if teh table is accessed 2000 times and a particular NUSI only 40 times, then it is actually accessed on ly 2% of the times.  Hence there might be no justification to still have it.

What are the factors that we would need to take into consideration for a NUSI? Would a USI or NUSI ever help in the case of joins(from what i have heard, it apparently does). Please do let me know.