Re: index problem: leading subset of columns?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 6 Mar 2003 22:41:49 +1000
Message-ID: <daG9a.61381$jM5.156654@newsfeeds.bigpond.com>

"Derya OKTAY" <deryaoktay_at_uekae.tubitak.gov.tr> wrote in message news:b47cbg$91a$1_at_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.

Hi Derya,

Ooopps, don't know what happened to question 2 !!

The best way for you to determine the above is to simply try it. In your example, yes an index could be used. Quick test ....

SQL> select max(owner) from bowie1;

MAX(OWNER)

---

XDB Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=6)    1 0 SORT (AGGREGATE)

   2    1     INDEX (FULL SCAN (MIN/MAX)) OF 'BOWIE1_OWNER_IDX' (NON-U
          NIQUE) (Cost=2 Card=69888 Bytes=419328)

Statistics

---

          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        382  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see from the above, Oracle has indeed efficiently made use of the index. The FULL SCAN MIN/MAX scan which simply just reads the appropriate "end" of the index structure. Note the 2 consistent gets, one for the index root block, one for the last index block.

There are a few ifs and buts with all this. For example if the column permits nulls and a NVL nested function was used, then you would be stuffed, or if bind variables were used in conjunction with some arithmetic expressions you might have issues but generally speaking if it "makes sense" for Oracle to use an index, it will do so.

Make sense ?

Cheers

Richard Received on Thu Mar 06 2003 - 06:41:49 CST