소트 #3 - IN 절

인덱스 선두 칼럼에 IN 절을 사용하면 소트를 제거할 수 없다.

 

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 VARCHAR2 (4000));
CREATE INDEX t1_x1 ON t1 (c1, c2);

INSERT INTO t1 SELECT 1, ROWNUM, LPAD ('X', 4000, 'X') FROM XMLTABLE ('1 to 5');
INSERT INTO t1 SELECT 2, ROWNUM, LPAD ('X', 4000, 'X') FROM XMLTABLE ('1 to 10000');
COMMIT;

 

아래 쿼리는 c1 열에 IN 절을 사용했기 때문에 인덱스로 소트를 회피할 수 없다. 전체 범위로 처리되어 10,031개의 블록 I/O가 발생했다.

-- 2
SELECT c1, c2, LTRIM (c3, 'X') AS c3
  FROM (SELECT   *
            FROM t1
           WHERE c1 IN (1, 2)
        ORDER BY c2)
 WHERE ROWNUM <= 10;

C1 C2 C3
-- -- --
 1  1
 2  1
 1  2
 2  2
 1  3
 2  3
 1  4
 2  4
 1  5
 2  5

10 행이 선택되었습니다.

-------------------------------------------------------------------------------------
| Id  | Operation                               | Name  | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |       |      1 |     10 |   10031 |
|*  1 |  COUNT STOPKEY                          |       |      1 |     10 |   10031 |
|   2 |   VIEW                                  |       |      1 |     10 |   10031 |
|*  3 |    SORT ORDER BY STOPKEY                |       |      1 |     10 |   10031 |
|   4 |     INLIST ITERATOR                     |       |      1 |  10005 |   10031 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      2 |  10005 |   10031 |
|*  6 |       INDEX RANGE SCAN                  | T1_X1 |      2 |  10005 |      26 |
-------------------------------------------------------------------------------------

 

IN 절의 값 목록이 고정적이라면 아래와 같이 UNION ALL 연산자를 사용하여 성능을 개선할 수 있다. 19개의 I/O를 확인할 수 있다.

-- 3
SELECT c1, c2, LTRIM (c3, 'X') AS c3
  FROM (SELECT   *
            FROM (SELECT *
                    FROM (SELECT * FROM t1 WHERE c1 = 1 ORDER BY c2)
                   WHERE ROWNUM <= 10
                  UNION ALL
                  SELECT *
                    FROM (SELECT * FROM t1 WHERE c1 = 2 ORDER BY c2)
                   WHERE ROWNUM <= 10)
        ORDER BY c2)
 WHERE ROWNUM <= 10;

C1 C2 C3
-- -- --
 1  1
 2  1
 1  2
 2  2
 1  3
 2  3
 1  4
 2  4
 1  5
 2  5

10 행이 선택되었습니다.

--------------------------------------------------------------------------------
| Id  | Operation                          | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |       |      1 |     10 |      19 |
|*  1 |  COUNT STOPKEY                     |       |      1 |     10 |      19 |
|   2 |   VIEW                             |       |      1 |     10 |      19 |
|*  3 |    SORT ORDER BY STOPKEY           |       |      1 |     10 |      19 |
|   4 |     VIEW                           |       |      1 |     15 |      19 |
|   5 |      UNION-ALL                     |       |      1 |     15 |      19 |
|*  6 |       COUNT STOPKEY                |       |      1 |      5 |       7 |
|   7 |        VIEW                        |       |      1 |      5 |       7 |
|   8 |         TABLE ACCESS BY INDEX ROWID| T1    |      1 |      5 |       7 |
|*  9 |          INDEX RANGE SCAN          | T1_X1 |      1 |      5 |       2 |
|* 10 |       COUNT STOPKEY                |       |      1 |     10 |      12 |
|  11 |        VIEW                        |       |      1 |     10 |      12 |
|  12 |         TABLE ACCESS BY INDEX ROWID| T1    |      1 |     10 |      12 |
|* 13 |          INDEX RANGE SCAN          | T1_X1 |      1 |     10 |       2 |
--------------------------------------------------------------------------------