Oracle sorting when using an index

I have a question about how Oracle decides when to sort the result of a query. Assume you have the following queries, where columns 'a', 'b', and 'c' are in a concatenated index:

1. SELECT a, b, c FROM foo WHERE a = 'X' ORDER BY b, c;
2. SELECT a, b, c FROM foo WHERE a = 'X' ORDER BY a, b, c;

Looking at the EXPLAIN PLAN for both queries gives the following result for BOTH the rule and cost based optimizers.

1. Oracle uses the index to retrieve the rows, and sorts the result.
2. Oracle uses the index to retrieve the rows, but does not sort the rows.

Does anyone know why Oracle behaves like this? I would think that Oracle would be smart enough to know that since you are using an equality in your WHERE clause, the value of column 'a' will always be the same, and since it is using an index, it shouldn't have to sort the rows.

Why does adding column 'a' to the ORDER BY clause cause Oracle to just use the index to get the rows, retrieving them in pre-sorted order? Is there something about the ways Oracle manages indexes that prevent the rows from coming out pre-sorted when column 'a' is left off of the ORDER BY clause?

Thanks in advance,
Brian Biggs
(bbiggs_at_cincom.com) Received on Sun Sep 17 1995 - 00:00:00 CEST