It is hard to explain the behaviour of the optimizer :-) You can only hope it's right :-)
Quote Originally Posted by dbbyleo
Question 1: When I analyzed only the NGTAGS_PK index, which improved response time, did the stats tell Oracle that NGTAGS_PK was not the best index to use, so instead it used NGUSRFLD2_IND?
Cannot be sure about that BUT it could be. Other possibility is that gathering statistics , you unlocled the cost based aproach of the optimizer and it ignored the ..PK index. I see the optimizer_goal is CHOOSE what means RULE in case of statistics lack and COST in case if statistics for at least one object are presented.
Q 2 and Q3 - keep in mind that the order in which the fields are present into the query mather. The index cannot be used for selectivity if the predicate field is not leading column into the index. Hence if NGPROJGUID is leading column in NGUSRFLD6_IND but is not leading in NGUSRFLD2_IND (supposing that NGPROJGUID='6A018CB9-C15B-443b-8487-5C016EB8251B' is most selective predicarte) that could be the reason.
Again, it is really hard to say, this is just a guess. One can hardly explain the CBO even when all the data are available, and here when we do not know the exact index setup, stats etc. it can be really obly a guess