Sorting Operations   «Prev 

Oracle Sorting Process

An Oracle database will automatically perform sorting operations on row data under the following circumstances:
  1. When an index is created
  2. When using the ORDER BY clause in SQL
  3. When using the GROUP BY clause in SQL
There are several ways to sequence Oracle output rows:
  1. Oracle internal sort (in sort_area_size or TEMP tablespace)
  2. Retrieve the rows in pre-sorted order (using an index)
  3. Using a third-party sort product: This is for businesses that have to sequence millions of rows of output on an hourly basis, external sort products can be faster than using Oracle to sort the data.

Oracle Database 12c Performance Tuning
1) Oracle estimates the number of rows to be sorted.

2) If this space is less than sort_area_size, the sort is performed in-memory

3) If space is greater than sort_area_size, then the sort is performed in the TEMP tablespace

4) Oracle returns the sorted result set to the user.