TunA

아래와 같이 테이블을 생성하자.

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

CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 100000');
CREATE TABLE t2 AS SELECT * FROM t1;

ALTER TABLE t2 ADD CONSTRAINT t2_pk PRIMARY KEY (c1);

아래는 t1, t2 테이블을 아우터 조인한 Top-N 쿼리다. t1, t2 테이블이 해시 조인되어 418개의 블록 I/O가 발생했다.

-- 2
SELECT *
  FROM (SELECT   *
            FROM t1 a
            LEFT OUTER
            JOIN t2 b
              ON b.c1 = a.c2
           WHERE a.c1 > 0
        ORDER BY a.c1) a
 WHERE ROWNUM <= 10;

--------------------------------------------------------------------
| Id  | Operation               | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |     10 |     418 |
|*  1 |  COUNT STOPKEY          |      |      1 |     10 |     418 |
|   2 |   VIEW                  |      |      1 |     10 |     418 |
|*  3 |    SORT ORDER BY STOPKEY|      |      1 |     10 |     418 |
|*  4 |     HASH JOIN OUTER     |      |      1 |    100K|     418 |
|*  5 |      TABLE ACCESS FULL  | T1   |      1 |    100K|     209 |
|   6 |      TABLE ACCESS FULL  | T2   |      1 |    100K|     209 |
--------------------------------------------------------------------

2번 쿼리는 아우터 조인한 t2 테이블의 c1 칼럼이 PK이므로 아래 쿼리처럼 Top-N 처리 후 t2 테이블을 조인해도 결과가 동일하다. t2 테이블의 NL 조인되어 블록 I/O가 229개로 감소했다.

-- 3
SELECT *
  FROM (SELECT   *
            FROM t1 a
           WHERE a.c1 > 0
        ORDER BY a.c1) a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c2
 WHERE ROWNUM <= 10;

---------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |     10 |     229 |
|*  1 |  COUNT STOPKEY                |       |      1 |     10 |     229 |
|   2 |   NESTED LOOPS OUTER          |       |      1 |     10 |     229 |
|   3 |    VIEW                       |       |      1 |     10 |     209 |
|   4 |     SORT ORDER BY             |       |      1 |     10 |     209 |
|*  5 |      TABLE ACCESS FULL        | T1    |      1 |    100K|     209 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2    |     10 |     10 |      20 |
|*  7 |     INDEX UNIQUE SCAN         | T2_PK |     10 |     10 |      10 |
---------------------------------------------------------------------------

아래와 같이 t2 테이블에 대한 동적 조건이 존재한다면 쿼리를 3번 쿼리처럼 변경할 수 없다. 동적 조건과 함께 아우터 조인까지 동적으로 처리하는 방식이 성능 측면에서 가장 효율적이지만 개발 난이도가 높아지는 단점이 있다.

-- 4
SELECT *
  FROM (SELECT   *
            FROM t1 a
            LEFT OUTER
            JOIN t2 b
              ON b.c1 = a.c2
           WHERE a.c1 > 0
             AND b.c2 > 0 -- 동적 조건
        ORDER BY a.c1) a
 WHERE ROWNUM <= 10;

--------------------------------------------------------------------
| Id  | Operation               | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |     10 |     418 |
|*  1 |  COUNT STOPKEY          |      |      1 |     10 |     418 |
|   2 |   VIEW                  |      |      1 |     10 |     418 |
|*  3 |    SORT ORDER BY STOPKEY|      |      1 |     10 |     418 |
|*  4 |     HASH JOIN           |      |      1 |    100K|     418 |
|*  5 |      TABLE ACCESS FULL  | T1   |      1 |    100K|     209 |
|*  6 |      TABLE ACCESS FULL  | T2   |      1 |    100K|     209 |
--------------------------------------------------------------------

아래와 같이 쿼리를 변경해보자. 인라인 뷰의 SELECT 목록을 t1 테이블만 조회했고, Top-N 처리 후에 t2 테이블을 아우터 조인했다. 중복 조인으로 인해 블록 I/O가 5번 쿼리보다 20개 증가했다. 전체 블록 I/O에 비해 크지 않은 수치로 볼 수 있다.

-- 5
SELECT *
  FROM (SELECT   a.*      -- 변경
            FROM t1 a
            LEFT OUTER
            JOIN t2 b
              ON b.c1 = a.c2
           WHERE a.c1 > 0
             AND b.c2 > 0 -- 동적 조건
        ORDER BY a.c1) a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c2
 WHERE ROWNUM <= 10;

---------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |     10 |     438 |
|*  1 |  COUNT STOPKEY                |       |      1 |     10 |     438 |
|   2 |   NESTED LOOPS OUTER          |       |      1 |     10 |     438 |
|   3 |    VIEW                       |       |      1 |     10 |     418 |
|   4 |     SORT ORDER BY             |       |      1 |     10 |     418 |
|*  5 |      HASH JOIN                |       |      1 |    100K|     418 |
|*  6 |       TABLE ACCESS FULL       | T1    |      1 |    100K|     209 |
|*  7 |       TABLE ACCESS FULL       | T2    |      1 |    100K|     209 |
|   8 |    TABLE ACCESS BY INDEX ROWID| T2    |     10 |     10 |      20 |
|*  9 |     INDEX UNIQUE SCAN         | T2_PK |     10 |     10 |      10 |
---------------------------------------------------------------------------

