뷰 병합이 동작하지 않는 사례

뷰 병합이 동작하지 않아 쿼리 성능이 저하된 사례를 살펴보자.

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

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

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

CREATE UNIQUE INDEX t1_u1 ON t1 (c2);
CREATE UNIQUE INDEX t2_u1 ON t2 (c1);
CREATE UNIQUE INDEX t3_u1 ON t3 (c1);

CREATE OR REPLACE VIEW v1 AS SELECT * FROM t2;

아래 쿼리는 테이블과 뷰를 조인한다. v1 뷰가 병합된 것을 확인할 수 있다.

-- 2
SELECT *
  FROM t1 a, v1 b
 WHERE a.c2 = 1
   AND b.c1 = a.c1;

-----------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |      1 |       6 |
|   1 |  NESTED LOOPS                |       |      1 |      1 |      1 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |      1 |       3 |
|*  3 |    INDEX UNIQUE SCAN         | T1_U1 |      1 |      1 |      1 |       2 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2    |      1 |      1 |      1 |       3 |
|*  5 |    INDEX UNIQUE SCAN         | T2_U1 |      1 |      1 |      1 |       2 |
-----------------------------------------------------------------------------------

아래와 같이 뷰에 스칼라 서브 쿼리와 스칼라 서브 쿼리의 결과를 사용한 필터 조건을 추가해보자.

-- 3
CREATE OR REPLACE VIEW v1
AS
SELECT *
  FROM (SELECT a.c1
             , (SELECT x.c2 FROM t3 x WHERE x.c1 = a.c2) AS c2 --> ADD
          FROM t2 a)
 WHERE c2 = 1 --> ADD
;

쿼리를 다시 수행해보면 v1 뷰가 병합되지 않은 것을 확인할 수 있다. 스칼라 서브 쿼리가 10,000번 수행되어 블록 I/O가 10,175개로 증가했다.

-- 4
SELECT /*+ MERGE(B) */
       *
  FROM t1 a, v1 b
 WHERE a.c2 = 1
   AND b.c1 = a.c1;

-----------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |      1 |   10175 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T3    |  10000 |      1 |  10000 |   10142 |
|*  2 |   INDEX UNIQUE SCAN          | T3_U1 |  10000 |      1 |  10000 |     142 |
|   3 |  NESTED LOOPS                |       |      1 |      1 |      1 |   10175 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |      1 |       3 |
|*  5 |    INDEX UNIQUE SCAN         | T1_U1 |      1 |      1 |      1 |       2 |
|*  6 |   VIEW                       |       |      1 |      1 |      1 |   10172 |
|   7 |    TABLE ACCESS FULL         | T2    |      1 |  10000 |  10000 |      30 |
-----------------------------------------------------------------------------------

힌트를 추가해도 JPPD가 동작하지 않는 것을 확인할 수 있다.

-- 5
SELECT /*+ LEADING(A) USE_NL(B) PUSH_PRED(B) */
       *
  FROM t1 a, v1 b
 WHERE a.c2 = 1
   AND b.c1 = a.c1;

-----------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |      1 |   10175 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T3    |  10000 |      1 |  10000 |   10142 |
|*  2 |   INDEX UNIQUE SCAN          | T3_U1 |  10000 |      1 |  10000 |     142 |
|   3 |  NESTED LOOPS                |       |      1 |      1 |      1 |   10175 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |      1 |       3 |
|*  5 |    INDEX UNIQUE SCAN         | T1_U1 |      1 |      1 |      1 |       2 |
|*  6 |   VIEW                       |       |      1 |      1 |      1 |   10172 |
|   7 |    TABLE ACCESS FULL         | T2    |      1 |  10000 |  10000 |      30 |
-----------------------------------------------------------------------------------

조인을 아우터 조인으로 변경하면 JPPD가 동작하지만, 뷰를 사용하는 쿼리가 다수인 경우 근본적인 해법이 될 수 없다.

-- 6
SELECT /*+ LEADING(A) USE_NL(B) PUSH_PRED(B) NO_OUTER_JOIN_TO_INNER */
       *
  FROM t1 a, v1 b
 WHERE a.c2 = 1
   AND b.c1(+) = a.c1
   AND b.c1 IS NOT NULL;

-------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Starts | E-Rows | A-Rows | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |      1 |        |      1 |       9 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | T3    |      1 |      1 |      1 |       3 |
|*  2 |   INDEX UNIQUE SCAN            | T3_U1 |      1 |      1 |      1 |       2 |
|*  3 |  FILTER                        |       |      1 |        |      1 |       9 |
|   4 |   NESTED LOOPS OUTER           |       |      1 |      1 |      1 |       9 |
|   5 |    TABLE ACCESS BY INDEX ROWID | T1    |      1 |      1 |      1 |       3 |
|*  6 |     INDEX UNIQUE SCAN          | T1_U1 |      1 |      1 |      1 |       2 |
|*  7 |    VIEW PUSHED PREDICATE       |       |      1 |      1 |      1 |       6 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T2    |      1 |      1 |      1 |       3 |
|*  9 |      INDEX UNIQUE SCAN         | T2_U1 |      1 |      1 |      1 |       2 |
-------------------------------------------------------------------------------------

해당 현상은 스칼라 서브 쿼리의 결과를 필터 조건으로 사용했기 때문이다. t3 테이블의 c1 열이 고유하므로 뷰의 스칼라 서브 쿼리를 조인으로 변경할 수 있다.

-- 7
CREATE OR REPLACE VIEW v1
AS
SELECT a.c1, b.c2
  FROM t2 a, t3 b
 WHERE b.c1(+) = a.c2
   AND b.c2(+) = 1;

v1 뷰가 병합되어 블록 I/O가 9개로 감소한 것을 확인할 수 있다.

-- 8
SELECT *
  FROM t1 a, v1 b
 WHERE a.c2 = 1
   AND b.c1 = a.c1;

------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |       9 |
|   1 |  NESTED LOOPS OUTER           |       |      1 |      1 |      1 |       9 |
|   2 |   NESTED LOOPS                |       |      1 |      1 |      1 |       6 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |      1 |       3 |
|*  4 |     INDEX UNIQUE SCAN         | T1_U1 |      1 |      1 |      1 |       2 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |      1 |      1 |       3 |
|*  6 |     INDEX UNIQUE SCAN         | T2_U1 |      1 |      1 |      1 |       2 |
|*  7 |   TABLE ACCESS BY INDEX ROWID | T3    |      1 |      1 |      1 |       3 |
|*  8 |    INDEX UNIQUE SCAN          | T3_U1 |      1 |      1 |      1 |       2 |
------------------------------------------------------------------------------------