Conference orarep::nomahs::rdb_60

5111.0. "Completely STUPID index question :-)." by M5::LWILCOX (Chocolate in January!!) Wed Mar 05 1997 16:52

OK, I know this is "indexing 101" but I seem to either have a complete mental
block or simply no mental capability at all right now.  So, laugh, giggle,
and humiliate me if you will, but please answer my question.

Customer has an index, C1, C2, C3, C4, C5 defined on columns 1-5 of his
table.  He does a nymber of selects, varying the WHERE  and sees the
following strategies:

SELECT C1, C2, C3,  FROM TABLE WHERE C1=XX C3=XX  C4=XX
Strategy is INDEXED ONLY RETRIEVAL...[1:1]

SELECT C1, C2, C3 FROM TABLE WHERE C1=XX C2=XX C3=XX 
Strategy is INDEXED ONLY RETRIEVAL...[2:2]

SELECT C1, C2, C3 FROM TABLE WHERE C1=XX C2=CC C3=XX C4=XX
Strategy is INDEXED ONLY RETRIEVAL...[4:4]

SELECT C1, C2, C3 FROM TABLE WHERE C1=XX C2=XX C4=XX
Strategy is INDEXED ONLY RETRIEVAL...[2:2]


Now, I know that the numbers inside the [] represent the low ikey and high
ikey, but what does "low ikey" and "high ikey" mean?  I know, for example,
that if we were doing a WHERE something < or something > we might see a
[0:1] or [1:0] meaning that there is one bound, but not another bound.
But I've completely forgotten what the numbers other than 1 or 0 mean and
how, if at all, those might relate to the number of segments in the index
or the number of index segments used in the query.

So, after you stop rolling on the floor and wipe the tears from your eyes,
please explain.

Thanks so much.

Liz (the sinus infection has REALLY gotten to me!)

>SELECT C1, C2, C3 FROM TABLE WHERE C1=XX C2=XX C3=XX 
>Strategy is INDEXED ONLY RETRIEVAL...[2:2]

i would have expected 3:3, but anyway...

[n:m]

explains how many columns (segments) is used from the index to satify the
query.  This number always represents an unbroken sequence of columns
from the index definition counted from the first column of the index.

n shows the optimizer is using <n> segments for a starting point
m shows the optimizer is using <m> segments for an ending point

all queries only using the "=" will always return the same number of
segments for low and high value.

want a copy of the optimizer guide?

When you perform an EQUALITY we always set the high ikey = low ikey.  i.e. it
represents a very small range of values.  Thus you
get the same number on both sides of the ":".

Ian

>When you perform an EQUALITY we always set the high ikey = low ikey.  i.e. it
>represents a very small range of values.  Thus you
            ^^^^^^^^^^^^^^^^^^
>get the same number on both sides of the ":".

Is this like 2+2=5 ... for very large values of 2 ?

G'day,

Mark.

Thank you all so very much for the answers and for not laughing toooooo loud!

Liz