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

From: <strepxe_at_yahoo.co.uk>
Date: 20 Jun 2005 08:26:01 -0700
Message-ID: <1119281161.736602.279240@g14g2000cwa.googlegroups.com>

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

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

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

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? if i am correct on this can anyone explain why this is? Received on Mon Jun 20 2005 - 10:26:01 CDT