System and Application Softwares
Oracle SQL Tuning: Avoiding Sort
By its nature, sort is CPU- and I/O-intensive, as it must reorder a set of rows by comparing values from every row.
Examining the Optimizer strategy is one way to see whether a sort is being performed. However, there are are some standard operators that will often cause a sort to occur. You should keep these in mind when tuning queries:
The goal is to use DISTINCT only on those columns that need to be reduced to a unique subset. This will result in lower sort overhead (shorter records to sort
means less virtual memory and smaller temporary files). We also hope to encourage the Optimizer to use a sorted index to avoid the sort completely.
Applying DISTINCT to a minimal column list could be done using a view or a derived table, as shown in the example below:
from employee e
where e.employee_id = jh.employee_id)
(select distinct employee_id
where employee_id < ' 00166') jh (employee_id);
Retrieval of a single row that is the result of applying a group function to a group of selected rows.
Operation sorting a set of rows to eliminate duplicates.
Operation sorting a set of rows into groups for a query with a GROUP BY clause.
GROUP BY PIVOT
Operation sorting a set of rows into groups for a query with a GROUP BY clause. The PIVOT option indicates a pivot-specific optimization for the SORT GROUP BY operator.
SORT GROUP BY
Operation sorting a set of rows before a merge-join.
Operation sorting a set of rows for a query with an ORDER BY clause.
© 2020 Digcode.com. All rights reserved.