8.6 Sorting

 < 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.

8.6.1 Unique Sort

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

8.6.2 ORDER BY Sort

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 

8.6.3 GROUP BY Sort

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

8.6.4 Sort Merge Join Sort

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

8.6.5 Aggregate Sort

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 > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net