Quote Originally Posted by Bore
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.