Re: index problem: leading subset of columns?

From: Derya OKTAY <deryaoktay_at_uekae.tubitak.gov.tr>
Date: Thu, 6 Mar 2003 12:00:28 +0200
Message-ID: <b47cbg$91a$1@list.ege.edu.tr>

Richard thanks for your reply, it is what I need, but what about my 2nd question?

"Question 2: Do we need a group(aggregate) function in the select clause of select statement? When does an index is used without an WHERE clause in a  select statement?"

I think I did not ask my question clearly, rephrasing my question may help: For example we had defined an index on column product_id and issue the following query:

SELECT max(product_id)
FROM oe.inventories;

What happens will our index be used or not?

Regards...
Derya.
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:vbm9a.60859$jM5.156048_at_newsfeeds.bigpond.com...
> "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 Thu Mar 06 2003 - 04:00:28 CST