꿈꾸는 개발자, DBA 커뮤니티 구루비

3.13 JPPD Extension : Distinct나 Group By, Semi/Anti-join을 사용한 뷰에 조인 조건을 침투시켜라

JPPD Extension

10g까지 (Distinct, Group by, Semi/Anti Join)을 사용하는 뷰에 대해서는 조인 조건이 뷰 내부로 파고들지 못했다.
11g부터는 이러한 제약조건들이 사라졌다.
이 기능을 JPPD Extension이라 한다.

JPPD 예제
{code:sql	borderStyle=solid} SELECT /+ LEADING(d) USE_NL(e) / d.department_id, d.department_name, e.job_title, e.sum_sal, max_sal FROM department d, (SELECT e.department_id, e.job_id, MIN (j.job_title) job_title, SUM (e.salary) sum_sal, MAX (e.salary) max_sal FROM employee e, job j WHERE e.job_id = j.job_id GROUP BY e.department_id, e.job_id ) e WHERE d.department_id = e.department_id(+) ;

-+

---+

Id

Operation

Name

Rows

Bytes

Cost

Time

-+

---+

0	SELECT STATEMENT				138
1	NESTED LOOPS OUTER		97	6693	138	00:00:02
2	TABLE ACCESS FULL	DEPARTMENT	27	540	3	00:00:01
3	VIEW PUSHED PREDICATE		1	49	5	00:00:01
4	SORT GROUP BY		6	312	5	00:00:01
5	MERGE JOIN		9	468	5	00:00:01
6	TABLE ACCESS BY INDEX ROWID	JOB	19	513	2	00:00:01
7	INDEX FULL SCAN	JOB_ID_PK	19		1	00:00:01
8	SORT JOIN		10	250	3	00:00:01
9	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	10	250	2	00:00:01
10	INDEX RANGE SCAN	EMP_DEPARTMENT_IX	10		1	00:00:01

-+

---+
Predicate Information:

--
8 - access("E"."JOB_ID"="J"."JOB_ID")
8 - filter("E"."JOB_ID"="J"."JOB_ID")
10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") <-- Group By를 사용한 Complex View임에도 메인 쿼리의 조인 조건이 파고 들었다.

|
||JPPD 예제 - QT가 바꾼 쿼리||
|{code:sql|borderStyle=solid}
SELECT /*+ LEADING(d) USE_NL(e) */
       d.department_id, d.department_name, e.job_title, e.sum_sal, max_sal
  FROM department d,
       LATERAL (SELECT   e.department_id, e.job_id, MIN (j.job_title) job_title,
                 SUM (e.salary) sum_sal, MAX (e.salary) max_sal
            FROM employee e, job j
           WHERE e.job_id = j.job_id
           AND d.department_id = e.department_id
           GROUP BY e.job_id )(+) e;

|

JPPD 예제 - 10053 Trace

