Tuning to remove SQL Sorts

In all cases where an ORDER BY clause is specified in Oracle SQL, the sort operation can be removed with the existence of an index with the same keys as the ORDER BY clause. Unfortunately, the Oracle optimizers may not choose this index to service the query, and unnecessary sorting may occur.

In these cases, an index hint^[1] can be used to force the Oracle SQL to access the table rows with the index. This tuning technique gets tricky when the SQL query is joining several tables. For example, consider the following SQL:

select
   customer_name,
   order_number
from
   customer c,
   order o
where
   c.customer_num = o.customer_num
ORDER BY
   customer_name;

In this case, the ORDER BY clause specifies a different column from the WHERE clause. Now we have a choice. The Oracle optimizer would choose (in most cases) to retrieve customer rows using the customer_num column to facilitate the join operation with the order table. Then the result set would be sorted. The alternative is to force Oracle to access the customer rows in customer_name order. This could be done as follows:

Select /*+  index(cust_name_idx) */
   customer_name,
   order_number
from
   customer c,
   order o
where
   c.customer_num = o.customer_num
ORDER BY
   customer_name;

In this case, Oracle will access the customer rows using the cust_name_idx. However, Oracle would then need to perform a full-table scan against the order table to perform the table join. In short, you would need to weigh the trade-off between a full-table scan and a sort operation.
However, this is only one way to bypass sorting. There are many other ways to avoid SQL sorting, and we will be exploring them in great depth in the fourth course in this series. The next lesson concludes this module.

[1]index hint: A directive made in an SQL statement to force the use of an index when servicing a query.