Removing Sorts by Adding Indexes
Oracle Tips by Burleson Consulting
One of the best ways to remove disk sorts is
by adding indexes. When operating in first_rows mode, Oracle
will always try to resolve an order by clause by using an
index in lieu of a sort operation. However, you must always remember
that while an index will retrieve the rows far faster than a sort,
the index scan will have less throughput than the sort operation.
As you may know, Oracle will sometimes use an
index-full scan in place of a disk sort operation. As part of the
determination of whether to use an index, the Oracle optimizer
considers the number of rows returned by the query and weighs the
costs of the sort versus the costs of retrieving the rows via the
index. In most cases, Oracle will only consider an index-full scan
in cases where a large number of rows are being retrieved and a disk
sort would be required to satisfy the query.
There are many cases where Oracle performs a
sort operation even though a sort is not required. This generally
happens when one of the following conditions is present:
Missing index Many Remote DBAs are not aware
that a column index is required for a query until they begin SQL
Sort merge join Anytime a sort merge
join is requested, a sort will be performed to join the key
values. In many cases, a nested loop join is a better choice
because it is more efficient and does not require sorting or
Using the distinct clause Using
the select distinct clause on a query will always invoke a
sort to remove the duplicate rows. There are many documented cases
of SQL that has a distinct clause even though there can never be
duplicate rows in the result set.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.