{code:sql}
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD: Checking validity of push-down from query block SEL$1 (#1) to query block SEL$2 (#2)
Check Basic Validity for Non-Union View for query block SEL$2 (#2)
JPPD: Passed validity checks
JPPD: JPPD: Pushdown from query block SEL$1 (#1) passed validity checks.
Join-Predicate push-down on query block SEL$1 (#1)
JPPD: Using search type: linear <--
JPPD: Considering join predicate push-down
JPPD: Starting iteration 1, state space = (2) : (0)
JPPD: Performing join predicate push-down (no transformation phase) from query block SEL$1 (#1) to query block SEL$2 (#2) <--

FPD: Considering simple filter push in query block SEL$1 (#1)
"D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+)
try to generate transitive predicate from check constraints for query block SEL$1 (#1)
finally: "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+)

FPD: Considering simple filter push in query block SEL$2 (#2)
"E"."JOB_ID"="J"."JOB_ID"
try to generate transitive predicate from check constraints for query block SEL$2 (#2)
finally: "E"."JOB_ID"="J"."JOB_ID"

JPPD: Costing transformed query. <--

.......................

*********************************
Number of join permutations tried: 1
*********************************
Trying or-Expansion on query block SEL$1 (#1)
Transfer Optimizer annotations for query block SEL$1 (#1)
Final cost for query block SEL$1 (#1) - All Rows Plan:
Best join order: 1
Cost: 192.1920 Degree: 1 Card: 97.0000 Bytes: 7566
Resc: 192.1920 Resc_io: 138.0000 Resc_cpu: 1200402490
Resp: 192.1920 Resp_io: 138.0000 Resc_cpu: 1200402490
kkoqbc-subheap (delete addr=0x2AC48CB0, in-use=19036, alloc=22500)
kkoqbc-end:
:
call(in-use=24032, alloc=65488), compile(in-use=168852, alloc=173156), execution(in-use=3348, alloc=4060)

kkoqbc: finish optimizing query block SEL$1 (#1)
CBQT: Saved costed qb# 2 (SEL$2), key = SEL$2_00004200_2
CBQT: Saved costed qb# 1 (SEL$1), key = SEL$1_00000000_0
JPPD: Updated best state, Cost = 192.19 <--
JPPD: Starting iteration 2, state space = (2) : (1) <--
JPPD: Performing join predicate push-down (candidate phase) from query block SEL$1 (#1) to query block SEL$2 (#2) <--
JPPD: Pushing predicate "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+) <--
from query block SEL$1 (#1) to query block SEL$2 (#2)
JPPD: Push dest of pred 0x2AC402FC is qb 0x2AC32908:query block SEL$2 (#2)

Registered qb: SEL$639F1A6F 0x2ac32908 (PUSHED PREDICATE SEL$2; SEL$1; "E"@"SEL$1" 1) <--

-
QUERY BLOCK SIGNATURE

-
signature (): qb_name=SEL$639F1A6F nbfros=2 flg=0
fro(0): flg=0 objn=74263 hint_alias="E"@"SEL$2" <--
fro(1): flg=0 objn=74270 hint_alias="J"@"SEL$2" <--

FPD: Considering simple filter push in query block SEL$1 (#1)
??
FPD: Considering simple filter push in query block SEL$639F1A6F (#2)
"E"."JOB_ID"="J"."JOB_ID" AND :B1="E"."DEPARTMENT_ID"
try to generate transitive predicate from check constraints for query block SEL$639F1A6F (#2) <--
finally: "E"."JOB_ID"="J"."JOB_ID" AND "E"."DEPARTMENT_ID"=:B1 <--

JPPD: Costing transformed query.

................

*********************************
Number of join permutations tried: 1
*********************************
Or-Expansion validity checks failed on query block SEL$1 (#1) because no push join pred. would need new qbc as in mp
Transfer Optimizer annotations for query block SEL$1 (#1)
Final cost for query block SEL$1 (#1) - All Rows Plan:
Best join order: 1
Cost: 138.0536 Degree: 1 Card: 97.0000 Bytes: 6693
Resc: 138.0536 Resc_io: 111.0000 Resc_cpu: 599262696
Resp: 138.0536 Resp_io: 111.0000 Resc_cpu: 599262696
kkoqbc-subheap (delete addr=0x2AC55030, in-use=18444, alloc=22500)
kkoqbc-end:
:
call(in-use=51044, alloc=98240), compile(in-use=221780, alloc=225664), execution(in-use=3576, alloc=4060)

kkoqbc: finish optimizing query block SEL$1 (#1)
CBQT: Saved costed qb# 1 (SEL$1), key = SEL$1_00010000_0
JPPD: Updated best state, Cost = 138.05 <--
JPPD: Will use JPPD from SEL$1 (#1) to SEL$2 (#2). <--

kkqctdrvTD-cleanup: transform(in-use=25712, alloc=29188) :
call(in-use=51044, alloc=98240), compile(in-use=233604, alloc=238036), execution(in-use=3576, alloc=4060)

kkqctdrvTD-end:
call(in-use=51044, alloc=98240), compile(in-use=204420, alloc=238036), execution(in-use=3576, alloc=4060)

JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD: JPPD: Pushdown from query block SEL$1 (#1) passed validity checks.
JPPD: Performing join predicate push-down (final phase) from query block SEL$1 (#1) to query block SEL$2 (#2) <--
JPPD: Pushing predicate "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+) <--
from query block SEL$1 (#1) to query block SEL$2 (#2) <--
JPPD: Push dest of pred 0x2AC34FA4 is qb 0x0EAA2770:query block SEL$2 (#2)

|

h3. JPPD Extension의 획기적인 기능
* 10g까지는 아래의 3가지 경우에만 JPPD가 발생
** Union을 사용한 뷰
** Union All을 사용한 뷰
** Outer 조인을 사용한 뷰

||11g부터 Group By, Distinct 등을 사용하는 뷰는 Outer 조인이 필요 없다.||
|{code:sql|borderStyle=solid}
SELECT /*+ LEADING(d) USE_NL(e) */
       d.department_id, d.department_name, 
       e.job_title, e.sum_sal, max_sal
  FROM department d,
       (  SELECT /*+ NO_MERGE */ e.department_id, e.job_id, MIN (j.job_title) job_title,
                 SUM (e.salary) sum_sal, MAX (e.salary) max_sal
            FROM employee e, job j
           WHERE e.job_id = j.job_id
        GROUP BY e.department_id, e.job_id) e
 WHERE d.department_id = e.department_id ;
   
------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |    95 |  6460 |   138  (20)| 00:00:02 |
|   1 |  NESTED LOOPS                    |                   |    95 |  6460 |   138  (20)| 00:00:02 |
|   2 |   TABLE ACCESS FULL              | DEPARTMENT        |    27 |   567 |     3   (0)| 00:00:01 |
|   3 |   VIEW PUSHED PREDICATE          |                   |     1 |    47 |     5  (20)| 00:00:01 |
|   4 |    SORT GROUP BY                 |                   |     6 |   312 |     5  (20)| 00:00:01 |
|   5 |     MERGE JOIN                   |                   |     9 |   468 |     5  (20)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID | JOB               |    19 |   513 |     2   (0)| 00:00:01 |
|   7 |       INDEX FULL SCAN            | JOB_ID_PK         |    19 |       |     1   (0)| 00:00:01 |
|*  8 |      SORT JOIN                   |                   |    10 |   250 |     3  (34)| 00:00:01 |
|   9 |       TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |    10 |   250 |     2   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("E"."JOB_ID"="J"."JOB_ID")
       filter("E"."JOB_ID"="J"."JOB_ID")
  10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  
-- 11g부터는 Outer 조인이 없는 경우에도 JPPD가 발생함을 알 수 있다.

|

Semi-/Anti-join을 사용한 경우 JPPD 예제
{code:sql	borderStyle=solid} SELECT /+ QB_NAME(MAIN) / e.* FROM employee e WHERE e.job_id = 'AD_ASST' AND EXISTS ( SELECT /+ QB_NAME(SUB) UNNEST / 1 FROM department d, location l WHERE d.department_id = e.department_id AND d.location_id = l.location_id ) ;

-

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

-

0	SELECT STATEMENT		1	71	3 (0)	00:00:01
1	NESTED LOOPS SEMI		1	71	3 (0)	00:00:01	<--	2	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	1	69	2 (0)	00:00:01
3	INDEX RANGE SCAN	EMP_JOB_IX	1		1 (0)	00:00:01
4	VIEW PUSHED PREDICATE	VW_SQ_1	1	2	1 (0)	00:00:01	<--	5	NESTED LOOPS		1	10	1 (0)	00:00:01
6	TABLE ACCESS BY INDEX ROWID	DEPARTMENT	1	7	1 (0)	00:00:01
7	INDEX UNIQUE SCAN	DEPT_ID_PK	1		0 (0)	00:00:01
8	INDEX UNIQUE SCAN	LOC_ID_PK	23	69	0 (0)	00:00:01

-

Predicate Information (identified by operation id):

---

3 - access("E"."JOB_ID"='AD_ASST')
7 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

SELECT /*+ QB_NAME(MAIN) */
e.employee_id, e.last_name, e.email
FROM employee e
WHERE e.job_id = 'AD_VP'
AND NOT EXISTS ( SELECT /*+ QB_NAME(SUB) UNNEST */ 1
FROM department d, location l
WHERE d.department_id = e.department_id
AND d.location_id = l.location_id
AND d.manager_id = 205 );

-

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

-

0	SELECT STATEMENT		1	34	4 (0)	00:00:01
1	NESTED LOOPS ANTI		1	34	4 (0)	00:00:01	<--	2	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	2	64	2 (0)	00:00:01
3	INDEX RANGE SCAN	EMP_JOB_IX	2		1 (0)	00:00:01
4	VIEW PUSHED PREDICATE	VW_SQ_1	1	2	1 (0)	00:00:01	<--	5	NESTED LOOPS		1	23	1 (0)	00:00:01
6	TABLE ACCESS BY INDEX ROWID	DEPARTMENT	1	20	1 (0)	00:00:01
7	INDEX UNIQUE SCAN	DEPT_ID_PK	1		0 (0)	00:00:01
8	INDEX UNIQUE SCAN	LOC_ID_PK	23	69	0 (0)	00:00:01

-

Predicate Information (identified by operation id):

---

3 - access("E"."JOB_ID"='AD_VP')
6 - filter("D"."MANAGER_ID"=205)
7 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

|
* JPPD Extenstion 변환을 control하는 파라미터는 _optimizer_extend_jppd_view_types (default true)
* _optimizer_extend_jppd_view_types 값을 false로 할 경우 JPPD Extension 기능을 사용할 수 없다.
|{code:sql|borderStyle=solid}
ALTER SESSION SET "_optimizer_extend_jppd_view_types"=FALSE;
SELECT /*+ QB_NAME(MAIN) PUSH_PRED(@SUB) */
       e.employee_id, e.last_name, e.email
  FROM employee e
 WHERE e.job_id = 'AD_VP'
   AND NOT EXISTS ( SELECT /*+ QB_NAME(SUB) UNNEST */ 1
                      FROM department d, location l
                     WHERE d.department_id = e.department_id
                       AND d.location_id = l.location_id
                       AND d.manager_id = 205 );

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |    45 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN ANTI              |            |     1 |    45 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE   |     2 |    64 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX |     2 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                       | VW_SQ_1    |     1 |    13 |     3   (0)| 00:00:01 |
|   5 |    NESTED LOOPS              |            |     1 |    23 |     3   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL        | DEPARTMENT |     1 |    20 |     3   (0)| 00:00:01 |							<--
|*  7 |     INDEX UNIQUE SCAN        | LOC_ID_PK  |     1 |     3 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ITEM_1"="E"."DEPARTMENT_ID")
   3 - access("E"."JOB_ID"='AD_VP')
   6 - filter("D"."MANAGER_ID"=205)
   7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

|

3.13 JPPD Extension Distinct나 Group By, Semi,Anti-join을 사용한 뷰에 조인 조건을 침투시켜라 (by jongmali) [2013.07.06]

3.13 JPPD Extension : Distinct나 Group By, Semi/Anti-join을 사용한 뷰에 조인 조건을 침투시켜라