All Forums Database
RGlass 35 posts Joined 09/04
25 Sep 2007
Secondary Index causes query to peform poorly.

Hi all,I have an instance where removing a secondary index from a column used in the where clause of a query is allowing it to run much faster - seconds vs. minutes.The index is on a large + 1TB table.The query has a long in list matching to the indexed column. Could this be the reason?Any ideas?thanks,Tbob

26 Sep 2007

You havent mentioned if its USI or NUSI.USI is always a two AMP operation and in most cases it would be more efficient to access records via USI.The same is not true with NUSI.Sometimes not having updated stats can cause optimiser to go for Secondary index retrieval rather than a FTS (Which could be better if SI has low selectivity)As the records pointed to by the NUSI value increases, the efficiency of a NUSI read decreases.Check if your SI has very low selectivity!!!Regards,Annal T

You must sign in to leave a comment.