| < Day Day Up > |
|
There are different types of sorting operations used by the Optimizer. Numerous different types of operations trigger sorts, both implicit and explicitly specified.
SORT UNIQUE. Caused by the use of DISTINCT or a list of unique values.
SORT ORDER BY. The ORDER BY clause forces a sort operation when indexes or other aspects in the query plan do not sort the rows.
SORT GROUP BY. The GROUP BY clause groups or aggregates rows into a summarized set of rows. Sorting is required to aggregate duplicate column values into their distinct groups.
SORT JOIN. A sort on a column to be joined in a sort merge join.
SORT AGGREGATE. Aggregation across many rows.
Here is a unique sort.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT DISTINCT(order_id) FROM ordersline; Query Cost Rows Bytes Pos ------------------------------ ---- ------ ------- --- 1. SELECT STATEMENT on 8209 226703 906812 0 2. SORT UNIQUE on 8209 226703 906812 1 3. INDEX FAST FULL SCAN on XFK_OL_ORDER 668 997903 3991612 1
The query below requires that QTY values in the subquery must be unique prior to being validated against QTY values in the calling query. There is no sense in checking the same value twice.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT * FROM stockmovement sm WHERE qty IN (SELECT qty FROM stockmovement); Query Cost Rows Bytes sort ------------------------------ ---- ------ -------- -------- SELECT STATEMENT on 16353 570175 15964900 MERGE JOIN SEMI on 16353 570175 15964900 SORT JOIN on 11979 570175 13684200 45802000 TABLE ACCESS FULL on STOCKMOVEMENT 355 570175 13684200 SORT UNIQUE on 4374 570175 2280700 13755000 TABLE ACCESS FULL on STOCKMOVEMENT 355 570175 2280700
In this first example the entire table is read and the ORDER BY clause forces a sort on one of the columns.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT * FROM customer ORDER BY name; Query Cost Rows Bytes Pos Sort ------------------------- ---- ---- ------ --- ------ 1. SELECT STATEMENT on 149 2694 360996 0 2. SORT ORDER BY on 149 2694 360996 1 877000 3. TABLE ACCESS FULL on CUSTOMER 16 2694 360996 1
This second example is different as the sort specified in the ORDER BY clause is catered for by the index full scan, a key value ordered index scan. Thus there is no SORT ORDER BY step in the query plan as the index is already sorted.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT customer_id FROM customer ORDER BY customer_id; Query Cost Rows Bytes Pos Sort ------------------------- ---- ---- ------ --- ------ 1. SELECT STATEMENT on 7 2694 8082 0 2. INDEX FULL SCAN on XPK_CUSTOMER 7 2694 8082 1
Here is another example of the same thing. The GROUP BY clause causes a sort. The ORDER BY clause sorts the same thing and is thus superfluous.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT amount, COUNT(amount) FROM ordersline GROUP BY amount ORDER BY amount; Query Cost Rows Bytes Pos Sort ----------------------- ---- ---- ------ --- -------- 1. SELECT STATEMENT on 8682 97713 488565 0 2. SORT GROUP BY on 8682 97713 488565 1 13214000 3. TABLE ACCESS FULL on ORDERSLINE 765 997903 4989515 1
In this case the GROUP BY clause is required to sort by the aggregated column.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT amount, COUNT(amount) FROM ordersline GROUP BY amount; Query Cost Rows Bytes Pos Sort ----------------------- ---- ---- ------ --- -------- 1. SELECT STATEMENT on 8682 97713 488565 0 2. SORT GROUP BY on 8682 97713 488565 1 13214000 3. TABLE ACCESS FULL on ORDERSLINE 765 997903 4989515 1
Here is another example of a sort not required and indicated so by the Optimizer.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT customer_id, COUNT(customer_id) FROM customer GROUP BY customer_id; Query Cost Rows Bytes Pos Sort ----------------------- ---- ---- ------ --- -------- 1. SELECT STATEMENT on 7 2694 8082 0 2. SORT GROUP BY NOSORT on 7 2694 8082 1 3. INDEX FULL SCAN on XPK_CUSTOMER 7 2694 8082 1
In this example the ORDER BY clause is executing the sort on the COA table as indicated by the SORT JOIN in the query plan, allowing the MERGE JOIN to occur on the COA# column between the two row sets.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT coa.*, gl.* FROM generalledger gl JOIN coa ON(gl.coa# = coa.coa#) ORDER BY coa.coa#; Query Cost Rows Bytes Pos Sort ----------------------- ---- ------ -------- --- -------- 1. SELECT STATEMENT on 20782 1068929 50239663 0 2. MERGE JOIN on 20782 1068929 50239663 1 3. TABLE ACCESS BY INDEX ROWID on GE 20778 1068929 24585367 1 4. INDEX FULL SCAN on XFK_COA# 2536 1068929 1 3. SORT JOIN on 4 55 1320 2 4. TABLE ACCESS FULL on COA 2 55 1320 1
An aggregate sort is shown in the query plan for the following query.
EXPLAIN PLAN SET statement_id='TEST' FOR SELECT AVG(balance) FROM customer; Query Cost Rows Bytes Pos ----------------------- ---- ---- ------ --- 1. SELECT STATEMENT on 16 1 2 0 2. SORT AGGREGATE on 1 2 1 3. TABLE ACCESS FULL on CUSTOMER 16 2694 5388 1
| < Day Day Up > |
|