조인 순서 변경에 의한 수행 시간 증가

조인 순서를 변경하여 블록 I/O가 감소했지만, 수행 시간이 증가한 사례를 살펴보자.

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

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

CREATE TABLE t1 AS
SELECT ROWNUM AS c1
     , ROWNUM AS c2
     , LPAD ('X', 100, 'X') AS c3
     , LPAD ('X', 100, 'X') AS c4
     , LPAD ('X', 100, 'X') AS c5
     , LPAD ('X', 100, 'X') AS c6
     , LPAD ('X', 100, 'X') AS c7
  FROM XMLTABLE ('1 to 100000');

INSERT INTO t1 VALUES (0, 0, 'Y', 'Y', 'Y', 'Y', 'Y');
COMMIT;

CREATE TABLE t2 AS SELECT * FROM t1;

CREATE INDEX t1_x1 ON t1 (c1);
CREATE INDEX t2_x1 ON t2 (c1);

아래에서 2-1번 쿼리는 t1을 먼저 읽고 t2를 10,000번 조인했지만 결과로 1건을 반환했다. 2-2번 쿼리는 조인 순서를 변경하여 t2 테이블을 먼저 읽고 t1을 1번만 조인했다. 블록 I/O는 8,640에서 7,706로 934만큼 감소했지만, 수행 시간이 0.44초에서 3.65초로 증가한 것을 확인할 수 있다. 2-1번 쿼리는 5개의 REGEXP_LIKE 표현식을 10,000번 수행했지만, 2-2번 쿼리는 10배인 100,000번이나 수행한 것이 원인이다.

-- 2-1
SELECT /*+ LEADING(A) USE_NL(B) */
       *
  FROM t1 a, t2 b
 WHERE a.c2 < 10000
   AND b.c1 = a.c1
   AND (   REGEXP_LIKE (b.c3, 'Y')
        OR REGEXP_LIKE (b.c4, 'Y')
        OR REGEXP_LIKE (b.c5, 'Y')
        OR REGEXP_LIKE (b.c6, 'Y')
        OR REGEXP_LIKE (b.c7, 'Y'));

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      1 |00:00:00.44 |    8640 |
|   1 |  NESTED LOOPS                |       |      1 |      1 |00:00:00.44 |    8640 |
|   2 |   NESTED LOOPS               |       |      1 |  10000 |00:00:00.03 |    7870 |
|*  3 |    TABLE ACCESS FULL         | T1    |      1 |  10000 |00:00:00.02 |    7720 |
|*  4 |    INDEX RANGE SCAN          | T2_X1 |  10000 |  10000 |00:00:00.01 |     150 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T2    |  10000 |      1 |00:00:00.41 |     770 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("A"."C2"<10000)
   4 - access("B"."C1"="A"."C1")
   5 - filter(( REGEXP_LIKE ("B"."C3",'Y',<not feasible="">)


-- 2-2
SELECT /*+ LEADING(B) USE_NL(A) */
       *
  FROM t1 a, t2 b
 WHERE a.c2 < 10000
   AND b.c1 = a.c1
   AND (   REGEXP_LIKE (b.c3, 'Y')
        OR REGEXP_LIKE (b.c4, 'Y')
        OR REGEXP_LIKE (b.c5, 'Y')
        OR REGEXP_LIKE (b.c6, 'Y')
        OR REGEXP_LIKE (b.c7, 'Y'));

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |      1 |00:00:03.65 |    7706 |
|   1 |  NESTED LOOPS                |       |      1 |      1 |00:00:03.65 |    7706 |
|   2 |   NESTED LOOPS               |       |      1 |      1 |00:00:03.65 |    7705 |
|*  3 |    TABLE ACCESS FULL         | T2    |      1 |      1 |00:00:03.65 |    7702 |
|*  4 |    INDEX RANGE SCAN          | T1_X1 |      1 |      1 |00:00:00.01 |       3 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T1    |      1 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(( REGEXP_LIKE ("B"."C3",'Y',<not feasible="">)
   4 - access("B"."C1"="A"."C1")
   5 - filter("A"."C2"<10000)

SQL Monitor를 확인해보면 2-2번 쿼리의 Cpu Time(s)가 3.65초인 것을 확인할 수 있다. V$SQL 뷰의 cpu_time 열에서도 해당 정보를 확인할 수 있다. 일반적인 경우는 아니지만 과도한 표현식을 사용한 쿼리의 경우 조인 순서 변경에 주의할 필요가 있다.

-- 3-1: 2-1
======================================
| Elapsed |   Cpu   | Fetch | Buffer |
| Time(s) | Time(s) | Calls |  Gets  |
======================================
|    0.44 |    0.44 |     2 |   8640 |
======================================

-- 3-2: 2-2
======================================
| Elapsed |   Cpu   | Fetch | Buffer |
| Time(s) | Time(s) | Calls |  Gets  |
======================================
|    3.65 |    3.65 |     2 |   7706 |
======================================