Secondary Index causes query to peform poorly.

Database
Enthusiast

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
1 REPLY
Enthusiast

Re: Secondary Index causes query to peform poorly.

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