by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

2. 소트를 발생시키는 ...오퍼레이션




1. Sort Aggregate

  • 'SORT AGGREGATE'는 전체 로우를 대상으로 집계를 수행할 때 나타나는데, 실제 SORT가 이루어지지는 않음
SET autotrace traceonly;

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Note                                                                       
-----                                                                      
   - dynamic sampling used for this statement                              


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

  • 데이터 정렬을 위해 ORDER BY 오퍼레이션을 수행할 때 나타남.
SET autotrace traceonly;

SELECT *
FROM   EMP
ORDER BY SAL DESC
;
    
Execution Plan
----------------------------------------------------------                 
Plan hash value: 4060621227                                                
                                                                           
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |  1358 |     6  (17)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |  1358 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1358 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Note                                                                       
-----                                                                      
   - dynamic sampling used for this statement                              


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

  • SORT GROUP BY는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타남.
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 rows selected.

Elapsed: 00:00:00.10

Execution Plan
----------------------------------------------------------                 
Plan hash value: 637087546                                                 
                                                                           
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     6  (17)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |    14 |   518 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Note                                                                       
-----                                                                      
   - dynamic sampling used for this statement                              


Statistics
----------------------------------------------------------                 
          0  recursive calls                                               
          0  db block gets                                                 
         15  consistent gets                                               
          0  physical reads                                                
          0  redo size                                                     
        600  bytes sent via SQL*Net to client                              
        241  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 방식으로 되며 소트를 하지 않음.
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)             
---------- ------------------ ---------- ---------- ----------             
        30 MANAGER                  2850       2850       2850             
        30 SALESMAN                 5600       1600       1250             
        20 CLERK                    1900       1100        800             
        10 MANAGER                  2450       2450       2450             
        20 MANAGER                  2975       2975       2975             
        20 ANALYST                  6000       3000       3000             
        10 PRESIDENT                5000       5000       5000             
        30 CLERK                     950        950        950             
        10 CLERK                    1300       1300       1300             

9 rows selected.

Elapsed: 00:00:00.11

Execution Plan
----------------------------------------------------------                 
Plan hash value: 1697595674                                                
                                                                           
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     6  (17)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |    14 |   518 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Note                                                                       
-----                                                                      
   - dynamic sampling used for this statement                              


Statistics
----------------------------------------------------------                 
          4  recursive calls                                               
          0  db block gets                                                 
         31  consistent gets                                               
          0  physical reads                                                
          0  redo size                                                     
        580  bytes sent via SQL*Net to client                              
        241  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를 사용할 경우 소트를 보장한다는 것인데, 이는 8i부터 이미 소트를 보장하지 않는다고 밝혀짐.
  • 소팅 알고리즘을 사용해 SORT GROUP BY된 결과집합은 논리적인 정렬 순서를 따라 포인터로 서로 연결되 있을 뿐, 물리적인 순서와 다를 수 있고,
    ORDER BY를 명시하지 않았을 때는 논리적 순서를 무시하고 물리적 순서에 따라 결과를 출력하므로, 정렬을 보장하지 않음.
  • 즉, 실행계획에서 'SORT GROUP BY'의 의미는 '소팅 알고리즘을 사용해 값을 집계한다'는 뜻일 분 결과의 정렬을 의미하지 않음
  • 그러므로 GROUP BY 사용 시 정렬이 필요할 경우 반드시 ORDER BY를 추가해야 함.

4. Sort Unique

  • 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없을 때 UNNESTING 되어 조인으로 풀릴 경우, 그리고 세미 조인이 아닐 경우 SORT UNIQUE 오퍼레이션 수행
  • 만약 서브쿼리의 컬럼이 PK 또는 Unique인 경우 SORT UNIQUE 오퍼레이션은 생략
SET autotrace traceonly;

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

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     3 |   204 |     8  (13)| 00:00:01 |
|   1 |  NESTED LOOPS                |                  |     3 |   204 |     8  (13)| 00:00:01 |
|   2 |   SORT UNIQUE                |                  |     4 |    96 |     5   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMP              |     4 |    96 |     5   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT             |     1 |    44 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | DEPT_PRIMARY_KEY |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
                                                                                                 
