(1) sort aggregate





SQL> select sum(sal),max(sal),min(sal) from scott.emp;

 SUM(SAL)   MAX(SAL)   MIN(SAL)
---------- ---------- ----------
    29025       5000        800


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

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


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         7  consistent gets
         0  physical reads
         0  redo size
       554  bytes sent via SQL*Net to client
       420  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



SQL> select * from emp order by sal desc;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20

14 rows selected.


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

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


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

SQL>


  • 데이터 정렬을 위해 order by 수행시 나타남


(3) sort group by



  • 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타남





SQL> select deptno,job,sum(sal),max(sal),min(sal)
from emp
group by deptno,job
order by deptno,job;
 2    3    4
    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 rows selected.


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

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


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

SQL>






hash group by 와 비교

  • 10gr2에서 도입되었으며 order by 절을 명시 않으면 대부분 hash group by 방식으로 처리




SQL> select deptno,job,sum(sal),max(sal),min(sal)
from emp
group by deptno,job  2    3  ;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    11 |   132 |     3  (34)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |    11 |   132 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   168 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------


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







  • 해싱 알고리즘을 사용해 데이터를 그룹핑
  • 읽는 로우마다 group by 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신
  • sort group by 역시 읽는 로우마다 그룹별로 집계갱신하지만 소팅알고리즘으로 group by를 수행
  • 두방식 모두 집계대상 로우가 아무리 많아도 그룹개수(group by 기준 컬럼의 distinct value 개수) 가
    소수일 때는 디스크 소트가 전혀 발생 하지 않음

  • 관련 파라미터
    • 비활성: alter system set “_gby_hash_aggregation_enabled” =false;


group by 결과의 정렬 순서

  • 오라클은 group by 결과의 정렬순서를 보장하지않음
  • 소팅 알고리즘을 사용해 sort gorup by 된 결과 집합은 논리적인 정렬순서를 따라 포인터로 서로 연결되
    있을것이며 이는 물리적인 순서와 다를수 있다.
  • 실행계획상 sort group by 는 소팅 알고리즘을 사용해 값을 집계한다 는 뜻일뿐 정렬을 의미 하지 않음




SQL> create index scott.emp_idx2 on scott.emp(job,deptno);

Index created.


SQL> select /*+ index(scott.emp scott.emp_idx) */ deptno, job, avg(sal)
from scott.emp
where deptno is not null
group by deptno,job;  2    3    4

    DEPTNO JOB         AVG(SAL)
---------- --------- ----------
       20 ANALYST         3000
       10 CLERK           1300
       20 CLERK            950
       30 CLERK            950
       10 MANAGER         2450
       20 MANAGER         2975
       30 MANAGER         2850
       10 PRESIDENT       5000
       30 SALESMAN        1400

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2981309258

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     9 |   135 |     2   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |          |     9 |   135 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |    14 |   210 |     2   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN           | EMP_IDX2 |    14 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

  3 - filter("DEPTNO" IS NOT NULL)


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

SQL>





  • 컬럼 순서가 다르더라도 group by를 위해 emp_idx2 인덱스가 사용되어 기준 컬럼과 다른 정렬 순서를 보임
  • 결론
    • 정렬된 group by 결과를 얻고자 한다면, 실행계획에 ‘sort group by’라고 표시 되더라도 order by를 명시 해야한다.


(4) sort unique

  • Sort unique 오프레이션이 먼저 수행되는 경우 (메인쿼리와 조인전)
    • unnesting 된 서브 쿼리가 M쪽 집합
    • Unique 인덱스가 없을 경우
    • 세미 조인으로 수행되 않을 경우 
  • PK/unique 제약, unique인덱스를 통해 Unnesting된 서브쿼리의 uniqueness가 보장된다면 생략




SQL> select /*+ ordered use_nl(dept) */ * from scott.dept
where deptno in (select /*+unnest */ deptno
from scott.emp where job='CLERK');  2    3

    DEPTNO DNAME          LOC
---------- -------------- -------------
       10 ACCOUNTING     NEW YORK
       20 RESEARCH       DALLAS
       30 SALES          CHICAGO


