성능고도화 4-2 서브쿼리 Unnesting

서브쿼리의 분류 

 - 오라클메뉴얼은 3가지로 서브쿼리를 분류한다. 

    ● 인라인 뷰 : from 절에 나타나는 서브쿼리를 말한다. 

    ● 중첩된 서브쿼리 : 결과집합을 한정하기 위해 where절에 사용된 서브쿼리를 말한다. 

                               서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를

                               '상관관계 있는 서브쿼리'라고 부른다. 

    ● 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 컬럼 값만을 리터하는 것이 특징이다. 

                               주로 select-list에서 사용되지만 몇 가지 예외사항을 뺀다면 

                               컬럼이 올 수 있는 대부분 위치에서 사용 가능하다. 

 - 서브쿼리를 참조하는 메인 쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 블록 단위로 

   최적화를 수행한다. 즉, 쿼리 블록 단위로 최적의 엑세스 경로와 조인 순서, 조인 방식을 

   선택하는 것을 목표로 한다. 

서브쿼리 Unnesting의 의미 

 - 서브쿼리를 처리하는데 있어 옵티마이저가 선택하는 방식 2가지 

      ● 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. 이를 일컬어 

          '서브쿼리 Unnesting'이라고 한다.

         ( '서브쿼리 Flattening'이라고도 부른다. 이렇게 쿼리 변환이 이루어지고 나면 

           일반 조인문처럼 다양한 최적화 기법을 사요할 수 있게 된다. )  

      ● 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다.

          메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 

          이때 서브쿼리에 필터 오퍼레이션이 나타난다. 

서브쿼리 Unnesting의 이점 

 - 서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 엑세스 경로와 조인 메소드를 

   평가할 수 있다. 특히 옵티마이저는 많은 조인테크닉을 가지기 때문에 조인 형태로 변환했을때 

   더 나은 실행계획을 찾을 가능성이 높아진다. 

 - 10g부터는 서브쿼리 Unnesting이 비용기반 쿼리 변환 방식으로 전환되었다. 따라서 변한된 

   쿼리의 예상 비용이 더 낮을 때만 Unnesting된 버전을 사용하고, 그렇지 않을 때는 원본 

   쿼리 그대로 필터 방식으로 최적화 한다. 

 - 서브쿼리 Unnesting 과 관련한 힌트 

     ● unnest : 서브쿼리를 Unnesting 함을써 조인방식으로 최적화하도록 유도한다. 

     ● no_unnesting : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화 하도록 유도한다. 

서브쿼리 Unnesting 기본 예시 

 - 서브쿼리 Unnesting은 중첩된 서브쿼리 (where절에 사용된) 에서 발생(?) 한다. 

 - Unnesting 하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 

   넘기면서 서브쿼리를 반복 수행한다. 

 - 변환된 쿼리는 아래와 같은 조인문 형태가 된다. 

   ( 그런 경우 view merging으로 이어지는 경우가 많다. ) 

Unnesting된 쿼리의 조인 순서 조정 

 - Unnesting에 의해 일반 조인문으로 변환된 후에는 emp, dept 어느쪽이든 드라이빙 집합으로 

   선택될 수 있다는 사실이다. 

 - 서브쿼리에서 메인 쿼리에 있는 테이블을 참조할 수는 있지만 메인 쿼리에서 서브쿼리 쪽 

   테이블을 참조하지는 못하므로 leading 힌트를 통해 서브쿼리 테이블을 먼저 읽도록 할 수는 

   없다. 

 - 대신 ordered 힌트를 사용하면 서브쿼리 쪽 테이블을 직접 참조하지 않아도 되므로 

   원하는 대로 조인 순서를 유도할 수 있다. 이것을 통해, Unnesting 된 서브쿼리가 from 절에서 

   앞쪽에 위치함을 알 수 있다.

 - 10g부터는 쿼리 블록마다 이름을 지정할 수 있는 qb_name 힌트가 제공되어 쉽고 정확하게 

   제어할 수 있다. 

    예시 ) select /*+ leading(dept@qb1) */ * from emp 

             where deptno in (select /*+ unnest qb_name(qb1) */ deptno from dept )

서브쿼리가 M쪽 집합이거나 Nonunique 인덱스 일 때 

 - M쪽 집합이 메인 쿼리 이고 1쪽 집합이 서브 쿼리 일 때는 순서가 보장이 되지만 

   이것이 바뀌었을 때는 잘못된 결과가 나온다. 

 - 만약 두 테이블 사이에 PK/Unique 제약 또는 Unique 인덱스가 없다면 옵티마이저는 두 가지 

   방식 중 하나를 선택하는데, Unnesting 후 어느 쪽 집합이 먼저 드라이빙 되느냐에 따라 

   달라진다. 

     ● 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 

         먼저 sort unique 오퍼레이션을 수행하므로써 1쪽 집합으로 만든 다음에 조인한다. 

     ● 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인 방식으로 조인한다. 이것이 세미조인이 

         탄생하게 된 배경이다. 

Sort Unique 오퍼레이션 수행 

 - 여기서는 서브쿼리에서 unique 인덱스나 pk 가 없을 시 sort Unique 가 발생한다고 한다. 

   그러나 sort unique가 발생하지 않는다면 unnest가 되면서 서브 쿼리가 먼저 실행 되도록

   하려고 한다. 

세미 조인 방식으로 수행 

 - NL 세미 조인으로 수행할 때는 sort unique 오퍼레이션을 수행하지 않고도 결과집합이 M쪽 

   집합으로 확장되는 것을 방지하고 알고리즘을 사용한다. 

 - Outer 테이블의 한 로우가 Inner 테이블의 한 로우와 조인에 성공하는 순간 진행을 멈추고 

   Outer 테이블의 다음 로우를 계속 처리하는 방식이다. 

 - 세미조인 방식으로 변환할 때의 장점은, NL 세미 조인뿐만 아니라 해시 세미 조인

    , 소트머지 세미 조인도 가능하다는 데에 있다. 사용자가 직접 유도할 때는 unnest 힌트와 

    함께 각각 hash_sj, merge_sj 힌트를 사용하면 된다. 

   * 여기서 중요한 것 2가지 

      ● 세미 조인은 unnesting 후에 사용한다. 

      ● 또, 세미 조인은 outer (즉 메인 테이블) 건수 만큼 random access가 발생할 것이다. 

          그런데 이 부분이 생각할 부분이 있다. 세미 조인은 일반적인 조인 보다는 더 빠르게 실행

          된다. 그러나 서브쿼리는 제대로 된 쿼리라면 1쪽 집합일 경우가 많을 것이다. 

          그렇다면 M쪽 집합에서 조인을 시도하는 것보다 random access 되는 수가 적을 텐대 

          unnest 세미 조인이 이 경우 성능이 더 좋을 지는 확신 할 수 없다. 

          대략 결과 건수가 1:1 정도가 된다면 이 방법이 더 빨라 질 수 있을 것 같다.

     ● 여기서는 좀 잘 못 생각한 부분이 있다.  
         이 부분으로 인하여 23회 SQLP 실기 문제에서도 좀 헤맸었는데 

         기본적으로 M(메인) : 1(서브) 가 옳은 쿼리라고 가정하고 있다. 
         즉 1(메인) : M(서브) 가 되면 잘 못된 값을 도출 시킬 수도 있는 것이다.

         그런데 만약 1(메인) : M(서브) 이 과정에서도 세미 조인을 실시 할 수 있다면 
         이야기는 좀 달라질 수 있다. 
         1(메인) : M(서브)  에서도 세미 조인이 가능하면 M(메인) : 1(서브) 보다 
         random 엑세스 부하를 줄 일 수 있기 때문이고
         세미조인인 이상 실제 조인이 일어나지 않기 때문에 잘못된 값을 출력할 
         가능성도 없어진다. ( 이는 물론 실제 조인이 일어나지 않아도 된다는 가정하) 
         이 부분에서 1:M 의 관계에서 세미 조인이 실시 되는지 확인해보지 못했다 

필터 오퍼레이션과 세미조인의 캐싱 효과 

 -  서브쿼리 unnesting 하지 않으면 쿼리를 최적화하는 데 있어 선택의 폭이 넓지 않아 불리 

    메인 쿼리를 수행하면서 건건이 서브쿼리를 반복 수행하는 단순한 필터 오퍼레이션을 사용할 

    수 없기 때문이이다. 

    ( 대량의 집합을 기준으로 이처럼 Random 엑세스 방식으로 서브쿼리 집합을 필터링한다면 

      결코 빠른 수행 속도를 얻을 수 없다. ) 

 - 오라클은 서브쿼리 수행 결과를 버리지 않고 내부 캐시에 저장하고 있다가 같은 값이 출력되면 

   저장된 값을 출력한다. 

    ( 스칼라 서브퀄의 캐싱효과와 같다. ) 

 - 조나단 루이스 설명에 의하며, 오라클은 8i와 9i에서 256개, 10g에서, 1024개 해시 엔트리를 

   캐싱한다고 한다. 실제 캐싱할 수 있는 엔트리 개수가 몇 개이건 간에 서브쿼리와 조인되는 

   컬럼의 Distinct Value 개수가 캐시 상한선을 초과하지 않는다면 필터 오퍼레이션은 

   매우 효과적인 수행방식일 것이다. 

 - 10g부터는 NL 세미 조인도 캐싱 효과를 갖는다. 그동안 캐싱 효과를 앞세워 명맥을 유지하던 필터 오퍼레이션이 설 자리를 잃게 되었다. 

    * 여기서 궁금한 것은 세미 조인은 outer (즉 메인 테이블)을 먼저 읽을 때 조인 하지 않고 

      빠르게 필터링 할 수 있게 해준다. 그런데 이것을 inner 테이블을 사용하면서도 semi 조인을 

      발생 시킨다면 성능적으로 더 좋지 않을까 생각한다. 단, 여기서는 결과가 메인 테이블에 있는 

      컬럼들만 출력해야 하며, 이럴경우 쿼리 자체를 조인으로 하여 세미 조인을 사용하는 것이 

      성능 향상에 도움이 될 것으로 생각된다. 

      (바로 위에서 했던 이야기와 동일하네 .. ^^;) 

