Composite index

[marist89]

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:

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

And you have an index:

CREATE INDEX xyz_x_y ON xyz(x,y)

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

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

Whereas in 9i, it would.