2.15 PM (Predicate move Around )

Where 조건을 다른 뷰에 이동시켜라

 

PM

개념 : 조건절을 다른 뷰 혹은 인라인 뷰에 Copy 하는 기능이다.

수행조건 : 뷰 혹은 인라인 뷰가 존재하여야 한다.

 

PM EX)

인라인뷰가 여러개 있고 각각의 Where 절에 공통적인 조건들이 있어야 한다고 가장하자.

이럴경우 인라인 뷰의 WHere 절에 똑같은 조건들을 반복해서 사용해야 할까.?

물론 그런 경우도 있지만 아래의 SQL을 본다면 생각이 달라질 것이다.

 

create index dept_IX_01 ON department(department_id,location_id);

SELECT /*+ QB_NAME (v_outer)*/
       v1.*
  FROM (SELECT /*+ QB_NAME (IV1) NO_MORDER */
               e1.*,d1.location_id
          FROM employee e1,department d1
         WHERE e1.department_id = d1.department_id
           AND d1.department_id = 30) v1,
       (SELECT   /*+ QB_NAME (IV2) NO_MORDER */
                 AVG (salary) avg_sal_dept,
                 d2.department_id
            FROM employee e2, department d2,loc l2
           WHERE e2.department_id = d2.department_id
             AND l2.location_id = d2.location_id
        GROUP BY d2.department_id
       ) v2

 WHERE v1.department_id = v2.department_id
   AND v1.salary > v2.avg_sal_dept ;

 

 

이 SQL 을 보면 인라인 뷰 v1에 d1.department_id = 30 라는 조건이 있지만. v2 에는 해당 조건이 없다.  PM 기능으로 인하여 v1의 조건이 v2 로 파고들어야 한다.

아래의 실행 계획으로 이 기능이 성공적으로 수행되었음을 알 수 있다.

 

-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | E-Rows |E-Bytes| Cost | E-Time  
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |        |       |     5|         
|*  1 |  HASH JOIN                      |                   |      1 |   172 |     5| 00:00:01
|   2 |   VIEW                          |                   |      1 |    26 |     2| 00:00:01
|   3 |    HASH GROUP BY                |                   |      1 |    17 |     2| 00:00:01
|   4 |     NESTED LOOPS                |                   |        |       |      |         
|   5 |      NESTED LOOPS               |                   |      6 |   102 |     2| 00:00:01
|   6 |       NESTED LOOPS              |                   |      1 |    10 |     1| 00:00:01
|*  7 |        INDEX RANGE SCAN         | DEPT_IX_01        |      1 |     7 |     1| 00:00:01
|*  8 |        INDEX UNIQUE SCAN        | LOC_ID_PK         |     23 |    69 |     0|         
|*  9 |       INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |      6 |       |     0|         
|  10 |      TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      6 |    42 |     1| 00:00:01
|  11 |   VIEW                          |                   |      6 |   876 |     2| 00:00:01
|  12 |    NESTED LOOPS                 |                   |        |       |      |         
|  13 |     NESTED LOOPS                |                   |      6 |   456 |     2| 00:00:01
|* 14 |      INDEX RANGE SCAN           | DEPT_IX_01        |      1 |     7 |     1| 00:00:01
|* 15 |      INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      6 |       |     0|         
|  16 |     TABLE ACCESS BY INDEX ROWID | EMPLOYEE          |      6 |   414 |     1| 00:00:01
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------- 
   1 - access("V1"."DEPARTMENT_ID"="V2_DEPT"."DEPARTMENT_ID")
       filter("V1"."SALARY">"V2_DEPT"."AVG_SAL_DEPT")
   7 - access("D2"."DEPARTMENT_ID"=30)
   8 - access("L2"."LOCATION_ID"="D2"."LOCATION_ID")
   9 - access("E2"."DEPARTMENT_ID"=30)
  14 - access("D1"."DEPARTMENT_ID"=30)
  15 - access("E1"."DEPARTMENT_ID"=30)

 

Predicate Information 을 보면 두번째 뷰 (v2 ) 에  d2.department_id = 30 조건과  e2.department_id = 30 조건을 파고들어 갔다.

다시말하면 department_id = 30 인 조건을 v2에 추가 시킨것 이다

 

SELECT /*+ QB_NAME (v_outer)*/
       v1.*
  FROM (SELECT /*+ QB_NAME (IV1) NO_MORDER */
               e1.*,d1.location_id
          FROM employee e1,department d1
         WHERE e1.department_id = d1.department_id
           AND d1.department_id = 30) v1,
       (SELECT   /*+ QB_NAME (IV2) NO_MORDER */
                 AVG (salary) avg_sal_dept,
                 d2.department_id
            FROM employee e2, department d2,location l2
           WHERE e2.department_id = d2.department_id
             AND l2.location_id = d2.location_id

             AND d2.department_id = 30
             AND e2.department_id = 30
        GROUP BY d2.department_id
       ) v2

 WHERE v1.department_id = v2.department_id
   AND v1.salary > v2.avg_sal_dept ;

 

이 현상 때문에 d2/e2 인덱스를 사용하였는데 결과는 아주 성공적이다.

그렇다면 오라클은 어떤 과정을 거칠까.?

 

 

10053 trace 정보를 보자.

PM : Considering predicate move-around in query block V_OUTER(#1)

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

Predicate Move-Around (PM)

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

 

PM: passed validity checks.

PM: Pulled up predicate "V1".DEPARTMENT_ID*=30

 from query block IV1 (#2) to query block V_OUTER (#1)

PM : Pushed down predicate "D2"."DEPARTMENT_ID*=30

 from queyr block V_OUTER (#1) to query block IV2(#3)

 

10053 trace 수행순서는 다음과같고 항상 순서는 동일하다.

 

1.먼저 PM이 수행될 수 있는지 검사한다.

2.v1에서 where 조건 de.department_id = 30 을 바깥쪽 메인 쿼리로 이동시킨다.

이것을 predicate Pull up 이라고 한다.

3.메인 쿼리로 옮겨진 Where 조건을 v2에 복사한다.

이것을 Predicate Push Down 이라고 한다.

4. 최종 결과에서 중복된 조건절이 존재하면 삭제 한다.

 

즉 v1,v2  가 메인 쿼리 ( V_OUTER) 에서 department_id 기준으로 조인 되고 있기 때문에 조건적을 v_outer 로 빼낸 다음에 v2 에 조건절을 밀어 넣고 있다. 이것은 JPPD 기능과 유사한 면이있지만. Predcate putt up 이 발생한다는 점과  Hash 조인 등에서도 PM 이 발생한다는 점에서 엄연히 다르다. 오라클 조건절을 이리 저리 옮기면서 SQL 의 최적화를 시도하고있다.

 

PM 이 발생 하는 조건

1.뷰( 혹은 인라인 뷰)가 1개 혹은 그 이상 존재해야 한다. ( v1,v2 가 있음 )

2.인라인뷰내에 특정 조건이 존재하고 뷰의 바깥쪽에서 특정 조건을 사용한 컬럼으로 조인이 발생해야 한다. 위의 예제에서는 d1.department_id = 30  으로 v1 내부에 조건이 존재하고 v_outer 에서 department_id 컬럼으로 v2 와 조인을 하고있다.

3.veiw merge 발생하지 않아야 한다.

4.파라미터 _pred_move_around 가 True로 지정이 되어야 한다.