스칼라 서브 쿼리의 실행 계획 #1 - 표시 순서

스칼라 서브 쿼리는 실행 계획 상단에 별도로 표시된다. 다수의 스칼라 서브 쿼리를 사용했을 경우는 어떨까? 관련 내용을 살펴보자.

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

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

CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);
CREATE TABLE t2 (c1 NUMBER, c2 NUMBER);
CREATE TABLE t3 (c1 NUMBER, c2 NUMBER);
CREATE TABLE t4 (c1 NUMBER, c2 NUMBER);

CREATE INDEX t2_x1 ON t2 (c1);
CREATE INDEX t3_x1 ON t3 (c1);
CREATE INDEX t4_x1 ON t4 (c1);

아래 쿼리는 인라인 뷰(t2)와 메인 쿼리(t3)에 스칼라 서브 쿼리를 사용했다. 최종 SELECT 목록 순서(t3 -> t2)대로 스칼라 서브 쿼리가 표시되는 것을 확인할 수 있다.

-- 2
SELECT (SELECT y.c2
          FROM t3 y
         WHERE y.c1 = a.c1) AS t3
     , a.*
  FROM (SELECT a.*
             , (SELECT x.c2
                  FROM t2 x
                 WHERE x.c1 = a.c1) AS t2
          FROM t1 a) a;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3    |
|*  2 |   INDEX RANGE SCAN          | T3_X1 |
|   3 |  TABLE ACCESS BY INDEX ROWID| T2    |
|*  4 |   INDEX RANGE SCAN          | T2_X1 |
|   5 |  TABLE ACCESS FULL          | T1    |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("Y"."C1"=:B1)
   4 - access("X"."C1"=:B1)

아래 쿼리는 스칼라 서브 쿼리를 중첩했다. 중첩 스칼라 서브 쿼리의 안쪽(t3)부터 표시되는 것을 확인할 수 있다.

-- 3
SELECT a.*
     , (SELECT (SELECT y.c2
                  FROM t3 y
                 WHERE y.c1 = x.c1) AS t4
          FROM t2 x
         WHERE x.c1 = a.c1
           AND x.c2 > 0) AS t3
  FROM t1 a;

---------------------------------------------
| Id  | Operation                   | Name  |
---------------------------------------------
|   0 | SELECT STATEMENT            |       |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3    |
|*  2 |   INDEX RANGE SCAN          | T3_X1 |
|*  3 |  TABLE ACCESS BY INDEX ROWID| T2    |
|*  4 |   INDEX RANGE SCAN          | T2_X1 |
|   5 |  TABLE ACCESS FULL          | T1    |
---------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("Y"."C1"=:B1)
   3 - filter("X"."C2">0)
   4 - access("X"."C1"=:B1)

쿼리 2, 3에서 얻을 수 있는 규칙은 단순하다. 스칼라 서브 쿼리는 최종 SELECT 목록 순서로 표시되며, 중첩 스칼라 서브 쿼리 안쪽부터 표시된다. 규칙을 통해 대략의 위치를 파악하고, Predicate Information을 통해 확인하는 방식을 사용하면 스칼라 서브 쿼리를 쉽게 식별할 수 있다.

참고로 모든 스칼라 서브 쿼리가 실행 계획 상단에 별도로 표시되는 것을 아니다. 서브 쿼리 팩토링이 MATERIALIZE된 경우에는 서브 쿼리에 포함된 스칼라 서브 쿼리가 LOAD AS SELECT 오퍼레이션 하단에 표시된다.

-- 4
WITH w1 AS (
SELECT /*+ MATERIALIZE */
       a.*
     , (SELECT x.c2
          FROM t2 x
         WHERE x.c1 = a.c1) AS t2
  FROM t1 a)
SELECT a.*
     , (SELECT y.c2
          FROM t3 y
         WHERE y.c1 = a.c1) AS t3
  FROM w1 a;

--------------------------------------------------------------------
| Id  | Operation                     | Name                       |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |
|   1 |  TABLE ACCESS BY INDEX ROWID  | T3                         |
|*  2 |   INDEX RANGE SCAN            | T3_X1                      |
|   3 |  TEMP TABLE TRANSFORMATION    |                            |
|   4 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6A84_ADF921B |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2                         |
|*  6 |     INDEX RANGE SCAN          | T2_X1                      |
|   7 |    TABLE ACCESS FULL          | T1                         |
|   8 |   VIEW                        |                            |
|   9 |    TABLE ACCESS FULL          | SYS_TEMP_0FD9D6A84_ADF921B |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("Y"."C1"=:B1)
   6 - access("X"."C1"=:B1)