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.

Unnecessary Sorts

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 tuning.

• 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 full-table scans.

• 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.