To be able to remove unnecessary Oracle sorting, you must first become familiar with poor SQL programming that can lead to sorting.
As you may know, all SQL query processing can be divided into seven phases:
SQL query processing can be divided into seven phases
Checks the syntax of the query
Checks that all objects exist and are accessible
Rewrites query as join on base tables as opposed to using views
Rewrites query transforming some complex constructs into simpler ones where appropriate
Determines the optimal access path for the query to take < Here Oracle chooses to sort
Query evaluation plan generation
Execute the SQL query
During this process, Oracle may make a decision that a final or intermediate result set requires re-sequencing and will invoke an Oracle sort.
Two most common sort operations invoke include, the ORDER BY sort, and the JOIN sort.
ORDER BY sort
The ORDER BY sort is most commonly seen in queries that have an ORDER BY clause and do not access the table using an index.
In the example below, you will notice an unqualified select against the EMP table:
SELECT STATEMENT Optimizer=CHOOSE
SORT (ORDER BY)
TABLE ACCESS (FULL) OF 'EMP'
If the query is going to read the majority of the EMP rows then a full-table scan is fine. However, if the query is only reading a small portion
of the rows (i.e.
select * from emp where status = 3
) then you would tune the query by adding an index.
The JOIN sort
The JOIN sort is a very expensive operation and should be avoided whenever possible. To understand how a JOIN sort is created, consider the following example.
In this query, notice that the JOIN sort is caused by the lack of a WHERE clause to join the tables together, and Oracle must sort each result set
separately and join based upon the sorted rows:
SQL> explain plan for
SELECT STATEMENT [CHOOSE] Cost=5
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP
However, the lack of a WHERE clause is not the only time that a sort join can be invoked.
In the next lesson, you will learn to monitor your database for disk sorts.
Below is an example of invoking the sort join clause.