Lesson 1

As a small but very important component of SQL syntax, sorting is a frequently overlooked aspect of Oracle tuning. In general, an Oracle database will automatically perform sorting operations on row data as requested by

1. a CREATE INDEX or
2. an SQL ORDER BY or GROUP BY statement.

As we have discussed in prior modules, Oracle sorting can be one of the most time consuming and expensive operations in the Oracle database. Your job as an Oracle tuner is to identify sort operations and verify that there is no possible way to avoid the sort.
Remember, there are legitimate cases where a sort may be required such as the computation of a sum or an average number within a table. The trick is to investigate sorts and see if they can be tuned by adding a sorted index. We will begin this module with a review of the scripts that you can use to identify the amount of sorting in your Oracle database.

Use the ORDER BY clause to order the rows selected by a query. Sorting by position is useful in the following cases:

1. To order by a lengthy select list expression, you can specify its position in the ORDER BY clause rather than duplicate the entire expression.
2. For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

The mechanism by which Oracle Database sorts values for the ORDER BY clause is specified either explicitly by the NLS_SORT initialization parameter or implicitly by the NLS_LANGUAGE initialization parameter. You can change the sort mechanism dynamically from one linguistic sort sequence to another using the ALTER SESSION statement. You can also specify a specific sort sequence for a single query by using the NLSSORT function with the NLS_SORT parameter in the ORDER BY clause.

In Oracle, sorting occurs under the following circumstances:

1. Using the ORDER BY clause in SQL
2. Using the GROUP BY clause in SQL
3. When an index is created
4. When a SORT is invoked by the SQL optimizer because inadequate indices exist for a table join
5. Using UNION, INTERSECTION, or MINUS clauses that cause sort-merge joins
6. Using the DISTINCT clause

In short, the Oracle database will perform sorting operations whenever it detects the need to re-sequence row information.
In this module, you will learn about the tuning considerations related to sorting. By the end of this module, you will be able to:

1. Describe how Oracle performs sorts
2. Identify the parameters that govern sorting operations
3. Identify sorts in SQL statements
4. Monitor for disk sorts
5. Enable direct sorting in Oracle
6. Allocate and size dedicated TEMP tablespace
7. Monitor temporary segments in the TEMP tablespace
8. Remove a sort from a SQL query

In the next lesson, we will begin looking at how sorts are performed and the parameters governing sorts.