아래 쿼리는 동적 조건이 비활성화된 경우를 가정하여 동적 조건을 주석 처리했다. 쿼리 변환에 의해 인라인 뷰 내에서 t2 테이블의 조인이 제거되었다. 블록 I/O가 3번 쿼리와 동일하게 229개만 발생한다. 쿼리 변환을 통해 동적 조건이 비활성화된 경우 블록 I/O를 감소시키는 개선 방안이다.

-- 6
SELECT *
  FROM (SELECT   a.*      -- 변경
            FROM t1 a
            LEFT OUTER
            JOIN t2 b
              ON b.c1 = a.c2
           WHERE a.c1 > 0
          -- AND b.c2 > 0 -- 동적 조건
        ORDER BY a.c1) a
  LEFT OUTER
  JOIN t2 b
    ON b.c1 = a.c2
 WHERE ROWNUM <= 10;

---------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |     10 |     229 |
|*  1 |  COUNT STOPKEY                |       |      1 |     10 |     229 |
|   2 |   NESTED LOOPS OUTER          |       |      1 |     10 |     229 |
|   3 |    VIEW                       |       |      1 |     10 |     209 |
|   4 |     SORT ORDER BY             |       |      1 |     10 |     209 |
|*  5 |      TABLE ACCESS FULL        | T1    |      1 |    100K|     209 |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2    |     10 |     10 |      20 |
|*  7 |     INDEX UNIQUE SCAN         | T2_PK |     10 |     10 |      10 |
---------------------------------------------------------------------------

스칼라 서브 쿼리도 동일한 기법을 활용할 수 있다. 아래 쿼리는 동적 조건에 스칼라 서브 쿼리의 결과를 사용했다. 101,000개의 블록 I/O가 발생했다.

-- 7
SELECT a.*
  FROM (SELECT   a.*
               , (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS xc2
            FROM t1 a
        ORDER BY a.c1) a
 WHERE ROWNUM <= 10
   AND a.xc2 > 0 -- 동적 조건
;

--------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |     10 |     101K|
|   1 |  SORT AGGREGATE              |       |    100K|    100K|     101K|
|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |    100K|    100K|     101K|
|*  3 |    INDEX UNIQUE SCAN         | T2_PK |    100K|    100K|    1462 |
|*  4 |  COUNT STOPKEY               |       |      1 |     10 |     101K|
|*  5 |   VIEW                       |       |      1 |     10 |     101K|
|   6 |    SORT ORDER BY             |       |      1 |     10 |     101K|
|   7 |     TABLE ACCESS FULL        | T1    |      1 |    100K|     209 |
--------------------------------------------------------------------------

아래와 같이 쿼리를 변경해보자. 메인 쿼리의 SELECT 목록에서 t1 테이블만 조회했고, 스칼라 서브 쿼리를 중복 기술했다. 중복 스칼라 서브 쿼리로 인해 블록 I/O가 7번 쿼리보다 20개 증가했다.

-- 8
SELECT a.c1 -- 변경
     , a.c2 -- 변경
     , (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS xc2
  FROM (SELECT   a.*
               , (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS xc2
            FROM t1 a
        ORDER BY a.c1) a
 WHERE ROWNUM <= 10
   AND a.xc2 > 0 -- 동적 조건
;

----------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |      1 |     10 |     101K|
|   1 |  SORT AGGREGATE                |       |     10 |     10 |      20 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | T2    |     10 |     10 |      20 |
|*  3 |    INDEX UNIQUE SCAN           | T2_PK |     10 |     10 |      10 |
|*  4 |  COUNT STOPKEY                 |       |      1 |     10 |     101K|
|*  5 |   VIEW                         |       |      1 |     10 |     101K|
|   6 |    SORT AGGREGATE              |       |    100K|    100K|     101K|
|   7 |     TABLE ACCESS BY INDEX ROWID| T2    |    100K|    100K|     101K|
|*  8 |      INDEX UNIQUE SCAN         | T2_PK |    100K|    100K|    1462 |
|   9 |    SORT ORDER BY               |       |      1 |     10 |     101K|
|  10 |     TABLE ACCESS FULL          | T1    |      1 |    100K|     209 |
----------------------------------------------------------------------------

아래 쿼리는 동적 조건을 주석 처리했다. 아우터 조인과 마찬가지로 쿼리 변환에 의해 스칼라 서브 쿼리가 제거되어 블록 I/O가 209개만 발생한다.

-- 9
SELECT a.c1 -- 변경
     , a.c2 -- 변경
     , (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS c2x
  FROM (SELECT   a.*
               , (SELECT MAX (x.c2) FROM t2 x WHERE x.c1 = a.c2) AS xc2
            FROM t1 a
        ORDER BY a.c1) a
 WHERE ROWNUM <= 10
-- AND a.xc2 > 0 -- 동적 조건
;

--------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |     10 |     209 |
|   1 |  SORT AGGREGATE              |       |     10 |     10 |      20 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2    |     10 |     10 |      20 |
|*  3 |    INDEX UNIQUE SCAN         | T2_PK |     10 |     10 |      10 |
|*  4 |  COUNT STOPKEY               |       |      1 |     10 |     209 |
|   5 |   VIEW                       |       |      1 |     10 |     209 |
|*  6 |    SORT ORDER BY STOPKEY     |       |      1 |     10 |     209 |
|   7 |     TABLE ACCESS FULL        | T1    |      1 |    100K|     209 |
--------------------------------------------------------------------------