1. Sort Aggregate

sort aggregate는 전체 로우를 대상으로 집계를 수행할 때 나타나는데, sort라는 표현을 사용하지만 실제로는 발생하지 않는다.


SET autotrace traceonly;

SELECT SUM(SAL),MAX(SAL), MIN(SAL) FROM   EMP;

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        548  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2. Sort Order By

데이터를 정렬을 수행할 때 나타난다.


SET autotrace traceonly;

SELECT * FROM   EMP ORDER BY SAL DESC;

Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   518 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1321  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

3. Sort Group By

sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.


SET autotrace ON;

SELECT DEPTNO,JOB,SUM(SAL),MAX(SAL),MIN(SAL)
FROM   EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB
;


    DEPTNO JOB         SUM(SAL)   MAX(SAL)   MIN(SAL)
---------- --------- ---------- ---------- ----------
        10 CLERK           1300       1300       1300
        10 MANAGER         2450       2450       2450
        10 PRESIDENT       5000       5000       5000
        20 ANALYST         6000       3000       3000
        20 CLERK           1900       1100        800
        20 MANAGER         2975       2975       2975
        30 CLERK            950        950        950
        30 MANAGER         2850       2850       2850
        30 SALESMAN        5600       1600       1250

9 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------
Plan hash value: 15469362

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    11 |   165 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |    11 |   165 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   210 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        889  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          9  rows processed

10gR2부터 'HASH GROUP BY' 방식이 도임되면서, ORDER BY절을 함께 명시하지 않으면 대부분 HASH GROUP BY 방식으로 처리된다.{code:SQL}
SET autotrace ON;