Predicate Information (identified by operation id):                                              
---------------------------------------------------                                              
                                                                                                 
   3 - filter("JOB"='CLERK')                                                                     
   5 - access("DEPTNO"="DEPTNO")                                                                 
                                                                                                 
Note                                                                                             
-----                                                                                            
   - dynamic sampling used for this statement                                                    


Statistics
----------------------------------------------------------                                       
          0  recursive calls                                                                     
          0  db block gets                                                                       
         20  consistent gets                                                                     
          0  physical reads                                                                      
          0  redo size                                                                           
        395  bytes sent via SQL*Net to client                                                    
        241  bytes received via SQL*Net from client                                              
          2  SQL*Net roundtrips to/from client                                                   
          1  sorts (memory)                                                                      
          0  sorts (disk)                                                                        
          3  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
;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     8 |   296 |    12  (59)| 00:00:01 |
|   1 |  SORT UNIQUE        |      |     8 |   296 |    12  (59)| 00:00:01 |
|   2 |   UNION-ALL         |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| EMP  |     3 |   111 |     5   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| EMP  |     5 |   185 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------
                                                                            
Predicate Information (identified by operation id):                         
---------------------------------------------------                         
                                                                            
   3 - filter("DEPTNO"=10)                                                  
   4 - filter("DEPTNO"=20)                                                  
                                                                            
Note                                                                        
-----                                                                       
   - dynamic sampling used for this statement                               


Statistics
----------------------------------------------------------                  
          8  recursive calls                                                
          0  db block gets                                                  
         62  consistent gets                                                
          0  physical reads                                                 
          0  redo size                                                      
        340  bytes sent via SQL*Net to client                               
        241  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
;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |   296 |    12  (59)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |     3 |   111 |     6  (17)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |     3 |   111 |     5   (0)| 00:00:01 |
|   4 |   SORT UNIQUE       |      |     5 |   185 |     6  (17)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| EMP  |     5 |   185 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------
                                                                            
Predicate Information (identified by operation id):                         
---------------------------------------------------                         
                                                                            
   3 - filter("DEPTNO"=10)                                                  
   5 - filter("DEPTNO"=20)                                                  
                                                                            
Note                                                                        
-----                                                                       
   - dynamic sampling used for this statement                               


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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   182 |     7  (29)| 00:00:01 |
|   1 |  SORT UNIQUE       |      |    14 |   182 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Note                                                                       
-----                                                                      
   - dynamic sampling used for this statement                              


Statistics
----------------------------------------------------------                 
          4  recursive calls                                               
          0  db block gets                                                 
         31  consistent gets                                               
          0  physical reads                                                
          0  redo size                                                     
        250  bytes sent via SQL*Net to client                              
        241  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             
;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |  1974 |    12  (17)| 00:00:01 |
|   1 |  MERGE JOIN         |      |    14 |  1974 |    12  (17)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |   176 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |   176 |     5   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |    14 |  1358 |     6  (17)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    14 |  1358 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------
                                                                            
Predicate Information (identified by operation id):                         
---------------------------------------------------                         
                                                                            
   4 - access("D"."DEPTNO"="E"."DEPTNO")                                    
       filter("D"."DEPTNO"="E"."DEPTNO")                                    
                                                                            
Note                                                                        
-----                                                                       
   - dynamic sampling used for this statement                               


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

6. Window Sort

  • WINDOW SORT는 분석함수를 수행할 때 나타남
SET autotrace traceonly;

SELECT EMPNO,
       ENAME,
       JOB,
       MGR,
       SAL,
       AVG(SAL) OVER(PARTITION BY DEPTNO)
FROM   EMP                  
;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |  1050 |     6  (17)| 00:00:01 |
|   1 |  WINDOW SORT       |      |    14 |  1050 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |  1050 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Note                                                                       
-----                                                                      
   - dynamic sampling used for this statement                              


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

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.