Access Sub Query 의 함정

9월 달에 Using Sub query Method( Filter / Access sub Query ) 이란 글을 통하여 Access 서브쿼리를
설명한적이 있다.
오늘은 서브쿼리 사용시 함정이 몇가지 있지만 그중에서 가장 자주발생하는 Access 서브쿼리의 경우를 소개한다.

서브쿼리를 사용할때 Access sub Query plan (제공자 서브쿼리 plan)이 나오더라도 방심하면 안된다.
Plan 의 모습만 Access sub Query 일뿐 사실은 Filter sub Query(확인자 서브쿼리) 로 풀리는 경우가 많이 있기 때문이다.
 
실행환경 : Oracle 10.2.0.4
아래는 전형적인  Access sub Query plan 을 보여준다.

select small_vc
  from min_max mm1
 where mm1.id_parent = 100
   and mm1.id_child = ( select max(mm2.id_child)
                                    from min_max mm2
                                 where mm2.id_parent = 100 )  ;

         
--------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |   108 |     4 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MIN_MAX |     1 |   108 |     2 |
|*  2 |   INDEX UNIQUE SCAN            | MM_PK   |     1 |       |     1 |
|   3 |    SORT AGGREGATE              |         |     1 |     8 |       |
|   4 |     FIRST ROW                  |         |    10 |    80 |     2 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| MM_PK   |    10 |    80 |     2 |
--------------------------------------------------------------------------

위 쿼리의 실행순서는 id 기준으로 5 -> 4 -> 3 -> 2 -> 1 이라고 설명 하였다.
하지만 항상 그렇게 되는 것은 아니다.
아래 스크립트를 보자  

인덱스 현황 :
1)EMP 테이블  : EMP_IX04  (JOB, DEPTNO, HIREDATE)
2)DEPT 테이블 : DEPT_IDX1 (LOC)

SELECT /*+ GATHER_PLAN_STATISTICS INDEX_RS(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO IN (SELECT /*+ NO_UNNEST INDEX(B DEPT_IDX1) */  B.DEPTNO
                                  FROM DEPT B
                                WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101';

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      1 |00:00:00.01 |       7 |
|*  3 |    INDEX RANGE SCAN            | EMP_IX04  |      1 |      1 |00:00:00.01 |       6 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      2 |      1 |00:00:00.01 |       4 |
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |      2 |      2 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------

위 SQL 의 의도는 부서 테이블에서 위치가 CHICAGO 인 부서를 찾아서 EMP 테이블에 제공 하는것이다.
따라서 의도적으로 서브쿼리에 NO_UNNEST 힌트를 사용하였다.
PLAN 을 보면 정상적 이라고 생각할 수 있다.
그런데 Starts 컬럼(빨강색)을 보면 서브쿼리가 2번 실행된걸 알수 있다.
뭔가 이상하다.
제공자 서브쿼리는 단 한번만 실행 되어야 하는데...
아래의 Predicate Information 을 보면 더욱 이상한것을 발견할수 있다.

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   3 - access("A"."JOB"='CLERK' AND "A"."HIREDATE">='19801010' AND "A"."HIREDATE"<='19820101')
       filter(("A"."HIREDATE"<='19820101' AND "A"."HIREDATE">='19801010' AND  IS NOT NULL))
   4 - filter("B"."DEPTNO"=:B1)
   5 - access("B"."LOC"='CHICAGO')
 
id 3에서  access 정보를 보면 DEPTNO 가 빠져있다.
인덱스가 (JOB, DEPTNO, HIREDATE) 인 상황에서 JOB, HIREDATE 만 사용하였다.
또한 id 4에서 filter("B"."DEPTNO"=:B1) 이 발생하였다.
이것은 확인자 서브쿼리에서만 나올수 있는 정보이다.
뭔가 이상하지 않은가?

