Re: index problem: leading subset of columns?

On Tue, 4 Mar 2003 20:53:35 +0200, "Derya OKTAY" <deryaoktay_at_uekae.tubitak.gov.tr> wrote:

>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?
>

no. whatever combination of leading columns has a condition in the where clause consisting of an equality or a range scan (> >= or < <=)

>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?
>

If there is no WHERE clause no indexes will be used, unless an INDEX FAST FULL SCAN is possible and will result in less I/O than a FULL TABLE SCAN.
Using the RBO, a FULL TABLE SCAN will always be used.

You don't need a GROUP BY to use an index, and in the example above an index will never be used
You would need an extra index on the warehouse_id alone.

>Have a good day!
>Regards, Derya.
>
>
>

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Mar 04 2003 - 16:30:32 CST