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

3.12 Multi Level JPPD : 뷰 내부에 또 다른 뷰가 있더라도 메인 쿼리의 조인 조건을 침투시켜라

Multi Level JPPD

뷰 내부에 또 다른 Child 뷰가 존재할 때 Child 뷰 내부까지 메인 쿼리의 조인 조건이 침투하는 기능
11g부터 사용.

JPPD 예제

{code:sql

borderStyle=solid}
SELECT j.employee_id, e.last_name, j.job_id, j.start_date, e.city, e.department_name
FROM job_history j,
(SELECT e.employee_id, e.last_name, d.city, d.department_id, d.department_name
FROM employee e,
(SELECT /*+ NO_MERGE */
d.department_id, l.city, d.department_name
FROM department d, location l
WHERE d.location_id = l.location_id ) d
) e
WHERE j.employee_id = e.employee_id(+)
AND j.department_id = e.department_id(+) ;

-+

---+

Id

Operation

Name

Rows

Bytes

Cost

Time

-+

---+

0	SELECT STATEMENT				33
1	NESTED LOOPS OUTER		2461	209K	33	00:00:01
2	TABLE ACCESS FULL	JOB_HISTORY	10	310	3	00:00:01
3	VIEW PUSHED PREDICATE		1	56	3	00:00:01
4	NESTED LOOPS		1	63	3	00:00:01
5	TABLE ACCESS BY INDEX ROWID	EMPLOYEE	1	12	1	00:00:01
6	INDEX UNIQUE SCAN	EMP_EMP_ID_PK	1		0
7	VIEW		1	51	2	00:00:01
8	NESTED LOOPS		1	32	2	00:00:01
9	TABLE ACCESS BY INDEX ROWID	DEPARTMENT	1	20	1	00:00:01
10	INDEX UNIQUE SCAN	DEPT_ID_PK	1		0
11	TABLE ACCESS BY INDEX ROWID	LOCATION	23	276	1	00:00:01
12	INDEX UNIQUE SCAN	LOC_ID_PK	1		0

-+

---+
Predicate Information:

--
6 - access("E"."EMPLOYEE_ID"="J"."EMPLOYEE_ID")
10 - access("D"."DEPARTMENT_ID"="J"."DEPARTMENT_ID") <--
12 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

|
||JPPD 예제 - QT가 바꾼 쿼리||
|{code:sql|borderStyle=solid}
SELECT j.employee_id, e.last_name, j.job_id, j.start_date, e.city, e.department_name
  FROM job_history j,
      LATERAL (SELECT e.employee_id, e.last_name, d.city, d.department_id, d.department_name
          FROM employee e,
               LATERAL (SELECT /*+ NO_MERGE */  												<--
                       d.department_id, l.city, d.department_name
                  FROM department d, location l
                 WHERE d.location_id = l.location_id 
                 AND j.department_id = e.department_id)(+) d   										<--
          WHERE j.employee_id = e.employee_id
   )(+) e;

|

JPPD 예제 - 10053 Trace

