Re: help! cannot understand "leading subset of columns" in index usage

strepxe_at_yahoo.co.uk wrote:
> hi,
> i am preparing for my first ocp exam and am having a little difficulty
> understanding the concept of indexes. the book says:

What book? Is it only pretaining to RBO and not CBO?

> "b-tree indexes can be used if any combination of leading columns of
> the index is used in the SQL statement"

b-tree indices can be used in many ways. range scan, unique look-up, (these two are the "classical" uses) skip scan, Fast full index scan, slow full index scan (I don't recall the real name.)

> it then gives an example of table t1 with index inv_pk on columns c1
> and c2.
>
> table t1 has columns a,b,c1,c2.
>
> it then says the statement: "select count(a) from t1 where c1 = 1;"
> will used the index as it uses a leading subset of columns as will
> "select count(c1) from t1 where c2 = 2;".

Eh, that one might use the index, but it wouldn't be doing so in the classic way. Maybe a skip-scan, or maybe a fast full scan.

> however "select count(a) from t1 where c2 = 1;" will not work as it
> does not use a leading subset.
>
> am i correct in saying from this that the use of the b-tree index will
> depend purely on the order in which the columns were specified when the
> index was created?

No, it will depend on many other thing (statistics, cardinality estimates, join type, etc.), although the order is definitely an important one.

> if i am correct on this can anyone explain why this
> is?

If you need to ask, you need to go review the nature of B*tree indices.

Hint: If the phone book were sorted based on last name, then first name, how useful would this sort order be in finding all the people in it with a first name of Patrick?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB