Science of Database

개발자의 질문
개발자 한명이 DBA 에게 질문을 던졌다. ‘스칼라 서브쿼리를 사용한 SQL 을 인라인뷰로 싸고 인라인뷰 외부에서 스칼라 서브쿼리를 Filter 조건으로 사용하면 스칼라 서브쿼리가 없어지는 현상이 발생합니다. 이 현상이 정상인가요?’  그런데 DBA 는 그런일은 발생할 수가 없다고 하였다. 과연 사실일까? 정답은 스칼라 서브쿼리가 없어진다는 것이다. 예리한 눈을 가진 개발자 임에 틀림없다.

백견이 불여일행
SSTS( Scalar Subquery To Subquery ) 는 스칼라 서브쿼리를 서브쿼리로 변경시키는 Transformation 과정이다. 하지만 항상 변환되지 않는다. 스칼라 서브쿼리를 인라인뷰 외부에서 Filter 조건으로 사용할때만 가능하다. 아래의 SQL 을 보자

인덱스 상황 :

EMP_JOB_IX : employee (job_id)

DEPT_ID_PK1 : department(department_id)

위의 SQL 은 Location 을 스칼라 서브쿼리로 구현하였다. 하지만 스칼라 서브쿼리를 Select 하는데는 사용하지 않고 Where 조건으로 사용하는 것을 주목하라.

----------------------------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

----------------------------------------------------------------------------------

|*  1 |  FILTER                      |             |        |       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |      5 |   195 |     2   (0)|

|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX  |      5 |       |     1   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(>0)

   3 - access("E"."JOB_ID"='IT_PROG')

   5 - access("D"."DEPARTMENT_ID"=:B1)

위의 Plan 을 보면 본능적으로 스칼라 서브쿼리가 서브쿼리로 바뀐 것을 알수 있어야 한다. 다시말하면 옵티마이져가 SQL 을 아래처럼 바꾼 것 이다.

----------------------------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

----------------------------------------------------------------------------------

|*  1 |  FILTER                      |             |        |       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |      5 |   195 |     2   (0)|

|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX  |      5 |       |     1   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(>0)

   3 - access("E"."JOB_ID"='IT_PROG')

   5 - access("D"."DEPARTMENT_ID"=:B1)

실행계획과 Predicate Information 이 완전히 같음을 알수 있다. 이제 10053 Trace 를 분석해보자.

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

Order-by elimination (OBYE)

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

…중간생략

CVM:   Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

Registered qb: SEL$F5BB74E1 0xc4643d0 (VIEW MERGE SEL$1; SEL$2)

---------------------

QUERY BLOCK SIGNATURE

---------------------

  signature (): qb_name=SEL$F5BB74E1 nbfros=1 flg=0

    fro(0): flg=0 objn=70296 hint_alias="E"@"SEL$2"

View Merging 이 발생하여 SEL$2(인라인뷰 a) 가 SEL$1(메인쿼리)에 통합 되어 버렸다. View Merging 이 발생하여 새로운 쿼리블럭인 SEL$F5BB74E1 가 생성 되었다. 하지만 쿼리블럭 SEL$F5BB74E1 의 From 절을 보면 employee(Alias 로는 E) 만 존재하고 department(D)는 존재 하지 않는다. 그렇다면 스칼라 서브쿼리는 어디로 갔을까? 해답은 FPD(Filter Push Down) 기능에 있다.

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

Predicate Move-Around (PM)

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

…중간생략

