고유 값에 대한 상관 서브 쿼리 성능 개선 방안

이번 글에서는 고유 값에 대한 상관 서브 쿼리의 성능 개선 방안을 살펴보자.

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

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1, LPAD ('X', 100, 'X') AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT c1 + 1 AS c1, c2 FROM t1;

CREATE UNIQUE INDEX t1_u1 ON t1 (c1);
CREATE INDEX t2_x1 ON t2 (c1);

아래는 전형적인 상관 서브 쿼리다. t1 테이블을 랜덤 액세스하는 과정(2번 오퍼레이션)에서 155개의 블록 I/O가 발생했다.

-- 2
SELECT /*+ INDEX(A) */
       a.*
  FROM t1 a
 WHERE a.c1 > 0
   AND NOT EXISTS (SELECT /*+ UNNEST NL_AJ */
                          1
                     FROM t2 x
                    WHERE x.c1 = a.c1);

--------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      1 |     305 |
|   1 |  NESTED LOOPS ANTI           |       |      1 |      1 |     305 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |  10000 |     177 | -- !
|*  3 |    INDEX RANGE SCAN          | T1_U1 |      1 |  10000 |      22 |
|*  4 |   INDEX RANGE SCAN           | T2_X1 |  10000 |   9999 |     128 |
--------------------------------------------------------------------------

랜덤 액세스를 줄이기 위해 t1 테이블 셀프 조인하는 기법을 사용할 수 있다. 2번 쿼리에 비해 블록 I/O가 감소한 것을 확인할 수 있다.

-- 3
SELECT /*+ INDEX(A) ROWID(B) NO_ELIMINATE_JOIN(A) NO_ELIMINATE_JOIN(B) */
       b.*
  FROM t1 a, t1 b
 WHERE a.c1 > 0
   AND NOT EXISTS (SELECT /*+ UNNEST NL_AJ */
                          1
                     FROM t2 x
                    WHERE x.c1 = a.c1)
   AND b.ROWID = a.ROWID;

-------------------------------------------------------------------------
| Id  | Operation                   | Name  | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |      1 |      1 |     151 |
|   1 |  NESTED LOOPS               |       |      1 |      1 |     151 |
|   2 |   NESTED LOOPS ANTI         |       |      1 |      1 |     150 |
|*  3 |    INDEX RANGE SCAN         | T1_U1 |      1 |  10000 |      22 |
|*  4 |    INDEX RANGE SCAN         | T2_X1 |  10000 |   9999 |     128 |
|   5 |   TABLE ACCESS BY USER ROWID| T1    |      1 |      1 |       1 | -- !
-------------------------------------------------------------------------

t1 테이블의 c1 열이 고유하므로 아래처럼 MINUS 연산자를 사용할 수도 있다. 블록 I/O가 46개로 감소했지만, 소트 과정에서 848K의 PGA를 사용했다. 테이블 크기가 작고, 결과 건수가 적을 경우에 사용할 수 있는 방식이다. 

-- 4
SELECT /*+ LEADING(A) USE_NL(B) */
       b.*
  FROM (SELECT c1 FROM t1 WHERE c1 > 0
        MINUS
        SELECT c1 FROM t2 WHERE c1 > 0) a
     , t1 b
 WHERE b.c1 = a.c1;

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      1 |      46 |          |
|   1 |  NESTED LOOPS                |       |      1 |      1 |      46 |          |
|   2 |   NESTED LOOPS               |       |      1 |      1 |      45 |          |
|   3 |    VIEW                      |       |      1 |      1 |      43 |          |
|   4 |     MINUS                    |       |      1 |      1 |      43 |          |
|   5 |      SORT UNIQUE             |       |      1 |  10000 |      21 |  424K (0)| -- !
|*  6 |       INDEX RANGE SCAN       | T1_U1 |      1 |  10000 |      21 |          |
|   7 |      SORT UNIQUE             |       |      1 |  10000 |      22 |  424K (0)| -- !
|*  8 |       INDEX RANGE SCAN       | T2_X1 |      1 |  10000 |      22 |          |
|*  9 |    INDEX UNIQUE SCAN         | T1_U1 |      1 |      1 |       2 |          |
|  10 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |       1 |          |
-------------------------------------------------------------------------------------

아래 쿼리의 MINUS 연산자는 SORT UNIQUE NOSORT 오퍼레이션으로 동작했다. 인덱스를 사용했기 때문이다. 직전 쿼리의 SORT UNIQUE 오퍼레이션은 최적화되지 못한 동작으로 판단된다.

-- 5
SELECT c1 FROM t1 WHERE c1 > 0
MINUS
SELECT c1 FROM t2 WHERE c1 > 0;

-----------------------------------------------------------------
| Id  | Operation           | Name  | Starts | A-Rows | Buffers |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |      1 |      1 |      45 |
|   1 |  MINUS              |       |      1 |      1 |      45 |
|   2 |   SORT UNIQUE NOSORT|       |      1 |  10000 |      22 | -- !
|*  3 |    INDEX RANGE SCAN | T1_U1 |      1 |  10000 |      22 |
|   4 |   SORT UNIQUE NOSORT|       |      1 |  10000 |      23 | -- !
|*  5 |    INDEX RANGE SCAN | T2_X1 |      1 |  10000 |      23 |
-----------------------------------------------------------------

소트를 제거할 수 없으므로 인덱스 스캔 범위가 넓은 경우라면 INDEX_FFS 힌트를 고려할 수 있다. 블록 I/O는 다소 증가하지만 Multiblock I/O를 통해 수행 시간을 단축할 수 있다.

-- 6
SELECT /*+ LEADING(A) USE_NL(B) */
       b.*
  FROM (SELECT /*+ INDEX_FFS(T1) */ c1 FROM t1 WHERE c1 > 0
        MINUS
        SELECT /*+ INDEX_FFS(T2) */ c1 FROM t2 WHERE c1 > 0) a
     , t1 b
 WHERE b.c1 = a.c1;

------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |Used-Mem |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      1 |      56 |         |
|   1 |  NESTED LOOPS                |       |      1 |      1 |      56 |         |
|   2 |   NESTED LOOPS               |       |      1 |      1 |      55 |         |
|   3 |    VIEW                      |       |      1 |      1 |      53 |         |
|   4 |     MINUS                    |       |      1 |      1 |      53 |         |
|   5 |      SORT UNIQUE             |       |      1 |  10000 |      26 | 424K (0)|
|*  6 |       INDEX FAST FULL SCAN   | T1_U1 |      1 |  10000 |      26 |         | -- !
|   7 |      SORT UNIQUE             |       |      1 |  10000 |      27 | 424K (0)|
|*  8 |       INDEX FAST FULL SCAN   | T2_X1 |      1 |  10000 |      27 |         | -- !
|*  9 |    INDEX UNIQUE SCAN         | T1_U1 |      1 |      1 |       2 |         |
|  10 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |       1 |         |
------------------------------------------------------------------------------------