[SQL Tip] 오라클 실행계획 변경 사례 #2 – CTAS vs SELECT

지난 글에서 Oracle SQL과 ANSI SQL의 실행 계획 변경 사례를 소개했습니다. 이 때 사례로 사용한 쿼리는 CTAS(Create Table As Select) 유형입니다.

CTAS는 SELECT 쿼리 실행 결과를 그대로 테이블로 생성해주는 DDL문입니다. 일반적으로 SELECT 문 성능에 이상 없으면 CTAS로 변형하더라도 데이터 쓰기(Write) 부하만 추가되고 다른 성능은 큰 차이가 없습니다. SELECT 절에 해당하는 부분의 실행 계획이 동일하기 때문입니다.

하지만 이 글의 사례처럼 SELECT 문과 CTAS 문의 실행 계획이 다른 경우가 발생하기도 합니다.

준비

먼저 테스트 테이블을 준비합니다. 지난 글에서 사용한 테이블과 같습니다.

CREATE TABLE T1  
AS 
SELECT 1 C1, 'A' C2 FROM DUAL UNION ALL 
SELECT 2 C1, 'B' C2 FROM DUAL UNION ALL 
SELECT 3 C1, 'C' C2 FROM DUAL;

CREATE TABLE T2 
AS 
SELECT 1 C1, 'A' C2 FROM DUAL UNION ALL 
SELECT 2 C1, 'B' C2 FROM DUAL;

쿼리 1 – CTAS

CTAS 쿼리는 이전 글의 결과와 마찬가지로 소트머지 조인과 Later View 쿼리 변환이 발생합니다.

CREATE TABLE TEST1 
AS 
SELECT /*+ LEADING(A) USE_HASH(B) */
       A.C1 AS X1 
     , A.C2 AS X2 
     , B.C1 AS X3 
     , B.C2 AS X4 
FROM   T1 A LEFT OUTER JOIN T2 B 
ON     A.C1 = B.C1 
AND    A.C2 IN ( 'A', 'C' );

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT           |                 |     3 |    63 |    13   (0)| 00:00:01 |
|   1 |  LOAD AS SELECT                  | TEST1           |       |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |                 |     3 |    63 |    12   (0)| 00:00:01 |
|   3 |    MERGE JOIN OUTER              |                 |     3 |    63 |    12   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL            | T1              |     3 |    15 |     3   (0)| 00:00:01 |
|   5 |     BUFFER SORT                  |                 |     1 |    16 |     9   (0)| 00:00:01 |
|   6 |      VIEW                        | VW_LAT_DF9E236D |     1 |    16 |     3   (0)| 00:00:01 |
|*  7 |       FILTER                     |                 |       |       |            |          |
|*  8 |        TABLE ACCESS FULL         | T2              |     1 |     5 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - filter("A"."C2"='A' OR "A"."C2"='C')
   8 - filter("A"."C1"="B"."C1")
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   8 -  SEL$1 / B@SEL$1
         U -  USE_HASH(B)

쿼리 2 – SELECT

CTAS 문의 SELECT 부분만 따로 실행했을 때는 옵티마이저 힌트가 적용되고 해시 조인으로 실행되는 것을 볼 수 있습니다.

SELECT /*+ LEADING(A) USE_HASH(B) */
       A.C1 AS X1 
     , A.C2 AS X2 
     , B.C1 AS X3 
     , B.C2 AS X4 
FROM   T1 A LEFT OUTER JOIN T2 B 
ON     A.C1 = B.C1 
AND    A.C2 IN ( 'A', 'C' );

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     4 |    40 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     4 |    40 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     3 |    15 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     2 |    10 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."C1"="B"."C1"(+))
       filter("A"."C2"=CASE  WHEN ("B"."C1"(+) IS NOT NULL) THEN 'A' 
              ELSE 'A' END  OR "A"."C2"=CASE  WHEN ("B"."C1"(+) IS NOT NULL) THEN 'C' 
              ELSE 'C' END )

쿼리 3 – INSERT … SELECT

이번에는 CTAS와 유사한 INSERT … SELECT 문의 실행 계획을 보겠습니다. 테스트 결과 SELECT 부분은 CTAS 문과 동일한 실행 계획이 나타납니다.

INSERT INTO TEST
SELECT /*+ LEADING(A) USE_HASH(B) */
       A.C1 AS X1 
     , A.C2 AS X2 
     , B.C1 AS X3 
     , B.C2 AS X4 
FROM   T1 A LEFT OUTER JOIN T2 B 
ON     A.C1 = B.C1 
AND    A.C2 IN ( 'A', 'C' );

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                 |     3 |    63 |    12   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST            |       |       |            |          |
|   2 |   MERGE JOIN OUTER       |                 |     3 |    63 |    12   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T1              |     3 |    15 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT           |                 |     1 |    16 |     9   (0)| 00:00:01 |
|   5 |     VIEW                 | VW_LAT_DF9E236D |     1 |    16 |     3   (0)| 00:00:01 |
|*  6 |      FILTER              |                 |       |       |            |          |
|*  7 |       TABLE ACCESS FULL  | T2              |     1 |     5 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - filter("A"."C2"='A' OR "A"."C2"='C')
   7 - filter("A"."C1"="B"."C1")
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   7 -  SEL$1 / B@SEL$1
         U -  USE_HASH(B)

결론

CTAS(Create Table As Select) 문의 SELECT 부분 실행 계획은 SELECT 문을 단독으로 실행할 때의 실행 계획과 다를 수 있으므로 주의해야 합니다. INSERT … SELECT 문 또한 마찬가지 입니다.

실행 계획을 확인할 때는 부분 쿼리의 실행 계획이 이상 없더라도 실행하려는 전체 쿼리 관점에서 최종 실행 계획을 확인하는 것이 필요합니다.

관련 게시물

위로 스크롤