Anti조인 

 - not exists, not in 서브쿼리도 Unnesting 하지 않으면 아래와 같이 필터 방식으로 처리된다. 

 - not exists 필터 : 조인에 성공하는 (서브) 레코드가 하나도 없을 때만 결과지합에 담는다. 

                          ( unnesting 하면 아래와 같이 Anti 조인 방식으로 처리된다. ) 

 - 해시 Anti 조인은 조금 다르다. 해시 Anti 조인으로 수행할 때는, 먼저 메인테이블을 

   해시 테이블로 빌드한다. 서브쿼리를 스캔하면서 해시 테이블을 탐색하고, 

   조인에 성공한 엔트리에만 표시를 한다. 마지막으로, 해시 테이블을 스캔하면서 표시가 없는 

   엔트리만 결과집합에 담는 방식이다. 

집계 서브쿼리 제거 

 - 집계 함수를 포함하는 서브쿼리를 Unnesting 하고, 이를 다시 분석 함수로 대체하는 

   쿼리 변환이 10g에서 도입되었다. 

 - 10g부터 옵티마이저가 선택할 수 있는 옵션이 한 가지 더 추가되었는데, 서브쿼리로부터 

   전환된 인라인 뷰를 제거하고 아래와 같이 메인 쿼리에 분석 함수를 사용하는 형태로 

   변환하는 것이다. 

   * 이 부분은 절차에 관한 부분은 이야기는 잘 나와있지만 실제적으로 차이

     즉, 블록의 수와 같은 부부은 나와 있지 않다 다만 테이블을 실제로 한 번 읽고 

     window buffer 가 발생한다는 것이며, 이는 자세히 보면 조인 조건을 타고 검색 조건이 

     타고 들어가서 분석 함수로 대체 되고 그로 인하여 테이블 엑세스를 줄이게 되어 있다.  

     이 부분에 대해서 이해가 위해서는 조인 조건을 타고 들어가는 검색 조건의 효과를 

     확인 후 성능의 차이를 이해하는 것이 좋을 듯 싶다. 

Pushing 서브쿼리 

 - Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리되며, 대게 실행계획 상에서 

   맨 마지막 단계에 처리된다. 만약 서브쿼리 필터링을 먼저 처리했을 때 

   다음 수행 단계로 넘어가는 로우 수를 크게 줄일 수 있다면 성능은 그만큼 향상된다. 

   Pushing 서브쿼리는 이처럼 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 

   강제하는 것을 말하며, 이를 제어하기 위해 사용하는 옵티마이저 힌트가 push_subq이다. 

 - pushing 서브쿼리는 unnesting  되지 않는 서브쿼리에만 작동한다는 사실을 기억할 필요가 

   있다. 따라서 push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 

   사용방법이다. 

 - 9i 와 10g 사이에 push_subq 힌트를 기술하는 위치가 바뀌었다. 

    ● 9i 는 메인 쿼리에서 힌트 

    ● 10g부터 서브 쿼리에 힌트 작성

 - 서브쿼리가 조인으로 풀릴 때 서브쿼리에서 참조하는 테이블이 먼저 드라이빙되도록 

   제어할 목적으로 push_subq 힌트를 사용한다고 잘못 알고 있는 사람들이 있는데, 

   서브쿼리가 조인으로 풀린다는 것은 Unnesting 되었다는 뜻인데, Pushing 서브쿼리는 

   Unnesitng 되지 않은 서브쿼리의 처리 순서를 제어하는 기능이다. 

 * 여기서 부터 골치(?)가 아파진다. 

   - 서브쿼리 unnesting 은 비용기반 쿼리 변환이다 즉 unnesitng이 유리 하지 않는 경우도 

     발생할 수 있다는 이야기 이다. 그럼 언제 unnesting이 유리하지 않을까 ??

     여기서 메인 테이블에 from 절이 2개가 있고 두개의 테이블을 조인하고 서브쿼리 필터링 

     할 때와 서브쿼리 필터링을 하고 두 테이블이 조인하도록 하면서 조인량을 줄였다. 

     그런데 이 부분도 어찌 되었든 메인 테이블 중 1개와는 filter 작업이 발생할 것이다. 

     그렇다면 이 부분도 조인이 된다면 성능은 더 나아 질 수 있다. 

     이런 부분을 잘 생각해야 한다.