Execution Plan
----------------------------------------------------------
Plan hash value: 397061396

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     3 |    93 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                |          |       |       |            |          |
|   2 |   NESTED LOOPS               |          |     3 |    93 |     4  (25)| 00:00:01 |
|   3 |    SORT UNIQUE               |          |     3 |    33 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN         | EMP_IDX2 |     3 |    33 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT  |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    20 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

  4 - access("JOB"='CLERK')
  5 - access("DEPTNO"="DEPTNO")


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

SQL>





  • 집합연산자 (union , minus, intersect )를 사용할때





SQL> select job,mgr from scott.emp where deptno=10
union
select job,mgr from scott.emp where deptno=20;  2    3

JOB              MGR
--------- ----------
ANALYST         7566
CLERK           7782
CLERK           7788
CLERK           7902
MANAGER         7839
PRESIDENT

6 rows selected.


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

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

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

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


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

SQL>

SQL> select job,mgr from scott.emp where deptno=10
minus
select job,mgr from scott.emp where deptno=20;  2    3

JOB              MGR
--------- ----------
CLERK           7782
PRESIDENT


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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     5 |   150 |    10  (60)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |     5 |    75 |     5  (20)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |     5 |    75 |     4   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |      |     5 |    75 |     5  (20)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP  |     5 |    75 |     4   (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
       527  bytes sent via SQL*Net to client
       420  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         2  sorts (memory)
         0  sorts (disk)
         2  rows processed

SQL>






  • distinct 오프레이션





SQL> select distinct deptno from scott.emp order by deptno;

    DEPTNO
----------
       10
       20
       30


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

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


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

SQL>

10gr2 부터 distinct 연산에서도 order by 가 생략되면 hash unique방식으로 수행
SQL> select distinct deptno from scott.emp
 2  ;

    DEPTNO
----------
       30
       20
       10


Execution Plan
----------------------------------------------------------
Plan hash value: 3709190377

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


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

SQL>





  • 참고)
    • 10gr2
      • _convert_set_to_join 파라미터를 true로 설정하면 minus,intersect 같은 
        집합(set)연산에서 hash unique 오퍼레이션 사용
  • alter session set “_convert_set_to_join”=true;


(5) sort join

  • 소트 머지 조인을 수행할 때 나타남





select /*+ ordered use_merge(e) */ *
from scott.dept d, scott.emp e
where d.deptno=e.deptno;

14 rows selected.


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

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     7  (15)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     7  (15)| 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 |   532 |     5  (20)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

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


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

SQL>




  •  outer 테이블인 dept의 deptno 컬럼 인덱스를 이용할 때는 한번만 발생 (2장 조인원리와 활용)



(6) window sort


SQL> select empno,ename, job,mgr ,sal, avg(sal) over (partition by deptno)
 2  from scott.emp;

    EMPNO ENAME      JOB              MGR        SAL AVG(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- --------- ---------- ---------- -------------------------------
     7782 CLARK      MANAGER         7839       2450                      2916.66667
     7839 KING       PRESIDENT                  5000                      2916.66667
     7934 MILLER     CLERK           7782       1300                      2916.66667
     7566 JONES      MANAGER         7839       2975                            2175
     7902 FORD       ANALYST         7566       3000                            2175
     7876 ADAMS      CLERK           7788       1100                            2175
     7369 SMITH      CLERK           7902        800                            2175
     7788 SCOTT      ANALYST         7566       3000                            2175
     7521 WARD       SALESMAN        7698       1250                      1566.66667
     7844 TURNER     SALESMAN        7698       1500                      1566.66667
     7499 ALLEN      SALESMAN        7698       1600                      1566.66667

    EMPNO ENAME      JOB              MGR        SAL AVG(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- --------- ---------- ---------- -------------------------------
     7900 JAMES      CLERK           7698        950                      1566.66667
     7698 BLAKE      MANAGER         7839       2850                      1566.66667
     7654 MARTIN     SALESMAN        7698       1250                      1566.66667

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563

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


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

SQL>


  • 분석함수 (analytic Function) 를 수행할때 나타남


  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 남송휘 (tofriend)
  • 최초작성일: 2011년 6월 12일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^