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:
- To order by a lengthy select list expression, you can specify its position in the ORDER BY clause rather than duplicate the entire expression.
- 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.