Enbarassingly simple b-tree INDEX query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Enbarassingly simple b-tree INDEX query

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Enbarassingly simple b-tree INDEX query

    Ashamed to admit I don't know HOW Oracle actually reads the index.

    Say Primary Key is a NUMBER column.

    Values from 1 to 1,000,0000

    Query says WHERE PK = 0;

    Can Oracle determine from the 1st (top) level of the index that it has no values < 1 and thus not read the index leaf nodes at all. (I think 'Yes' but just want it confirming! )

    Similarly with varchar type indexes...?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's a guess, but i think that oracle would read leaf nodes -- i don't think that the high level states the range of values in the leaves below, just the midpoint of them.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    i don't think that the high level states the range of values in the leaves below, just the midpoint of them.
    Why will branches have just Mid Points?
    If that were the case then with B-Tree Level 2 & having only One Block at the root will have to do an N/2 Block scan for N blocks in the leaf -- PS i am talking about Unique Scan.

    Which it will not.

    for values less than a range it will scan leaf block, but not necessarly for a VAL which dosent exist and greater than max val and very much greater than it.

    Abhay.
    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"

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    for values less than a range it will scan leaf block, but not necessarly for a VAL which dosent exist and greater than max val and very much greater than it.
    So ... you're saying it will know from the root of the index that the value lies outside of the min and max range? Which is fine for numeric/date indexes. Not so for varchar2 indexes (where I presume the leaf nodes have to be scanned).

    I feel better now ... its not such a simple thing after all.

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    for values less than a range it will scan leaf block, but not necessarly for a VAL which dosent exist and greater than max val and very much greater than it.
    So ... you're saying it will know from the root of the index that the value lies outside of the min and max range? Which is fine for numeric/date indexes. Not so for varchar2 indexes (where I presume the leaf nodes have to be scanned).

    I feel better now ... its not such a simple thing after all.

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    for values less than a range it will scan leaf block, but not necessarly for a VAL which dosent exist and greater than max val and very much greater than it.
    So ... you're saying it will know from the root of the index that the value lies outside of the min and max range? Which is fine for numeric/date indexes. Not so for varchar2 indexes (where I presume the leaf nodes have to be scanned).

    I feel better now ... its not such a simple thing after all.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by abhaysk
    Why will branches have just Mid Points?
    If that were the case then with B-Tree Level 2 & having only One Block at the root will have to do an N/2 Block scan for N blocks in the leaf -- PS i am talking about Unique Scan.

    Which it will not.

    for values less than a range it will scan leaf block, but not necessarly for a VAL which dosent exist and greater than max val and very much greater than it.

    Abhay.
    Hey, i said it was a guess. Where is this documented?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    Hey, i said it was a guess. Where is this documented?
    Its indeed documented

    I think u need to RT*M
    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"

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by JMac
    So ... you're saying it will know from the root of the index that the value lies outside of the min and max range? Which is fine for numeric/date indexes. Not so for varchar2 indexes (where I presume the leaf nodes have to be scanned).

    I feel better now ... its not such a simple thing after all.
    I never said From the ROOT coz its always one block and may not fit in the necessary partial Key Vals, but i said/meant branches..this may apply only if B*level > 2..But in any case even for the Val less then Min must always have to scan Leaf Node.

    Abhay.
    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"

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by abhaysk
    Its indeed documented
    QUITE! THE QUESTION WAS "WHERE?"

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