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 a CREATE INDEX or an SQL ORDER BY or GROUP BY statement.

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.