query block SEL$F5BB74E1 (#0) unchanged

FPD: Considering simple filter push in query block SEL$F5BB74E1 (#0)

 (SELECT "D"."LOCATION_ID" FROM "DEPARTMENT" "D")>0 AND "SYS_ALIAS_1"."JOB_ID"='IT_PROG'

FPD: Considering simple filter push in query block SEL$3 (#0)

"D"."DEPARTMENT_ID"=:B1

try to generate transitive predicate from check constraints for query block SEL$3 (#0)

finally: "D"."DEPARTMENT_ID"=:B1

FPD 기능에 의해서 위에서 새로 생성된 쿼리블럭 SEL$F5BB74E1 의 조건절에 서브쿼리를 생성하고 있다. 또한 새로 생성된 서브쿼리에 "D"."DEPARTMENT_ID"=:B1 조건을 밀어넣고 있다.

검증

스칼라  서브쿼리가 서브쿼리로 바뀌었으므로 서브쿼리에 사용할수 있는 힌트 Push_subq 를 사용해보자. 이것이 가능해야지만 진정한 서브쿼리이다.

----------------------------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

----------------------------------------------------------------------------------

|*  1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEE    |      1 |    39 |     2   (0)|

|*  2 |   INDEX RANGE SCAN           | EMP_JOB_IX  |      5 |       |     1   (0)|

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(>0)

   2 - access("E"."JOB_ID"='IT_PROG')

   4 - access("D"."DEPARTMENT_ID"=:B1)

실행계획을 보면 알겠지만 Subquery Pushing 이 발생하여 Id 기준으로 2번과 3번이 동일 Level 상에 존재한다. Subquery Pushing 이 성공적으로 수행된것을 알수 있다.

한단계 더 나아가 보자

EMPLOYEE 테이블에 JOB_ID, DEPARTMENT_ID 를 생성 하였다. 바로 위에서 실행한 SQL 을 다시 실행 해보자.

--> 여기서 PUSH_SUBQ 를 적용한 SQL 실행한다.

---------------------------------------------------------------------------------------

| Id  | Operation                     | Name            | E-Rows |E-Bytes| Cost (%CPU)|

---------------------------------------------------------------------------------------

|   1 |  TABLE ACCESS BY INDEX ROWID  | EMPLOYEE        |      1 |    39 |     2   (0)|

|*  2 |   INDEX RANGE SCAN            | EMP_JOB_DEPT_IX |      1 |       |     1   (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT      |      1 |     7 |     1   (0)|

|*  4 |     INDEX UNIQUE SCAN         | DEPT_ID_PK1     |      1 |       |     0   (0)|

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("E"."JOB_ID"='IT_PROG')

       filter(>0)

   4 - access("D"."DEPARTMENT_ID"=:B1)

이것은 제공자 서브쿼리가 아니다

실행계획은 마치 제공자 서브쿼리처럼 바뀌었지만 이것은 제공자 서브쿼리가 아니고 Subquery Pushing 에 의한 효과이다. Predicate Information 을 주목하라. ID 기준으로 2번에서 Filter 가 수행된다. . (JOB_ID, DEPARTMENT_ID) 인덱스를 사용하지 않은 SQL의 Predicate Information을 보면 EMPLOYEE 테이블 엑세스 시에 filter 가 발생함을 알수 있다. 이것이 바로 Subquery Pushing 과 인덱스에 의한 Early Filter 의 효과이다. 물론 Early Filter 의 개념은 조인순서상에 서브쿼리를 최대한 먼저 조인 하는것으로 바꾸는 것이다. 하지만 위와 같은 Early Filter의 부가적인 기능도 있음을 알아야 한다.

 Subquery Pushing 에 의한 Early Filter 기능은 Using Sub query Method( Filter / Access sub Query ) 글을 참조하라

결론:
이처럼 Oracle Query Transformer 는 스칼라 서브쿼리를 Filter 조건으로 사용할 때 SSTS를 발생시킨다.. 여러분이 Query Transformation 을 의도 하던 의도하지 않던 말이다.

PS :
제목이 이상하다는 의견이 있다. 제목은 옵티마이져가  변환을 수행한다는 의미이다. 수동으로 스칼라 서브쿼리를 서브쿼리로 고치라는 의미가 아니므로 착오가 없길 바란다.

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 을 보여준다.

         
--------------------------------------------------------------------------
| 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 *
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 이다.

---------------------------------------------------------------------------------
| 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 함수 사용하기

---------------------------------------------------------------------------------------------
| 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 서브쿼리를 선택하라는 것이다.

---------------------------------------------------------------------------------------------
| 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 ) 글을 참고하기 바란다.

오늘은 지난시간에 논의 했던 SubQuery Flattening 에 이어서 쿼리변형이 발생하지 않는 Access 서브쿼리 와 Filter 서브쿼리, Early Filter 서브쿼리 에 대해서 이야기 할것이다.

튜닝관점의 서브쿼리의 분류

1.Unnesting 서브쿼리 : 참조
                 
2.Semi Join/Anti Join : 참조

3.Access 서브쿼리 : 쿼리변형이 없음
    1)흔히말하는 제공자 서브쿼리임.(서브쿼리부터 풀려서 메인쿼리에 값이 제공된다.)
    2)위의 1번 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없다.
    3)힌트: 특별한 힌트없음.
               다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
               서브쿼리로부터 제공되는 메인쿼리의 컬럼에 인덱스가 생성되어 있으면됨.
    4) 주의사항: corelate 서브쿼리는 제공자 서브쿼리가 될수 없음.

4.Filter 서브쿼리 : 쿼리변형이 없음
    1)흔히 말하는 확인자 서브쿼리임.(메인쿼리의 값을 제공받아 서브쿼리에서 체크하는 방식임)
    2)위의 1번 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없고 Filter 로 나온다.
    3)Filter SubQuery 의 특징은 메인쿼리의 From 절에 있는 모든 테이블을 엑세스후에 가장마지막에
      서브쿼리가 실행된다는 것이다.
    4) 힌트: 특별한 힌트없음.
                다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
                메인쿼리로부터 제공되는 서브쿼리의 조인컬럼에 인덱스가 생성되어 있으면됨.

5.Early Filter 서브쿼리 : 쿼리변형이 없음
    1)Filter SubQuery 와 같은 방식이지만 서브쿼리를 최대한 먼저 실행하여 데이터를 걸러낸다.
    2)힌트 : 메인쿼리에 push_subq 힌트사용 (10g 이후부터는 서브쿼리에 힌트사용해야함)
    3)주의사항: 많은 튜닝책에서 "Push_subq 힌트를 사용하면 제공자 서브쿼리를 유도한다" 라고
                     되어 있으나 이는 잘못된 것이다.
                     push_subq 힌트를 사용하면 확인자 서브쿼리(Filter 서브쿼리)를 유도하지만 최대한
                     먼저 수행된다.

아래의 스크립트를 보자

--------------------------------------------------------------------------

| 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 |

--------------------------------------------------------------------------

위의 plan 을 보면 실행순서가 헷갈릴수 있다.
결론을 이야기 하자면 id 기준으로 실행 순서는 5 -> 4 -> 3 -> 2 -> 1 이다.

즉 맨밑에서부터 위로 실행된다. (서브쿼리부터 실행해서 메인쿼리에 1건을 제공하였다)
특정일자에 max 일련번호를 찾아서 처리해야할때 많이 사용하는 SQL 패턴이다.
corelate 서브쿼리가 아니고 서브쿼리로부터 제공되는 메인쿼리의 컬럼에 인덱스가 생성되어 있는 경우만이
Access 서브쿼리로 풀린다.(mm1.id_child 컬럼에 인덱스가 있어야 한다)

다음의 두가지 경우에서만 Access 서브쿼리를 사용해야한다.
1) 서브쿼리의 엑세스건수가 적고 서브쿼리의 결과를 제공받은 메인쿼리도 엑세스 건수가 적어야 한다.

2) 비록 서브쿼리의 엑세스 건수가 많지만 그결과를 제공받은 메인쿼리의 엑세스 건수가 적다면 사용할수 있다.
    왜냐하면 Access 서브쿼리는 단한번만 수행되기 때문이다.   
    이경우 메인쿼리의 테이블이 mm1.id_child 컬럼기준으로 클러스트링 팩터가 좋다면 서브쿼리가 힘을 얻게 된다.
    하지만 이경우는 반드시 Semi Join 이나 Unnesting 서브쿼리, Filter 서브쿼리등과 성능을 비교해 보아야 한다.

