Composite index

I am confuse about composite index !! What is that said use the leading column and is it true for example if in the join condition column was second in order of the composite index it does not utilize ?? Can you clarify me may be with a real example

Which version?

In 8i, your leading column of a composite index must be utilized in order for your index to be used.

In 9i, this is no longer true.

For example, if you have a table:

Code:

---

CREATE TABLE xyz (
  x number(10),
  y number(10),
  z number(10))

---

And you have an index:

Code:

---

CREATE INDEX xyz_x_y ON xyz(x,y)

---

In Oracle 8i, the following query will NOT use your index:

Code:

---

SELECT x, y, z
FROM xyz
WHERE y = 123;

---

Whereas in 9i, it would.

Jeff we have Oracle 8.0.5 , it's not 8i . What's going on in 8 version? Can you explain?

i oracle 8.0.5 also you need to use the leading column first in the join condition.

cheese
anandkl