Execution Plan image:
Aggregation operation.
SORT JOIN sorts a set of records to use in a MERGE JOIN operation.
The example from theMERGE JOIN topic is used again. All of the tables used in this example are fully indexed, so the following example deliberately disables the indexes by adding 0 to the numeric keys during the join, in order to force a merge join to occur.
select COMPANY.Name
from COMPANY, SALES
where COMPANY.Company_ID+0 = SALES.Company_ID+0
and SALES.Period_ID =3
and SALES.Sales_Total>1000;
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL SALES
SORT JOIN
TABLE ACCESS FULL COMPANY
The Execution Plan shows that the COMPANY table and SALES table are accessed using TABLE ACCESS FULL operations. Before the records from those tables are passed to the MERGE JOIN operation, they are first processed by SORT JOIN operations that sort the records. The SORT JOIN output is used as input to the MERGE JOIN operation.
Execution Plan image:
Aggregation operation.
SORT ORDER BY sorts result sets without eliminating duplicate records.
select Name
from COMPANY
order by Name;
SORT ORDER BY
TABLE ACCESS FULL COMPANY
The Execution Plan shows that after the query is resolved (by the TABLE ACCESS FULL operation), the records are passed to the SORT ORDER BY operation for ordering. The SORT ORDER BY operation orders the records by their Name values, and sends the output to the user.
Note: If the example had used the Company_ID column instead of the Name column, the optimizer would have used the COMPANY_PK index to perform the ordering.
Execution Plan image:
Aggregation operation.
SORT UNIQUEsorts result sets and eliminates duplicate records prior to processing with the MINUS, INTERSECTION and UNION operations.
A MINUS operation is used in this example, although the SORT UNIQUE operation is also shown in the INTERSECTION and UNION topics.
select Company_ID
from COMPANY
MINUS
select Company_ID
from COMPETITOR;
PROJECTION
MINUS
SORT UNIQUE
TABLE ACCESS FULL COMPANY
SORT UNIQUE
TABLE ACCESS FULL COMPETITOR
The Execution Plan shows that after each of the queries is separately resolved (by the TABLE ACCESS FULL operations), the records are passed to the SORT UNIQUE operation prior to being input into the MINUS operation. The SORT UNIQUE operation sorts the records and eliminates any duplicates, sends the records to the MINUS operation.
Operation: SQL MODEL
Option: ACYCLIC
The SQL MODEL ACYCLIC operation retrieves and organizes data into an AUTOMATIC ORDER SQL Model.
Option: ACYCLIC FAST
The SQL MODEL ACYCLIC FAST operation retrieves and organizes data into an AUTOMATIC ORDER SQL Model using the FAST method.
Option: ORDERED
The SQL MODEL ORDERED operation retrieves and organizes data into a SEQUENTIAL ORDER SQL Model.
Option: ORDERED FAST
The SQL MODEL ORDERED FAST operation retrieves and organizes data into a SEQUENTIAL ORDER SQL Model using the FAST method.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center