성능고도화 6-2. 파티션 Pruning

파티션 Pruning 이란 

 - 파티션 Pruning은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 

   파티션 세그먼트를 엑세스 대상에서 제외시키는 기능이다. 파티션 테이블에 대한 쿼리나 

   DML을 수행할 때 극적인 성능 개선을 가져다주는 핵심 원리가 파티션 Pruning에 있다

기본 파티션 Pruning 

 - 기본 파티션 Pruning 기법 

    ● 정적 파티션 Pruning : 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동하며, 

                                       엑세스할 파티션이 쿼리 최적화 시점에 미리 결정되는 것이 

                                       특징이다. 실행계획의 Pstart 와 Pstop 컬럼에 엑세스할 

                                       파티션 번호가 출력된다. 

    ● 동적 파티션 Pruning : 파티션 키 컬럼을 바인드 변수로 조회하면 쿼리 최적화 시점에는

                                       엑세스할 파티션을 미리 결정할 수 없다. 실행 시점이 돼서야 

                                       사용자가 입력한 값에 따라 결정되며, 실행계획의 Pstart와 

                                       Pstop 컬럼에는 'KEY'라고 표시된다. NL 조인할 때도 Inner 테이블이 

                                       조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다. 

 - 파티션 컬럼에 IN-List 조건을 사용하면 상수 값이더라도 아래처럼 'KEY(I)'라고 표시 된다. 

 - NL조인에서도 inner 테이블일 때 동적 Pruning이 일어난다. 

파티션 Pruning 기능에 따른 I/O 수행량 비교 

 - 주 파티션과 서브 파티션이 있을 때 서브 파티션 키 컬럼을 함수로 가공하면 서브 파티션 

   Pruning을 사용할 수 없다. 

 - 주 파티션 키 컬럼을 가공함으로써 서브 파티션 키 컬럼에 묵시적 형변환이 일어난다. 

동적 파티션 Pruning 시 테이블 레벨 통계 사용 

 - 동적 파티션 Pruning이 일어나면 쿼리 최적화에 테이블 레벨 통계가 사용된다. 

   반면, 정적 파티션 Pruning일 때는 파티션 레벨 통계가 사용된다. 

 - 테이블 레벨 통계는 파티션 레벨 통계보다 다소 부정확하기 때문에 옵티마이저가 가끔 

   잘못된 실행계획을 수립하는 경우가 생기며, 이는 바인드 변수 때문에 생기는 대표적인 

   부작용 중 하나다. 

서브쿼리 Pruning 

 - 조인에 사용되는 고급 파티션 Pruning 기법으로는 아래 두 가지가 있다. 

    ● 서브 쿼리 Pruning (8i ~ ) 

    ● 조인 필터 Pruning (11g ~ )

 - 오라클은 Recursive 서브쿼리를 이용한 동적 파티션 Pruning을 고려한다. 

   이른바 '서브쿼리 Pruning'이라고 불리는 메커니즘으로서, 엑세스해야할 파티션 번호 목록을 

   특별한 쿼리를 통해 구해 이를 이용해 필요한 파티션만 스캔할 수 있다. 

 - KEY(SQ)에서 SQ는 SubQuery를 뜻한다. 이 방식으로 파티션 Pruning 하려면 

   드라이빙 테이블을 한 번 더 읽게 되므로 경우에 따라 총 비요이 오히려 증가할 수 있다. 

   따라서 서브쿼리 Pruning 적용 여부는 옵티마이저가 비용을 고려해 내부적으로 결정한다. 

 - 옵티마이저 결정에 영향을 미치는 Hidden 파라미터는 3가지가 있다. 

    ● _subquery_pruning_cost_factor 

    ● _subquery_pruning_reduction

    ● _subquery_pruning_enabled 

        (위 파라미터의 자세한 설명은 p.649 참조) 

 - 제거될 것으로 예상되는 파티션 개수가 상당히 (기본 값에 의하면 50%) 많고, where 조건절을 

   가진 드라이빙 테이블이 파티션 테이블에 비해 상당히 작을 때만 서브쿼리 Pruning이 작동 

 - 참고로, 아래와 같이 설정하면 옵티마이저에 의해 계산된 비용과 상관없이 항상 서브쿼리 

   Pruning을 실시한다. 

    ● _subquery_pruning_cost_factor =1 

    ● _subquery_pruning_reduction = 100

조인 필터 Pruning 

 - 서브쿼리 Pruning은 드라이빙 테이블을 한 번 더 엑세스하는 추가 비용이 발생한다. 

   그래서 11g부터 오라클은 블룸 필터 알고리즘을 기반으로 한 조인 필터 Pruning 방식을 

   도입하였다. 

 - 블룸 필터는 교집합이 작을 때 큰효과를 발휘한다. 

   * 해당 알고리즘은 p.648을 참고한다. 

 - 블룸 필터 알고리즘에서 false positive를 줄이는 방법은 더 많은 비트를 할당하거나 

   더 많은 해시 함수를 사용하면 false positive 발생 가능성은 줄어든다. 

   공간/시간의 효율성과 false positive 발생 가능성은 서로 트레이드 오프 관계이므로 

   적정한 개수의 비트와 해시 함수를 사용하는 것이 과제다. 

조인 필터 (=블룸 필터) Pruning 

 - '조인 필터 Pruning' 또는 '블룸 필터 Pruning'이라고 부르는 이 기능은 11g부터 적용되기 

   시작했으며 파티션 테이블과 조인할 때 , 읽지 않아도 되는 파티션을 제거하는 것이다. 

 - 실행계획에서 part join filter create 는 블룸 필터를 생성하는 단계 , 

    partition range join-filter는 블룸 필터를 이용해 파티션 Pruning 하는 단계를 나타낸다. 

 - 블룸필터의 역활은, 교집합 아님이 확실한 원소를 찾는 데에 있다. 이 알고리즘을 사용하는 

   조인 필터 Pruning도 조인 대상 집합을 확실히 포함하는 파티션을 찾는 게 아니라, 확실히 

   포함하지 않는 파티션을 찾는다. 

 - 서브쿼리 Pruning 과 비교해 보면, 드라이빙 테이블이 클수록 조인 필터 Pruning이 유리하다.

SQL 조건절 작성 시 주의사항     

 - 파티션 Pruning을 위해서라도 like 보다는 가급적 between 연산자를 이용해 정확한 검색구산을

   지정하는 것이 바람직하다.