메뉴 건너뛰기

bysql.net

5. 인덱스를 이용한 소트 연산 대체

2011.06.13 11:27

멋진넘 조회 수:3397

5. 인덱스를 이용한 소트 연산 대체

 ▶SORT UNIQUE NOSORT 오퍼레이션
  소트를 수행하지 않고도 인덱스를 이용해 UNIQUE한 집합을 출력
  (1)SORT ORDER BY 오퍼레이션 대체
  (2)SORT GROUP BY 오퍼레이션 대체
 
  SELECT /*+  */ DISTINCT EMPNO
    FROM EMP;
 
  (9i) - 교재에서 설명하는 내용
  -------------------------------------------------------------
  | Id  | Operation          | Name   | Rows  | Bytes | Cost  |
  -------------------------------------------------------------
  |   0 | SELECT STATEMENT   |        |    14 |    56 |     9 |
  |   1 |  SORT UNIQUE NOSORT|        |    14 |    56 |     9 |
  |   2 |   INDEX FULL SCAN  | PK_EMP |    14 |    56 |     1 |
  -------------------------------------------------------------
 
  (10g 이후..) - 옵티마이져가 자동으로 NOSORT 처리함
  ---------------------------------------------------------------------------
  | Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------
  |   0 | SELECT STATEMENT |        |    14 |    56 |     1   (0)| 00:00:01 |
  |   1 |  INDEX FULL SCAN | PK_EMP |    14 |    56 |     1   (0)| 00:00:01 |
  ---------------------------------------------------------------------------

 

 1)SORT ORDER BY 대체
 ▶테스트환경 구축
  DROP   TABLE YHN_CUSTOMER;
 
  CREATE TABLE YHN_CUSTOMER
  AS
  SELECT E.EMPLOYEE_ID                   AS CUSTID
       , E.LAST_NAME                     AS NAME
       , E.EMAIL                         AS RESNO
       , 'U'                             AS STATUS
       , E.PHONE_NUMBER                  AS TELL
       , E.HIRE_DATE - 265*40            AS AGE
       , SUBSTR(D.DEPARTMENT_NAME, 1, 1) AS REGION
    FROM HR.EMPLOYEES E, HR.DEPARTMENTS D
   WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
 
  CREATE UNIQUE INDEX PK_CUSTOMER  ON YHN_CUSTOMER(CUSTID);
 
  CREATE        INDEX CUSTOMER_X01 ON YHN_CUSTOMER(REGION);
 
  CREATE        INDEX CUSTOMER_X02 ON YHN_CUSTOMER(REGION, CUSTID);
 
 ▶인덱스가 REGION 단일 컬럼으로 구성되었거나, 결합인덱스라도 REGION 바로 뒤에

  CUSTID 오지 않는 경우
  SELECT /*+ INDEX(C, CUSTOMER_X01) */CUSTID, NAME, RESNO, STATUS, TELL
    FROM YHN_CUSTOMER C
   WHERE REGION = 'A'
   ORDER BY CUSTID;
 
  ---------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |              |     3 |   177 |     3  (34)| 00:00:01 |
  |   1 |  SORT ORDER BY               |              |     3 |   177 |     3  (34)| 00:00:01 |
  |   2 |   TABLE ACCESS BY INDEX ROWID| YHN_CUSTOMER |     3 |   177 |     2   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN          | CUSTOMER_X01 |     3 |       |     1   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     3 - access("REGION"='A')
 
 ▶결합인덱스 구성이 조건절에 이어 ORDER BY의 컬럼으로 구성되어 있는 경우

  SELECT /*+ INDEX(C, CUSTOMER_X02) */CUSTID, NAME, RESNO, STATUS, TELL
    FROM YHN_CUSTOMER C
   WHERE REGION = 'A'
   ORDER BY CUSTID;
 
  --------------------------------------------------------------------------------------------
  | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |              |     3 |   177 |     2   (0)| 00:00:01 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| YHN_CUSTOMER |     3 |   177 |     2   (0)| 00:00:01 |
  |*  2 |   INDEX RANGE SCAN          | CUSTOMER_X02 |     3 |       |     1   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------------
 
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - access("REGION"='A')

 

 2)SORT GROUP BY 대체
 ▶GROUP BY의 사용컬럼이 결합 인덱스나 단일 컬럼 인덱스의 선두컬럼으로 사용된다면
  SORT GROUP BY 연산을 대체할 수 있다. 실행계획에 SORT GROUP BY NOSORT 부분

  SELECT REGION, AVG(SYSDATE-AGE), COUNT(*) FROM YHN_CUSTOMER
   GROUP BY REGION;
 
  -----------------------------------------------------------------------------------
  | Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |              |     8 |    80 |     4  (25)| 00:00:01 |
  |   1 |  HASH GROUP BY     |              |     8 |    80 |     4  (25)| 00:00:01 |
  |   2 |   TABLE ACCESS FULL| YHN_CUSTOMER |   106 |  1060 |     3   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------
 
 ▶책내용과 다름 - DATA량이 얼마되지 않아서 옵티마이져가 FULL SCAN으로 유도함
  SELECT /*+  */
         REGION, AVG(SYSDATE-AGE), COUNT(*)
    FROM YHN_CUSTOMER C
   WHERE REGION > ' ' -- 교재와 유사한 형태의 PLAN을 유도하기 위해 WHERE절에 조건 추가
   GROUP BY REGION;
 
  ---------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |              |     8 |    80 |     2   (0)| 00:00:01 |
  |   1 |  SORT GROUP BY NOSORT        |              |     8 |    80 |     2   (0)| 00:00:01 |
  |   2 |   TABLE ACCESS BY INDEX ROWID| YHN_CUSTOMER |   106 |  1060 |     2   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN          | CUSTOMER_X02 |   106 |       |     1   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------
 
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     3 - access("REGION">' ' AND "REGION" IS NOT NULL)

 

 3)인덱스가 소트 연산을 대체하지 못하는 경우
 ▶아래는 SAL 컬럼을 선두로 갖는 인덱스가 있는데도 정렬을 수행하는 경우임
  SELECT /*+ INDEX(E, YHN_EMP_IDX) */
         *
    FROM YHN_EMP E
   ORDER BY SAL;
 
  --------------------------------------------------------------------------------------
  | Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |         | 14000 |   533K|       |   204   (2)| 00:00:02 |
  |   1 |  SORT ORDER BY     |         | 14000 |   533K|   896K|   204   (2)| 00:00:02 |
  |   2 |   TABLE ACCESS FULL| YHN_EMP | 14000 |   533K|       |    41   (3)| 00:00:01 |
  --------------------------------------------------------------------------------------
 
  Outline Data
  -------------
    /*+
        (생략...)
        ALL_ROWS
        (생략...)
    */
   
  ●위의 SQL처럼 옵티마이저가 이런 결정을 하는 가장 큰 이유는 인덱스를 이용하지 않는 편이 더 낫다고
   판단하는 경우임.
  ●또란 옵티마이저 모드가 ALL_ROWS임,
   이 때 옵티마이저는 전체 로우를 FETCH 하는 것을 기준으로 쿼리 수행 비용을 산정후 데이터량이 많을수록
   인덱스를 이용한 테이블 랜덤 액세스 비용이 높아져 옵티마이저는 차라리 FULL TABLE SCAN하는 쪽을 택할
   가능성이 높아짐
  ●옵티마이저 모드를 FIRST_ROWS로 바꾸면 사용자가 일부만 FETCH하고 멈출 것임을 시사하므로 옵티마이저는
   인덱스를 이용해 정렬 작업을 대체함.
 
  -- 옵티마이저 모드를 FIRST_ROWS로 변경한 PLAN
  ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
 
  SELECT /*+ INDEX(E, YHN_EMP_IDX) */
         *
    FROM YHN_EMP E
   ORDER BY SAL;
  
  --------------------------------------------------------------------------------------
  | Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |         | 14000 |   533K|       |   204   (2)| 00:00:02 |
  |   1 |  SORT ORDER BY     |         | 14000 |   533K|   896K|   204   (2)| 00:00:02 |
  |   2 |   TABLE ACCESS FULL| YHN_EMP | 14000 |   533K|       |    41   (3)| 00:00:01 |
  --------------------------------------------------------------------------------------
 
   Outline Data
  -------------
    /*+
        (생략...)
        FIRST_ROWS
        (생략...)
    */
 
  ●여전히 SORT 발생
  ●이러한 현상은 해당 인덱스 컬럼의 선언이 NOT NULL 제약조건이 없을 경우임
   단일 컬럼 인덱스일 때 값이 NULL이면 인덱스 레코드에 포함되지 않음.
   따라서 인덱스를 이용해 정렬 작업을 대체한다면 결과에 오류가 생길 수 있어 옵티마이저는
   사용자의 뜻을 따를 수 없는 것이다.
  ●GROUP BY도 마찬가지임.
   GROUP BY NOSORT를 위해 사용하려는 인덱스가 단일 컬럼 인덱스일 때는 해당 컬럼에
   NOT NULL 제약이 설정되어 있어야 제대로 동작함

  -- 해당 인덱스 컬럼을 NOT NULL로 변경 후 PLAN
  ALTER TABLE YHN_EMP MODIFY SAL NOT NULL;
 
  ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
 
  SELECT /*+ INDEX(E, YHN_EMP_IDX) */
         *
    FROM YHN_EMP E
   ORDER BY SAL;
 
  -------------------------------------------------------------------------------------------
  | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |             |       |       |  1124 (100)|          |
  |   1 |  TABLE ACCESS BY INDEX ROWID| YHN_EMP     | 14000 |   533K|  1124   (1)| 00:00:08 |
  |   2 |   INDEX FULL SCAN           | YHN_EMP_IDX | 14000 |       |    30   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------
 
  Outline Data
  -------------
    /*+
        (생략...)
        FIRST_ROWS
        (생략...)
    */
 
 ▶ORDER BY 사용시 NULL 값이 먼저 출력되도록 하려고 NULLS FIRST 구문을 사용하는 순간 실행계획에
  SORT ORDER BY가 나타남
  CREATE INDEX EMP_DEPT_ENAME_IDX ON YHN_EMP(DEPTNO , ENAME);
 
  SELECT /*+ INDEX(E, EMP_DEPT_ENAME_IDX) */
         *
    FROM YHN_EMP E
   WHERE DEPTNO = 30
   ORDER BY ENAME ;
 
  --------------------------------------------------------------------------------------------------
  | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |                    |     1 |    39 |     1   (0)| 00:00:01 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| YHN_EMP            |     1 |    39 |     1   (0)| 00:00:01 |
  |*  2 |   INDEX RANGE SCAN          | EMP_DEPT_ENAME_IDX |     1 |       |     1   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - access("DEPTNO"=30)
 
  -- ORDER BY에 NULLS FIRST를 추가할 경우
  SELECT /*+ INDEX(E, EMP_DEPT_ENAME_IDX) */
         *
    FROM YHN_EMP E
   WHERE DEPTNO = 30
   ORDER BY ENAME NULLS FIRST;
 
  ---------------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                    |     1 |    39 |     2  (50)| 00:00:01 |
  |   1 |  SORT ORDER BY               |                    |     1 |    39 |     2  (50)| 00:00:01 |
  |   2 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP            |     1 |    39 |     1   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN          | EMP_DEPT_ENAME_IDX |     1 |       |     1   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     3 - access("DEPTNO"=30)
 
  ●단일 컬럼 인덱스일 때는 NULL 값을 저장하지 않음
  ●결합 인덱스일 때는 NULL 값을 가진 레코드를 맨 뒤쪽에 저장한다.
   따라서 NULL 값부터 출력하려고 할 때는 인덱스를 이용하더라도 소트가 불가피함
   (교재 P28, 196참조)
 

이 게시물을
목록
목록
쓰기
태그