Re: index problem: leading subset of columns?

"Derya OKTAY" <deryaoktay_at_uekae.tubitak.gov.tr> wrote in message news:b432rd$660$1_at_list.ege.edu.tr...
> Hello, I want to ask questions according to below quoted paragraph?
> ------------
> The OE.INVENTORIES table has the index INVENTORY_PK on the PRODUCT_ID and
> WAREHOUSE_ID columns.
>
> We can use this INVENTORY_PK index with the following query:
> SELECT SUM(wuantity_on_hand)
> FROM oe.inventories
> WHERE warehouse_id=3
> AND product_id=3191;
>
> also we can use this INVENTORY_PK index with the following query:
>
> SELECT SUM(wuantity_on_hand)
> FROM oe.inventories
> WHERE product_id=3;
> ...
>
> We would not be able to use the INVENTORY_PK index if we ran the following
> query to see how many items are in warehouse ID 3, because WAREHOUSE_ID is
> not a leading subset of columns in the index:
>
> SELECT SUM(wuantity_on_hand)
> FROM oe.inventories
> WHERE warehouse_id=3;
>
> --------------
> Question 1: Does leading column refers only one column if a composite
index
> is composed of more than two columns?
>

Hi Derya,

Must admit to not being 100% sure of your question, but if you're asking you have a composite index based an columns a,b,c,d which leading column(s) must be specified for Oracle to consider using the index (in a traditional sense), then the answer is it depends.

Don't you just love it !!

It depends on your version of Oracle, which you haven't specified :(

Pre 9i, the answer is just column a. Provided you have specified column a in a where clause (in a manner by which Oracle can use the index) and if it's appropriate to do so, Oracle can. Column a is the crucial one.

With 9i, column a is still the crucial one, *but* if you have specified say column b only, then Oracle *can and may* consider the index. This is because Oracle can take advantage of "index skip scanning" where it can use the branch blocks in the index to determine if it's possible to have the required value of column b in the corresponding leaf blocks. This could be performed relatively efficiently *if* column a has low(ish) cardinality and so knows that for the same value of column a, it's not possible to have the required value of column b.

You can read about it in my new book "David Bowie and Oracle Indexes, Is There Anything Else To Life ?" coming out soon ;)

Cheers

Richard Received on Wed Mar 05 2003 - 07:57:48 CST