db file sequential read - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: db file sequential read

  1. #11
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Quote Originally Posted by Bore
    .....the index is scanned to avoid sorting
    READ carefully!!

  2. #12
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    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.

  3. #13
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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
    Last edited by Bore; 11-21-2006 at 11:21 AM.

  4. #14
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Quote Originally Posted by Bore
    READ carefully!!

    yes that was a typo.. it shoudl have been.. how will index scan aviod sorting?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #15
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Could you post the Clustering factors of all indexes?

    CBO uses CF while computing selectivity for a table.

    Tamil
    www.beaconinfotechcorp.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width