{code:sql}
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$2 (#2)
JPPD: Checking validity of push-down from query block SEL$2 (#2) to query block SEL$3 (#3)
Check Basic Validity for Non-Union View for query block SEL$3 (#3)
JPPD: JPPD bypassed: View has unsupported constructs.
JPPD: No valid views found to push predicate into.
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)
"J"."EMPLOYEE_ID"="E"."EMPLOYEE_ID"() AND "J"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"()
try to generate transitive predicate from check constraints for query block SEL$1 (#1)
finally: "J"."EMPLOYEE_ID"="E"."EMPLOYEE_ID"() AND "J"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"()

kkqfppRelFilter: Not pushing filter predicates in query block SEL$2 (#2) because no predicate to push
FPD: Considering simple filter push in query block SEL$2 (#2)
??
FPD: Considering simple filter push in query block SEL$3 (#3)
"D"."LOCATION_ID"="L"."LOCATION_ID"
try to generate transitive predicate from check constraints for query block SEL$3 (#3)
finally: "D"."LOCATION_ID"="L"."LOCATION_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: 42.5739 Degree: 1 Card: 2461.0000 Bytes: 243639
Resc: 42.5739 Resc_io: 40.5000 Resc_cpu: 45938944
Resp: 42.5739 Resp_io: 40.5000 Resc_cpu: 45938944
kkoqbc-subheap (delete addr=0x2A81658C, in-use=26956, alloc=30748)
kkoqbc-end:
:
call(in-use=48448, alloc=98240), compile(in-use=181844, alloc=185428), execution(in-use=4240, alloc=8136)

kkoqbc: finish optimizing query block SEL$1 (#1)
CBQT: Saved costed qb# 3 (SEL$3), key = SEL$3_00000200_2
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 = 42.57 <--
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 "J"."EMPLOYEE_ID"="E"."EMPLOYEE_ID"(+) <--
from query block SEL$1 (#1) to query block SEL$2 (#2)
JPPD: Push dest of pred 0x2A838F50 is qb 0x2A83CF80:query block SEL$2 (#2)
JPPD: Pushing predicate "J"."DEPARTMENT_ID"="E"."DEPARTMENT_ID"(+) <--
from query block SEL$1 (#1) to query block SEL$2 (#2)
JPPD: Push dest of pred 0x2A8200C4 is qb 0x2A83CF80:query block SEL$2 (#2)

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

-
QUERY BLOCK SIGNATURE

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

FPD: Considering simple filter push in query block SEL$1 (#1)
??
try to generate transitive predicate from check constraints for query block SEL$1 (#1)
finally: ??

kkqfppRelFilter: Not pushing filter predicates in query block SEL$639F1A6F (#2) because no predicate to push
FPD: Considering simple filter push in query block SEL$639F1A6F (#2)
:B1="D"."DEPARTMENT_ID" AND :B2="E"."EMPLOYEE_ID"
try to generate transitive predicate from check constraints for query block SEL$639F1A6F (#2)
finally: "D"."DEPARTMENT_ID"=:B1 AND "E"."EMPLOYEE_ID"=:B2 <--

FPD: Following are pushed to where clause of query block SEL$3 (#3)
"D"."DEPARTMENT_ID"=:B1
JPPD: Push dest of pred 0x2A8200C4 is qb 0x2A83D3FC:query block SEL$3 (#3)
FPD: JPPD predicate pushed into view SEL$3 <--
FPD: Considering simple filter push in query block SEL$3 (#3)
"D"."LOCATION_ID"="L"."LOCATION_ID" AND "D"."DEPARTMENT_ID"=:B1 <--
try to generate transitive predicate from check constraints for query block SEL$3 (#3)
finally: "D"."LOCATION_ID"="L"."LOCATION_ID" AND "D"."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: 33.0131 Degree: 1 Card: 2461.0000 Bytes: 214107
Resc: 33.0131 Resc_io: 33.0000 Resc_cpu: 289150
Resp: 33.0131 Resp_io: 33.0000 Resc_cpu: 289150
kkoqbc-subheap (delete addr=0x2A833C40, in-use=25764, alloc=30748)
kkoqbc-end:
:
call(in-use=97276, alloc=130992), compile(in-use=262040, alloc=267024), execution(in-use=4672, alloc=8136)

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 = 33.01 <--
JPPD: Will use JPPD from SEL$1 (#1) to SEL$2 (#2). <--

|
* Multi Level JPPD 변환을 control하는 파라미터는 _optimizer_multi_level_push_pred (default true)
* PUSH_PRED / NO_PUSHE_PRED 힌트 사용.


* 아래의 SQL은 Multi Level JPPD가 아니라 Single Level JPPD이다.
||Single Level JPPD|
|{code:sql|borderStyle=solid}
SELECT /*+ GATHER_PLAN_STATISTICS */
       e.employee_id, e.last_name, e.phone_number, d.city, d.country_name
  FROM employee e,
       (SELECT d.department_id, l.city,  c.country_name
          FROM department d,
               location l,
               (SELECT /*+ NO_MERGE */
                       country_id, country_name
                  FROM country) c
         WHERE d.location_id = l.location_id 
           AND l.country_id = c.country_id(+) ) d 
 WHERE e.department_id = d.department_id(+) 
   AND e.job_id = 'AD_PRES' ;
   
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |      1 |        |       |     4 (100)|       |         1 |00:00:00.01 |       8 |
|   1 |  NESTED LOOPS OUTER             |                 |      1 |      1 |    77 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | EMPLOYEE        |      1 |      1 |    36 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN             | EMP_JOB_IX      |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|   4 |   VIEW PUSHED PREDICATE         |                 |      1 |      1 |    41 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|   5 |    NESTED LOOPS OUTER           |                 |      1 |      1 |    46 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|   6 |     NESTED LOOPS                |                 |      1 |      1 |    22 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|   7 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENT      |      1 |      1 |     7 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  8 |       INDEX UNIQUE SCAN         | DEPT_ID_PK      |      1 |      1 |       |     0   (0)|       |         1 |00:00:00.01 |       1 |
|   9 |      TABLE ACCESS BY INDEX ROWID| LOCATION        |      1 |     23 |   345 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|* 10 |       INDEX UNIQUE SCAN         | LOC_ID_PK       |      1 |      1 |       |     0   (0)|       |         1 |00:00:00.01 |       1 |
|  11 |     VIEW PUSHED PREDICATE       |                 |      1 |      1 |    24 |     0   (0)|       |         1 |00:00:00.01 |       1 |
|* 12 |      INDEX UNIQUE SCAN          | COUNTRY_C_ID_PK |      1 |      1 |    15 |     0   (0)|       |         1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1        / E@SEL$1
   3 - SEL$1        / E@SEL$1
   4 - SEL$639F1A6F / D@SEL$1
   5 - SEL$639F1A6F
   7 - SEL$639F1A6F / D@SEL$2
   8 - SEL$639F1A6F / D@SEL$2
   9 - SEL$639F1A6F / L@SEL$2
  10 - SEL$639F1A6F / L@SEL$2
  11 - SEL$B01C6807 / C@SEL$2
  12 - SEL$B01C6807 / COUNTRY@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$B01C6807")
      PUSH_PRED(@"SEL$2" "C"@"SEL$2" 2)															<--
      OUTLINE_LEAF(@"SEL$639F1A6F")
      PUSH_PRED(@"SEL$1" "D"@"SEL$1" 1)															<--
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))
      NO_ACCESS(@"SEL$1" "D"@"SEL$1")
      LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
      USE_NL(@"SEL$1" "D"@"SEL$1")
      INDEX_RS_ASC(@"SEL$639F1A6F" "D"@"SEL$2" ("DEPARTMENT"."DEPARTMENT_ID"))
      INDEX_RS_ASC(@"SEL$639F1A6F" "L"@"SEL$2" ("LOCATION"."LOCATION_ID"))
      NO_ACCESS(@"SEL$639F1A6F" "C"@"SEL$2")
      LEADING(@"SEL$639F1A6F" "D"@"SEL$2" "L"@"SEL$2" "C"@"SEL$2")
      USE_NL(@"SEL$639F1A6F" "L"@"SEL$2")
      USE_NL(@"SEL$639F1A6F" "C"@"SEL$2")
      INDEX(@"SEL$B01C6807" "COUNTRY"@"SEL$3" ("COUNTRY"."COUNTRY_ID"))
      END_OUTLINE_DATA
  */

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

   3 - access("E"."JOB_ID"='AD_PRES')
   8 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
  10 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
  12 - access("COUNTRY_ID"="L"."COUNTRY_ID")

|

3.12 Multi Level JPPD 뷰 내부에 또 다른 뷰가 있더라도 메인 쿼리의 조인 조건을 침투시켜라 (by jongmali) [2013.07.06]

3.12 Multi Level JPPD : 뷰 내부에 또 다른 뷰가 있더라도 메인 쿼리의 조인 조건을 침투시켜라