SELECT DEPTNO,JOB,SUM(SAL),MAX(SAL),MIN(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
;

DEPTNO JOB SUM(SAL) MAX(SAL) MIN(SAL)

--

-

--

--
20 CLERK 1900 1100 800
30 SALESMAN 5600 1600 1250
20 MANAGER 2975 2975 2975
30 CLERK 950 950 950
10 PRESIDENT 5000 5000 5000
30 MANAGER 2850 2850 2850
10 CLERK 1300 1300 1300
10 MANAGER 2450 2450 2450
20 ANALYST 6000 3000 3000

9 개의 행이 선택되었습니다.

Execution Plan

--
Plan hash value: 4067220884

---

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

---

0	SELECT STATEMENT		11	165	4 (25)	00:00:01
1	HASH GROUP BY		11	165	4 (25)	00:00:01
2	TABLE ACCESS FULL	EMP	14	210	3 (0)	00:00:01

---

Statistics

--
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
904 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed


hash group by는 정렬을 수행하지 않고 해싱 알고리즘을 사용해 데이터를 그룹핑한다. 읽는 로우마다 group by 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식이다. 
9i부터 group by 소팅되ㅣ지 않는다고 공식적으로 밝히고 있다. 소팅 알고리즘으로 sort group by된 결과집합은 논리적인 정렬 순서를 따라 포인터를 서로 연결되어 있을 것이다. 하지만 물리적인 순서는 논리적인 순서와 다를 수 있고, order by를 명시하지 않을 경우는 논리적 순서를 무시하고 물리적 순서에 따라 결과를 출력하기 때문에 정렬을 보장하지 않을 것이라고 짐작할 수 있다. 
*+즉, 정렬된 group by를 얻고자 한다면, 실행계획에 설령 'sort group by'라고 표시되더라도 반드시 order by를 명시해야 한다.+*
 

h1. 4. Sort Unique 
Unnesting된 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없다면, 그리고 세미 조인으로 수행되지도 않는다면 메인 쿼리와 조인하기 전에 sort unique 오퍼레인션이 먼저 수행된다. 
{code:SQL}
SET autotrace traceonly;

SELECT /*+ ORDERED USE_NL(DEPT) */ * FROM   DEPT
WHERE  DEPTNO IN (SELECT /*+ UNNEST */
                         DEPTNO
                  FROM   EMP
                  WHERE  JOB = 'CLERK')
;

Execution Plan
----------------------------------------------------------
Plan hash value: 1091542497

---------------------------------------------------------------------------------

| Id| Operation                    | Name    | Rows| Bytes |Cost(%CPU)| Time    |
---------------------------------------------------------------------------------

|  0| SELECT STATEMENT             |         |   3 |    93 |   6  (17)| 00:00:01|

|  1|  NESTED LOOPS                |         |   3 |    93 |   6  (17)| 00:00:01|

|  2|   SORT UNIQUE                |         |   3 |    33 |   3   (0)| 00:00:01|

|* 3|    TABLE ACCESS FULL         | EMP     |   3 |    33 |   3   (0)| 00:00:01|

|  4|   TABLE ACCESS BY INDEX ROWID| DEPT    |   1 |    20 |   1   (0)| 00:00:01|

|* 5|    INDEX UNIQUE SCAN         | PK_DEPT |   1 |       |   0   (0)| 00:00:01|

---------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("JOB"='CLERK')
   5 - access("DEPTNO"="DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        605  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

union, minus, intersect 같은 집합 연산자와 distinct 연산을 사용할 때도 sort unique 오퍼레이션이 나타난다.


-- 1. UNION
SET autotrace traceonly;

SELECT JOB, MGR FROM   EMP WHERE  DEPTNO = 10
UNION
SELECT JOB,MGR FROM   EMP WHERE  DEPTNO = 20;

Execution Plan
----------------------------------------------------------
Plan hash value: 3774834881

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     8 |   120 |     8  (63)| 00:00:01 |
|   1 |  SORT UNIQUE        |      |     8 |   120 |     8  (63)| 00:00:01 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| EMP  |     3 |    45 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP  |     5 |    75 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPTNO"=10)
   4 - filter("DEPTNO"=20)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        578  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed
-- 2. MINUS
SET autotrace traceonly;

SELECT JOB,MGR FROM   EMP WHERE  DEPTNO = 10
MINUS
SELECT JOB,MGR FROM   EMP WHERE  DEPTNO = 20;

Execution Plan
----------------------------------------------------------
Plan hash value: 3686975449

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |   120 |     8  (63)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |     3 |    45 |     4  (25)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |     3 |    45 |     3   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |      |     5 |    75 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP  |     5 |    75 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPTNO"=10)
   5 - filter("DEPTNO"=20)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed
                                               

-- 3. DISTINCT
SET autotrace traceonly;

SELECT DISTINCT DEPTNO FROM   EMP ORDER BY DEPTNO ;

Execution Plan
----------------------------------------------------------
Plan hash value: 596748738

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |     9 |     5  (40)| 00:00:01 |
|   1 |  SORT UNIQUE       |      |     3 |     9 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        469  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

5. Sort Join

sort join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다.


SET autotrace traceonly;

SELECT /*+ ORDERED USE_MERGE(E) */ *
FROM   DEPT D, EMP  E WHERE  D.DEPTNO = E.DEPTNO;

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

--------------------------------------------------------------------------------
--------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |    14 |   798 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN                  |         |    14 |   798 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |         |    14 |   518 |     4  (25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   518 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1472  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         11  rows processed

6. Window Sort

window sort는 분석함수를 수행할 때 나타난다.


SET autotrace traceonly;

SELECT EMPNO,ENAME,JOB,MGR,SAL,AVG(SAL) OVER(PARTITION BY DEPTNO) FROM   EMP;
Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   406 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |      |    14 |   406 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   406 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1171  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

최초작성자 : 안종식
최초작성일 : 2010년 11월 03일
이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*

2. 소트를 발생시키는 오퍼레이션 (by csahn79) [2010.11.01]

1. Sort Aggregate

2. Sort Order By

3. Sort Group By

5. Sort Join

6. Window Sort