PRECOMPUTE_SUBQUERY 힌트

PRECOMPUTE_SUBQUERY 힌트를 살펴보자.^[각주:1]

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

-- 1
DROP TABLE dept_t PURGE;
CREATE GLOBAL TEMPORARY TABLE dept_t (deptno NUMBER (2));
CREATE INDEX emp_x1 ON emp (deptno);

아래 쿼리는 가변 동적 조건을 처리하기 위해 임시 테이블을 사용했다.

-- 2
INSERT INTO dept_t VALUES (10);
INSERT INTO dept_t VALUES (20);

SELECT a.*
  FROM emp a
 WHERE a.deptno IN (SELECT x.deptno FROM dept_t x);

-----------------------------------------------
| Id  | Operation                    | Name   |
-----------------------------------------------
|   0 | SELECT STATEMENT             |        |
|   1 |  NESTED LOOPS                |        |
|   2 |   NESTED LOOPS               |        |
|   3 |    SORT UNIQUE               |        |
|   4 |     TABLE ACCESS FULL        | DEPT_T |
|*  5 |    INDEX RANGE SCAN          | EMP_X1 |
|   6 |   TABLE ACCESS BY INDEX ROWID| EMP    |
-----------------------------------------------

PRECOMPUTE_SUBQUERY 힌트는 10.2.0.1 버전부터 사용할 수 있다.

-- 3
SELECT name, version FROM v$sql_hint WHERE name = 'PRECOMPUTE_SUBQUERY';

NAME                VERSION
------------------- --------
PRECOMPUTE_SUBQUERY 10.2.0.1

1개의 행이 선택되었습니다.

아래와 같이 힌트를 기술하면 실행 계획에서 dept_t 테이블에 대한 액세스가 사라지고, Predicate Information에 상수가 표시된다.

-- 4
SELECT /* PS_TEST */
       a.*
  FROM emp a
 WHERE a.deptno IN (SELECT /*+ PRECOMPUTE_SUBQUERY */
                           x.deptno
                      FROM dept_t x);

-------------------------------------------------------
| Id  | Operation                            | Name   |
-------------------------------------------------------
|   0 | SELECT STATEMENT                     |        |
|   1 |  INLIST ITERATOR                     |        |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |
|*  3 |    INDEX RANGE SCAN                  | EMP_X1 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."DEPTNO"=10 OR "A"."DEPTNO"=20)

임시 테이블에 30, 40 부서를 입력하고 쿼리를 다시 수행하면 Predicate Information이 변경된 것을 확인할 수 있다.

-- 5
COMMIT;

INSERT INTO dept_t VALUES (30);
INSERT INTO dept_t VALUES (40);

SELECT /* PS_TEST */
       a.*
  FROM emp a
 WHERE a.deptno IN (SELECT /*+ PRECOMPUTE_SUBQUERY */
                           x.deptno
                      FROM dept_t x);

-------------------------------------------------------
| Id  | Operation                            | Name   |
-------------------------------------------------------
|   0 | SELECT STATEMENT                     |        |
|   1 |  INLIST ITERATOR                     |        |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |
|*  3 |    INDEX RANGE SCAN                  | EMP_X1 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."DEPTNO"=30 OR "A"."DEPTNO"=40)

다행히 새로운 커서가 생성되지 않는다.

-- 6
SELECT sql_id, child_number
  FROM v$sql
 WHERE sql_text LIKE '%PS_TEST%'
   AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
284p70ccnzczp 0

1개의 행이 선택되었습니다.

10046 트레이스를 확인해보면 동작 원리를 이해할 수 있다. Parse 단계에서 dept_t 테이블을 조회하는 Recursive 쿼리가 수행되는 것을 확인할 수 있다.

-- 7
SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (SELECT /*+ PRECOMPUTE_SUBQUERY */
                           x.deptno
                      FROM dept_t x)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          3          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0          3          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 110     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         2          2          2  HASH UNIQUE (cr=3 pr=0 pw=0 time=163 us cost=3 size=13 card=1)
         2          2          2   TABLE ACCESS FULL DEPT_T (cr=3 pr=0 pw=0 time=36 us cost=2 size=13 card=1)

********************************************************************************

SELECT /* PS_TEST */
       a.*
  FROM emp a
 WHERE a.deptno IN (SELECT /*+ PRECOMPUTE_SUBQUERY */
                           x.deptno
                      FROM dept_t x)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          4          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          5          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          9          0           6

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         6          6          6  INLIST ITERATOR  (cr=5 pr=0 pw=0 time=24 us)
         6          6          6   TABLE ACCESS BY INDEX ROWID BATCHED EMP (cr=5 pr=0 pw=0 time=35 us cost=3 size=228 card=6)
         6          6          6    INDEX RANGE SCAN EMP_X1 (cr=3 pr=0 pw=0 time=100 us cost=1 size=0 card=6)(object id 108098)

10053 트레이스를 확인해보면 옵티마이저 단계에 Parse 단계에서 변환된 쿼리가 입력되는 것을 확인할 수 있다.

-- 8
=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT "A"."EMPNO" "EMPNO","A"."ENAME" "ENAME","A"."JOB" "JOB","A"."MGR" "MGR","A"."HIREDATE" "HIREDATE","A"."SAL" "SAL","A"."COMM" "COMM","A"."DEPTNO" "DEPTNO"
  FROM "SCOTT"."EMP" "A"
 WHERE "A"."DEPTNO"=40
    OR "A"."DEPTNO"=30

서브 쿼리에 바인드 변수를 사용하면 해당 힌트가 동작하지 않는다. 쿼리 변환은 Parse 단계에서 수행되는데, 바인딩은 Execute 단계에서 수행되기 때문이다.

-- 9
SELECT a.*
  FROM emp a
 WHERE a.deptno IN (SELECT /*+ PRECOMPUTE_SUBQUERY */
                           x.deptno
                      FROM dept x
                     WHERE x.loc = :v_loc);

-----------------------------------------------
| Id  | Operation                    | Name   |
-----------------------------------------------
|   0 | SELECT STATEMENT             |        |
|   1 |  NESTED LOOPS                |        |
|   2 |   NESTED LOOPS               |        |
|*  3 |    TABLE ACCESS FULL         | DEPT   |
|*  4 |    INDEX RANGE SCAN          | EMP_X1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |
-----------------------------------------------

마지막으로 테스트를 위해 생성한 emp_x1 인덱스를 삭제하자.

-- 10
DROP INDEX emp_x1;

1. 해당 힌트는 Undocumented Hint이므로 운영환경에서 사용하시면 안된다. 개인 스크립트에만 활용하도록 하자. [본문으로]