index problem: leading subset of columns?

From: Derya OKTAY <deryaoktay_at_uekae.tubitak.gov.tr>
Date: Tue, 4 Mar 2003 20:53:35 +0200
Message-ID: <b432rd$660$1@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?

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?

Have a good day!
Regards, Derya. Received on Tue Mar 04 2003 - 12:53:35 CST