------------------------------------------------------------------------

| Id  | Operation                      | Name  | Rows  | Bytes | Cost  |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |       |   597 | 11343 |    28 |

|*  1 |  FILTER                        |       |       |       |       |

|   2 |   TABLE ACCESS FULL            | T1    | 10000 |   185K|    28 |

|*  3 |   TABLE ACCESS BY INDEX ROWID  | T2    |     1 |     7 |     2 |

|*  4 |    INDEX RANGE SCAN            | T2_PK |     1 |       |     1 |

------------------------------------------------------------------------

메인쿼리의 WHERE 절에 조건이 있고 OR 로 EXISTS 를 사용하게 되면 CBQT(Cost Based Query Transformation) 가 작동을 하지않는다.
따라서 위의 경우처럼 OR 가 있는 서브쿼리는 SubQuery Flattening 이 발생하지 않고 확인자 서브쿼리로 풀리게 된다.
위의 경우와는 반대로 10g 에 와서는 옵티마이져가 왠만하면  Semi Join 이나 서브쿼리 Unnesing등의 쿼리변형을 하게되므로 
대부분의 경우 강제로 NO_UNNEST 힌트를 사용해야지만  Filter 서브쿼리로 풀리게 된다.
주의할점은 10g 에서 서브쿼리가 filter 로 풀릴경우 Plan 에서는 Filter Operation 이 사라지는 경우가 많이 있다.
Plan 이 잘못된것이 아니니 참고하기 바란다.
상식이지만 노파심에서 다시한번 이야기 하지만 Filter 서브쿼리는 메인쿼리로부터 조인되는 컬럼(t2.n1)에 반드시 인덱스가
만들어져 있어야 한다.
그렇지 않으면 성능은 기대할수 없다.

Filter 서브쿼리는 다음의 두가지 경우에 사용하여야 한다.
1) 메인쿼리의 where 절에 똑똑한 조건들이 많아서 엑세스 건수가 적을때
    이경우는 filter Operation 이 몇번 발생하지 않게 되므로 당연히 유리하다.

2) 메인쿼리는 비록 엑세스건수가 많지만 서브쿼리의 체크조건이 True 인경우가 많은경우
   이경우는 특히 부분범위처리시 유리하다.
   왜냐하면  비록 건수가 많지만 서브쿼리의 체크조건이 True 인경우가 많으므로 화면에 바로바로 나오게 된다.
   하지만 배치 프로그램처럼 전체범위를 목적으로 하는경우는 성능이 저하되므로 주의하여야 한다.
   이때도 서브쿼리의 t2.n1 컬럼기준으로 서브쿼리 테이블의 클러스트링 팩터가 좋다면 성능이 향상되는데 물론 메인쿼리가
   sort 되는경우 이거나 인덱스의 사용등으로 자동 sort 가 되어 서브쿼리에 데이터가 공급되는 경우에 한해서다.

튜닝 컨설팅을 하다보면 개발자들이 서브쿼리에 대하여 많은 관심을 보이며 또한 자주 사용하곤 한다.
하지만 정작 튜닝관점및  외형적인 관점에서 서브쿼리에 대하여 정의를 내릴수 있는 사람은 많지않다.  
오늘은 서브쿼리에 대하여 정의를 내려보자.

외형적관점에서 서브쿼리의 종류는 2가지 밖에 없다.
1.Non Corelate 서브쿼리  : (서브쿼리 내에 서브쿼리와 메인쿼리의 조인절이 없음)
2.Corelate 서브쿼리: (서브쿼리 내에 서브쿼리와 메인쿼리의 조인절이 있음)
그렇다면 튜닝관점에서 서브쿼리를 어떻게 분류해야 할까?
튜닝관점의 서브쿼리는 아래처럼 5가지로 분류할수 있다.