READ carefully!!Quote:
Originally Posted by Bore
Printable View
READ carefully!!Quote:
Originally Posted by Bore
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.Quote:
Originally Posted by Bore
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
Quote:
Originally Posted by Bore
yes that was a typo.. it shoudl have been.. how will index scan aviod sorting?
Could you post the Clustering factors of all indexes?
CBO uses CF while computing selectivity for a table.
Tamil
www.beaconinfotechcorp.com