Enbarassingly simple b-tree INDEX query

[JMac]

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...?

[slimdave]

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.

[abhaysk]

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.

[JMac]

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.

[JMac]

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.

[JMac]

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.

[slimdave]

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?

[abhaysk]

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

[abhaysk]

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.

[DaPi]

Originally posted by abhaysk
Its indeed documented

QUITE! THE QUESTION WAS "WHERE?"