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.
Bore, thanks for detailed explanation. I'm starting to understand, but need to know what is exactly meant by the term "predicate field". In this case, the where clause of the query uses the NGPROJGUID field (first), then the NGUSRFLD2 field (second), and last the NGUSRFLD19 field.
The base table of the view being queried is NGTAGS. This table has the following indexes and the reflect the order of fields in the index - all has NGPROJGUID as the first column in the index:
NGTAGS_PK index - composite (NGPROJGUID, NGID)
NGUSRFLDx_IND - composite (NGPROJGUID, UPPER("NGUSRFLDx")) where x is number 1-30.
There are others indexes on other fields, but I think these are most pertinent and the execution of the query seem to bounce between these.
Krishna - thanks for tip on the histogram. I lack experience with it, could you please elaborate a little on how you think it can help and perhaps what method to use to collect histogram. Thanks.
By predicate field I mean a field included in the where clause predicate ( these fields here as you mentioned are NGPROJGUID NGUSRFLD2 and NGUSRFLD19.
Their order in the where clause should not matter, but the order in the index does.
As you explained, each of your indexes has a leading column of NGPROJGUID, and hence it can be used for the predicate NGPROJGUID='.......' and in addition for the predicates on the other fields in the index. Normally CBO should choose the most selective index and it should choose right, when stats are present.
In this setup I really do not have an explanation why CBO stopped using NGUSRFLD2_IND in favor of NGUSRFLD19_IND
Histograms could really help, but that's not sure. If you have highly skewed data, then histograms are useful. However I would not go with guessing here. Try to find out where the problem is and then solve it.
BTW what is the view definition? maybe the view contains some additional predicate or something which we cannot see