이것을 결정적으로 확인할수 있는것은 DBMS_XPLAN.DISPLAY_CURSOR 가 아니라
DBMS_XPLAN.DISPLAY 이다.

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |     1 |    32 |     3   (0)|
|*  1 |  FILTER                        |           |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |     1 |    32 |     1   (0)|
|*  3 |    INDEX RANGE SCAN            | EMP_IX04  |     1 |       |     1   (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |     9 |     1   (0)|
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |     1 |       |     1   (0)|
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   3 - access("A"."JOB"='CLERK' AND "A"."HIREDATE">='19801010' AND
              "A"."HIREDATE"<='19820101')
       filter("A"."HIREDATE"<='19820101' AND "A"."HIREDATE">='19801010'
              AND  EXISTS (SELECT /*+ NO_UNNEST INDEX ("B" "DEPT_IDX1") */ 0 FROM  --> EXISTS 발생
              "DEPT" "B" WHERE "B"."LOC"='CHICAGO' AND "B"."DEPTNO"=:B1))
   4 - filter("B"."DEPTNO"=:B1)
   5 - access("B"."LOC"='CHICAGO')

Predicate Information 을 보면 분명히 Filter sub Query(확인자 서브쿼리) 로 풀리는걸 확인할수 있다.
많은 튜닝책에서 이런식으로 결합인덱스를 만들면 제공자 서브쿼리로 풀린다고 가이드 하고 있지만
불행히도 버젼 9i 이후부터는 그렇지못하다.
Access sub Query 으로 예상 했지만  Filter sub Query 로 풀리면 심각한 성능저하 현상이 발생할수 있다.
그렇다면 어떻게  Filter sub Query 를 Access sub Query(제공자 서브쿼리) 로 만들것인가?
오라클은 옵티마이져는 이러한 경우에 왠만 해서는 Access sub Query 를 선택하지 않는다.
이때 간단한 트릭을 생각할수 있다.
몇가지 방법이 있지만 가장 간단한 두가지의 방법을 소개한다.
서브쿼리가 항상 1건만 Return 한다는 정보를 옵티마이져에게 주면된다.

1.min/max 함수 사용하기

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO IN (SELECT /*+ NO_UNNEST INDEX(B DEPT_IDX1) */  MAX(B.DEPTNO)
                                  FROM DEPT B
                                 WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101' ;

----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|*  1 |  FILTER                         |           |      1 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | EMP       |      1 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN             | EMP_IX04  |      1 |      1 |00:00:00.01 |       4 |
|   4 |     SORT AGGREGATE              |           |      1 |      1 |00:00:00.01 |       2 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       2 |
|*  6 |       INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   3 - access("A"."JOB"='CLERK' AND "A"."DEPTNO"= AND "A"."HIREDATE">='19801010' AND
              "A"."HIREDATE"<='19820101')
   6 - access("B"."LOC"='CHICAGO')

정상적으로 Access sub Query plan 이 나온것을 확인할수 있다.

2. IN 서브쿼리 대신에 = 서브쿼리로 바꾸기

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO = (SELECT /*+ NO_UNNEST INDEX(B DEPT_IDX1) */  B.DEPTNO
                                 FROM DEPT B
                               WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101';

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      1 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN            | EMP_IX04  |      1 |      1 |00:00:00.01 |       4 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       2 |
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
  3 - access("A"."JOB"='CLERK' AND "A"."DEPTNO"= AND "A"."HIREDATE">='19801010' AND
              "A"."HIREDATE"<='19820101')
   5 - access("B"."LOC"='CHICAGO')

두방법 모두 성공적으로 Access sub Query plan 으로 풀린다.
Buffers 도 7 에서 5 로 줄어들었다.
하지만 서브쿼리에서 2건 이상 RETURN 되는 경우는 어떻게 할것인가?
필자의 생각은 Access sub Query plan 을 포기하고 UNNEST 서브쿼리를 선택하라는 것이다.

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO IN (SELECT /*+ UNNEST INDEX(B DEPT_IDX1) */  B.DEPTNO
                                  FROM DEPT B
                                WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101';

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      1 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      1 |00:00:00.01 |       6 |
|   3 |    NESTED LOOPS                |           |      1 |      3 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       2 |
|*  6 |     INDEX RANGE SCAN           | EMP_IX04  |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   5 - access("B"."LOC"='CHICAGO')
   6 - access("A"."JOB"='CLERK' AND "A"."DEPTNO"="B"."DEPTNO" AND "A"."HIREDATE">='19801010'
              AND "A"."HIREDATE"<='19820101')

서브쿼리가 조인으로 바뀌었고 정상적으로  Access sub Query plan 과 같이
(JOB, DEPTNO, HIREDATE) 인덱스를 사용하였다.
위와 같은 상황에서는 Unnesting 을 적극 추천한다.
혹자는 "_unnest_subquery 파라미터 등을 세션단위에서 수정하여 제공자 서브쿼리로 만들면 되지않냐?"
고 말한다.
맞는 말이지만 연구소에서나 사용하여야 한다.
Production 시스템에서는 위와같이 히든 파라미터를 수정하는 것은 아주 위험하다.
현재는 버젼이 10g 이지만 Oracle을 Upgrade를 할때는 아마도 Oracle 버젼 13 혹은 14버젼을
사용할것이다.
그때에도 위와 같은 히든 파라미터가 있다고 보장할수 없다.
Deprecate 된 힌트도 마찬가지 일것이다.(대부분 3~4 버젼이 지나면 힌트가 없어짐)
물론 미래(Oralce Upgrade 시)에 모든 AS-IS 의 소스코드를 모조리 체크해서 새로운 힌트및 파라미터로 바꿀 각오가 되어 있다면 가능하다.

결론 :
마지막 예제와 같이 서브쿼리의 집합이 PK 컬럼이나 Unique 인덱스의 컬럼을 모두 RETURN 하는 경우는
Unnesting 될때 UNQUE 가 보장되므로 별도의 SORT UNIQUE 작업이나 HASH UNIQUE 작업이 일어나지 않는다.
따라서 서브쿼리의 결과 건수가 작고 별도의 Unique 작업이 없을경우 거의 Access sub Query 와
같은 성능을 보장하므로 굳이 Access sub Query plan (제공자 plan) 을 고집할 필요가  없다는 점을
기억 해야 한다.
서브쿼리 Unnesting 은 Using Sub query Method (Sub query Flattening ) 글을 참고하기 바란다.