쿼리 옵티마이저
- 개요: 쿼리 옵티마이저는 SQL 엔진의 구성요소 중의 하나로, 적절한 시간 내에 효율적인 실행계획을 만들어낸다.
- 기초 사항
- 최적의 실행 계획을 찾기 위해서 모든 실행 계획의 후보들로 구성된 탐색 공간(search space)를 조사
- 여러 실행 계획들의 비용을 추정하여 가능 낮은 비용의 실행 계획을 선택
- 휴리스틱(heuristic) 선택을 기반으로 가장 유력한 실행 계획부터 시작하여 실행 계획들을 평가하는데, 이 과정에서 가장 비용이 낮은 실행 계획을 찾아냈거나 대안 실행 계획들을 너무 많이 탐색했다고 판단될 경우에는 평가를 종료(branch-and-bound 알고리즘)
- 쿼리 옵티마이저 영향 요소
| 시스템 통계 | 데이터베이스 엔진이 실행되는 장비와 스토리지 서브시스템의 성능 수치(7장) |
| 오브텍트 통계 | 데이터 딕셔너리에 저장되는 테이블, 인덱스, 칼럼 통계(8장) |
| 제약조건 | NOT NULL 제약조건, Unique Key 제약조건, PK 제약조건, 외래키 제약저건, 일부 체크(check) 제약조건 등을 활용 |
| 물리설계 | 테이블 유형, 인덱스 유형, 컬럼 순서(7장, 16장) |
| SQL control | stored outline, SQL profiles, SQL plan baseline |
| 실행 환경 | 데이터 베이스 파라미터 |
| 바인드 변수 | 바인드 변수, 바인드 변수의 데이터타입 |
| 동적 샘플링 | 쿼리 최적화를 하는 동안 추가 통계를 동적으로 수집 |
| 카디널리티 피드백 | SQL에 대한 비용 추정이 정확하지 않을 경우, SQL 실행 후 재최적화 |
| 데이터베이스 버전(패치) | 데이터베이스 버전에 따라 동작 방식이 다름 |
| 파서(parser) | 실행할 SQL을 파싱된 형태로 쿼리 옵티마이저에게 전달 |
| 논리 옵티마이저 | 여러 가지 쿼리 변환 기술을 적용하여 의미상으로 동일한 새로운 SQL 생성, 최상의 쿼리 변환 조합을 찾음 |
| 물리 옵티마이저 | 논리 옵티마이저에서 생성된 SQL 실행 계획의 비용 계산, 최저 비용 실행 계획을 선택(가장 효율적인 시행 계획을 찾기 위해 탐색 공간을 조사 |
| 비용 추적기 | 물리 옵티마이저로부터 전달받은 실행 계획에 대한 비용을 계산 |
| 로우 소스 생성기 | 실행 계획을 로우 소스 오퍼레이션 트리로 변환되어 라이브러리 캐시에 저장 |
| 실행 엔진 | 로우 소스 생성기가 생성한 로우 소스 오퍼페이션을 실행(카디널리티 피드백 실행) |
| 휴리스틱 기반의 쿼리 변환 | 특정 조건을 충족시키는 경우 적용 |
| 비용 기반의 쿼리 변환 | 비용 추정기가 산출한 비용에 근거하여, 원래 실행 계획보다 더 낮은 비용의 실행 계획이 생성될 경우 적용 |
- count 변환(휴리스틱 기반)
- COUNT(컬럼) → COUNT( * ):COUNT(컬럼)보다 COUNT( * )로 처리할 경우 선택할 수 있는 인덱스가 더 많기 때문에 변환
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: Converting COUNT(ID) to COUNT(*).
CNT: COUNT() to COUNT(*) done.
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
(중간 생략)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(ID)" FROM "CHRIS"."T" "T"
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=1240, alloc=16344), compile(in-use=63528, alloc=64472), execution(in-use=87744, alloc=89448)
kkoqbc-subheap (create addr=0x7f3b123e56d0)
****************
QUERY BLOCK TEXT
****************
SELECT count(id) FROM t
- 공통 하위 표현식 제거(휴리스틱 기반)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"T"."N1"=1
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T"."N1"=1
apadrv-start sqlid=13651654030975505982
:
call(in-use=2224, alloc=16344), compile(in-use=62784, alloc=64496), execution(in-use=90448, alloc=93528)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."N1" "N1","T"."N2" "N2" FROM "CHRIS"."T" "T" WHERE "T"."N1"=1
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=2272, alloc=16344), compile(in-use=63728, alloc=64496), execution(in-use=90448, alloc=93528)
kkoqbc-subheap (create addr=0x7f3ca72f5618)
****************
QUERY BLOCK TEXT
****************
SELECT * FROM t WHERE (n1 = 1 AND n2 = 2) OR (n1 = 1)
- or 전개(비용 기반)
----- Current SQL Statement for this session (sql_id=fq3nn8hd5k76r) -----
EXPLAIN PLAN FOR SELECT pad FROM t WHERE n1 = 1 OR n2 = 2
sql_text_length=58
sql=EXPLAIN PLAN FOR SELECT pad FROM t WHERE n1 = 1 OR n2 = 2
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T | 1 | 111 | 2 | 00:00:01 |
| 3 | INDEX RANGE SCAN | I2 | 1 | | 1 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | T | 1 | 111 | 2 | 00:00:01 |
| 5 | INDEX RANGE SCAN | I1 | 1 | | 1 | 00:00:01 |
------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("N2"=2)
4 - filter(LNNVL("N2"=2))
5 - access("N1"=1)
----- Current SQL Statement for this session (sql_id=7jjmgcd6suhyr) -----
EXPLAIN PLAN FOR SELECT pad FROM t WHERE n3 = 3 OR n4 = 4
sql_text_length=58
sql=EXPLAIN PLAN FOR SELECT pad FROM t WHERE n3 = 3 OR n4 = 4
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 128 | |
| 1 | TABLE ACCESS FULL | T | 4705 | 492K | 128 | 00:00:01 |
-------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(("N3"=3 OR "N4"=4))
- 뷰 병합
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."PAD" "PAD","T3"."ID" "ID","T3"."T1_ID" "T1_ID","T3"."PAD" "PAD" FROM "CHRIS"."T1" "T1","CHRIS"."T2" "T2","CHRIS"."T3" "T3" WHERE "T1"."ID"="T3"."T1_ID" AND "T3"."ID">6 AND "T1"."ID"="T2"."T1_ID"
kkoqbc: optimizing query block SEL$5428C7F1 (#0)
:
call(in-use=5600, alloc=16344), compile(in-use=78312, alloc=80536), execution(in-use=120800, alloc=121896)
kkoqbc-subheap (create addr=0x7fc486325618)
****************
QUERY BLOCK TEXT
****************
SELECT *
FROM (SELECT t1.* FROM t1, t2 WHERE t1.id = t2.t1_id) t12,
(SELECT * FROM t3 WHERE id > 6) t3
WHERE t12.id = t3.t1_id
SELECT "T1"."ID" "ID",
"T1"."PAD" "PAD",
"T3"."ID" "ID",
"T3"."T1_ID" "T1_ID",
"T3"."PAD" "PAD"
FROM "CHRIS"."T1" "T1",
"CHRIS"."T2" "T2",
"CHRIS"."T3" "T3"
WHERE "T1"."ID"="T3"."T1_ID"
AND "T3"."ID">6
AND "T1"."ID"="T2"."T1_ID"
********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
FPD: Current where clause predicates "T1"."N"="T2"."N"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T1"."N"="T2"."N"
FPD: Considering simple filter push (pre rewrite) in query block SEL$2 (#0)
FPD: Current where clause predicates ??
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
(중간 생략)
****************************************
Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$F5BB74E1 (#1)
GBP: Checking validity of group-by placement for query block SEL$F5BB74E1 (#1)
GBP: Bypassed: complex view merging.
DP: Checking validity of distinct placement for query block SEL$F5BB74E1 (#1)
DP: Bypassed: Query has invalid constructs.
kkqctdrvTD-cleanup: transform(in-use=4528, alloc=8136) :
call(in-use=5768, alloc=16344), compile(in-use=162904, alloc=174136), execution(in-use=207560, alloc=208064)
kkqctdrvTD-end:
call(in-use=5768, alloc=16344), compile(in-use=155344, alloc=174136), execution(in-use=207560, alloc=208064)
kkqctdrvTD-start on query block SEL$F5BB74E1 (#1)
kkqctdrvTD-start: :
call(in-use=5768, alloc=16344), compile(in-use=155344, alloc=174136), execution(in-use=207560, alloc=208064)
TE: Checking validity of table expansion for query block SEL$F5BB74E1 (#1)
TE: Bypassed: No partitioned table in query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=5768, alloc=16344), compile(in-use=156312, alloc=174136), execution(in-use=207560, alloc=208064)
kkqctdrvTD-end:
call(in-use=5768, alloc=16344), compile(in-use=156992, alloc=174136), execution(in-use=207560, alloc=208064)
TE: Checking validity of table expansion for query block SEL$F5BB74E1 (#1)
TE: Bypassed: No partitioned table in query block.
ST: Query in kkqstardrv:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD",SUM("T2"."N") "SUM_N" FROM "CHRIS"."T1" "T1","CHRIS"."T2" "T2" WHERE "T1"."N"="T2"."N" GROUP BY "T2"."N","T1".ROWID,"T1"."PAD","T1"."N","T1"."ID"
ST: not valid since star transformation parameter is FALSE
kkqctdrvTD-start on query block SEL$F5BB74E1 (#1)
kkqctdrvTD-start: :
call(in-use=5816, alloc=16344), compile(in-use=156992, alloc=174136), execution(in-use=207560, alloc=208064)
JF: Checking validity of join factorization for query block SEL$F5BB74E1 (#1)
JF: Bypassed: not a UNION or UNION-ALL query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=5816, alloc=16344), compile(in-use=157960, alloc=174136), execution(in-use=207560, alloc=208064)
kkqctdrvTD-end:
call(in-use=5816, alloc=16344), compile(in-use=158640, alloc=174136), execution(in-use=207560, alloc=208064)
JPPD: Considering Cost-based predicate pushdown from query block SEL$F5BB74E1 (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$F5BB74E1 (#1)
kkqctdrvTD-start: :
call(in-use=5816, alloc=16344), compile(in-use=158640, alloc=174136), execution(in-use=207560, alloc=208064)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=5816, alloc=16344), compile(in-use=159608, alloc=174136), execution(in-use=207560, alloc=208064)
kkqctdrvTD-end:
call(in-use=5816, alloc=16344), compile(in-use=160288, alloc=174136), execution(in-use=207560, alloc=208064)
JPPD: Applying transformation directives
query block SEL$1 transformed to SEL$F5BB74E1 (#1)
FPD: Considering simple filter push in query block SEL$F5BB74E1 (#1)
"T1"."N"="T2"."N"
try to generate transitive predicate from check constraints for query block SEL$F5BB74E1 (#1)
finally: "T1"."N"="T2"."N"
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD",SUM("T2"."N") "SUM_N" FROM "CHRIS"."T1" "T1","CHRIS"."T2" "T2" WHERE "T1"."N"="T2"."N" GROUP BY "T2"."N","T1".ROWID,"T1"."PAD","T1"."N","T1"."ID"
kkoqbc: optimizing query block SEL$F5BB74E1 (#1)
:
call(in-use=6056, alloc=16344), compile(in-use=161592, alloc=174136), execution(in-use=207560, alloc=208064)
kkoqbc-subheap (create addr=0x7f9f10765560)
****************
QUERY BLOCK TEXT
****************
SELECT t1.*, t2.sum_n
FROM t1, (SELECT /*+ merge */ n, sum(n) AS sum_n
FROM t2
GROUP BY n) t2
WHERE t1.n = t2.n
SELECT "T1"."ID" "ID",
"T1"."N" "N",
"T1"."PAD" "PAD",
SUM("T2"."N") "SUM_N"
FROM "CHRIS"."T1" "T1",
"CHRIS"."T2" "T2"
WHERE "T1"."N"="T2"."N"
GROUP BY "T2"."N",
"T1".ROWID,
"T1"."PAD",
"T1"."N",
"T1"."ID"
- select 목록 제거(휴리스틱 기반)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_001"."N1" "N1" FROM (SELECT "T"."N1" "N1" FROM "CHRIS"."T" "T") "from$_subquery$_001"
kkoqbc: optimizing query block SEL$2 (#0)
:
call(in-use=1672, alloc=16344), compile(in-use=65376, alloc=68488), execution(in-use=95600, alloc=97560)
kkoqbc-subheap (create addr=0x7f54075956d0)
****************
QUERY BLOCK TEXT
****************
SELECT n1, n2, n3 FROM t
(중간 생략)
****************
QUERY BLOCK TEXT
****************
SELECT n1 FROM (SELECT n1, n2, n3 FROM t)
- 조건 푸시 다운
----- Current SQL Statement for this session (sql_id=4gptcutk7ntam) -----
EXPLAIN PLAN FOR SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) WHERE id = 1
sql_text_length=86
sql=EXPLAIN PLAN FOR SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) WHERE id = 1
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | VIEW | | 2 | 130 | 4 | 00:00:01 |
| 2 | SORT UNIQUE | | 2 | 130 | 4 | 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 65 | 1 | 00:00:01 |
| 5 | INDEX UNIQUE SCAN | SYS_C0017711| 1 | | 1 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 65 | 1 | 00:00:01 |
| 7 | INDEX UNIQUE SCAN | SYS_C0017712| 1 | | 1 | 00:00:01 |
------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
5 - access("T1"."ID"=1)
7 - access("T2"."ID"=1)
----- Current SQL Statement for this session (sql_id=4gz8h8zfbgtsu) -----
EXPLAIN PLAN FOR SELECT /*+ push_pred(@"SEL$1" "T23"@"SEL$1" 1) */ * FROM t1, (SELECT * FROM t2 UNION SELECT * FROM t3) t23 WHERE t1.id = t23.id
sql_text_length=145
sql=EXPLAIN PLAN FOR SELECT /*+ push_pred(@"SEL$1" "T23"@"SEL$1" 1) */ * FROM t1, (SELECT * FROM t2 UNION SELECT * FROM t3) t23 WHERE t1.id = t23.id
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | NESTED LOOPS | | 1 | 119 | 4 | 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 1 | 65 | 2 | 00:00:01 |
| 3 | VIEW | | 1 | 54 | 2 | 00:00:01 |
| 4 | SORT UNIQUE | | 2 | 130 | 2 | 00:00:01 |
| 5 | UNION ALL PUSHED PREDICATE | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 65 | 1 | 00:00:01 |
| 7 | INDEX UNIQUE SCAN | SYS_C006498| 1 | | 1 | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 65 | 1 | 00:00:01 |
| 9 | INDEX UNIQUE SCAN | SYS_C006499| 1 | | 1 | 00:00:01 |
------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
7 - access("T2"."ID"="T1"."ID")
9 - access("T3"."ID"="T1"."ID")
- 조건 이동(휴리스틱 기반)
----- Current SQL Statement for this session (sql_id=fv2zbf6r6tw76) -----
EXPLAIN PLAN FOR
SELECT t1.pad, t2.pad
FROM (SELECT DISTINCT n, pad FROM t1 WHERE n = 1) t1,
(SELECT DISTINCT n, pad FROM t2) t2
WHERE t1.n = t2.n
sql_text_length=152
sql=EXPLAIN PLAN FOR
SELECT t1.pad, t2.pad
FROM (SELECT DISTINCT n, pad FROM t1 WHERE n = 1) t1,
(SELECT DISTINCT n, pad FROM t2) t2
WHERE t1.n = t2.n
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 7 | |
| 1 | HASH JOIN | | 1 | 130 | 7 | 00:00:01 |
| 2 | VIEW | | 1 | 65 | 3 | 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 65 | 3 | 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 65 | 2 | 00:00:01 |
| 5 | VIEW | | 1 | 65 | 3 | 00:00:01 |
| 6 | HASH UNIQUE | | 1 | 65 | 3 | 00:00:01 |
| 7 | TABLE ACCESS FULL | T2 | 1 | 65 | 2 | 00:00:01 |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T1"."N"="T2"."N")
4 - filter("N"=1)
7 - filter("N"=1)
- distinct 추가(비용 기반)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$08FE944A nbfros=2 flg=0
fro(0): flg=0 objn=148792 hint_alias="T1"@"SEL$1"
fro(1): flg=1 objn=0 hint_alias="VW_DTP_AE9E49E8"@"SEL$AE9E49E8"
DP: Transformed query
******* UNPARSED QUERY IS *******
SELECT DISTINCT "T1"."N2" "N2","VW_DTP_AE9E49E8"."ITEM_2" "N2" FROM (SELECT DISTINCT "T2"."T1_ID" "ITEM_1","T2"."N2" "ITEM_2" FROM "CHRIS"."T2" "T2") "VW_DTP_AE9E49E8","CHRIS"."T1" "T1" WHERE "T1"."ID"="VW_DTP_AE9E49E8"."ITEM_1"
FPD: Considering simple filter push in query block SEL$08FE944A (#1)
"T1"."ID"="VW_DTP_AE9E49E8"."ITEM_1"
try to generate transitive predicate from check constraints for query block SEL$08FE944A (#1)
finally: "T1"."ID"="VW_DTP_AE9E49E8"."ITEM_1"
FPD: Considering simple filter push in query block SEL$BCD4421C (#2)
??
OJE: Begin: find best directive for query block SEL$BCD4421C (#2)
OJE: End: finding best directive for query block SEL$BCD4421C (#2)
DP: Costing query block.
CBQT: Looking for cost annotations for query block SEL$BCD4421C, key = SEL$BCD4421C_00001000_2
CBQT: Could not find stored cost annotations.
kkoqbc: optimizing query block SEL$BCD4421C (#2)
:
call(in-use=16704, alloc=65656), compile(in-use=186616, alloc=190344), execution(in-use=218808, alloc=220376)
kkoqbc-subheap (create addr=0x7ff50b393ed0)
(중간 생략)
****************
QUERY BLOCK TEXT
****************
SELECT DISTINCT t1.n2, t2.n2 FROM t1, t2 WHERE t1.id = t2.t1_id
SELECT DISTINCT "T1"."N2" "N2",
"VW_DTP_AE9E49E8"."ITEM_2" "N2"
FROM (SELECT DISTINCT "T2"."T1_ID" "ITEM_1",
"T2"."N2" "ITEM_2"
FROM "CHRIS"."T2" "T2") "VW_DTP_AE9E49E8",
"CHRIS"."T1" "T1"
WHERE "T1"."ID"="VW_DTP_AE9E49E8"."ITEM_1"
- distinct 제거(휴리스틱 기반)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$1 (#1)
kkqctdrvTD-start: :
call(in-use=1576, alloc=16344), compile(in-use=132144, alloc=133560), execution(in-use=157664, alloc=158976)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=1576, alloc=16344), compile(in-use=132552, alloc=133560), execution(in-use=157664, alloc=158976)
kkqctdrvTD-end:
call(in-use=1576, alloc=16344), compile(in-use=132672, alloc=133560), execution(in-use=157664, alloc=158976)
JPPD: Applying transformation directives
query block SEL$1 (#1) unchanged
FPD: Considering simple filter push in query block SEL$1 (#1)
??
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."ID" "ID","T"."N" "N" FROM "CHRIS"."T" "T"
kkoqbc: optimizing query block SEL$1 (#1)
:
call(in-use=1576, alloc=16344), compile(in-use=133664, alloc=137576), execution(in-use=161720, alloc=163032)
kkoqbc-subheap (create addr=0x7ff124d356d0)
****************
QUERY BLOCK TEXT
****************
SELECT DISTINCT id, n FROM t
- group-by 추가(비용 기반)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1928AB68 nbfros=2 flg=0
fro(0): flg=0 objn=148796 hint_alias="T1"@"SEL$1"
fro(1): flg=1 objn=0 hint_alias="VW_GBF_1"@"SEL$AE9E49E8"
GBP: Transformed query
******* UNPARSED QUERY IS *******
SELECT "T1"."N2" "N2","VW_GBF_1"."ITEM_3" "N2",SUM("VW_GBF_1"."ITEM_2") "COUNT(*)" FROM (SELECT "T2"."T1_ID" "ITEM_1",COUNT(*) "ITEM_2","T2"."N2" "ITEM_3" FROM "CHRIS"."T2" "T2" GROUP BY "T2"."T1_ID","T2"."N2") "VW_GBF_1","CHRIS"."T1" "T1" WHERE "T1"."ID"="VW_GBF_1"."ITEM_1" GROUP BY "T1"."N2","VW_GBF_1"."ITEM_3"
Column (#2): T1_ID(
AvgLen: 3 NDV: 100 Nulls: 0 Density: 0.010000 Min: 1 Max: 100
Column (#4): N2(
AvgLen: 4 NDV: 4200 Nulls: 0 Density: 0.000238 Min: 0 Max: 4199
FPD: Considering simple filter push in query block SEL$1928AB68 (#1)
"T1"."ID"="VW_GBF_1"."ITEM_1"
try to generate transitive predicate from check constraints for query block SEL$1928AB68 (#1)
finally: "T1"."ID"="VW_GBF_1"."ITEM_1"
FPD: Considering simple filter push in query block SEL$BCD4421C (#2)
??
OJE: Begin: find best directive for query block SEL$BCD4421C (#2)
OJE: End: finding best directive for query block SEL$BCD4421C (#2)
GBP: Costing transformed query.
CBQT: Looking for cost annotations for query block SEL$BCD4421C, key = SEL$BCD4421C_00001000_2
CBQT: Could not find stored cost annotations.
kkoqbc: optimizing query block SEL$BCD4421C (#2)
:
call(in-use=18544, alloc=65656), compile(in-use=197384, alloc=214536), execution(in-use=245240, alloc=248864)
kkoqbc-subheap (create addr=0x7f0ec118fb58)
(중간 생략)
****************
QUERY BLOCK TEXT
****************
SELECT t1.n2, t2.n2, count(*) FROM t1, t2 WHERE t1.id = t2.t1_id GROUP BY t1.n2, t2.n2
SELECT "T1"."N2" "N2",
"VW_GBF_1"."ITEM_3" "N2",
SUM("VW_GBF_1"."ITEM_2") "COUNT(*)"
FROM (SELECT "T2"."T1_ID" "ITEM_1",
COUNT(*) "ITEM_2",
"T2"."N2" "ITEM_3"
FROM "CHRIS"."T2" "T2"
GROUP BY "T2"."T1_ID",
"T2"."N2") "VW_GBF_1",
"CHRIS"."T1" "T1"
WHERE "T1"."ID"="VW_GBF_1"."ITEM_1"
GROUP BY "T1"."N2",
"VW_GBF_1"."ITEM_3"
- order-by 제거(휴리스틱 기반)
****************************************
Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$51F12574 (#1)
GBP: Checking validity of group-by placement for query block SEL$51F12574 (#1)
GBP: Bypassed: Query has invalid constructs.
DP: Checking validity of distinct placement for query block SEL$51F12574 (#1)
DP: Bypassed: Query has invalid constructs.
kkqctdrvTD-cleanup: transform(in-use=1592, alloc=4056) :
call(in-use=2896, alloc=16344), compile(in-use=138984, alloc=153744), execution(in-use=182240, alloc=183416)
kkqctdrvTD-end:
call(in-use=2896, alloc=16344), compile(in-use=135184, alloc=153744), execution(in-use=182240, alloc=183416)
kkqctdrvTD-start on query block SEL$51F12574 (#1)
kkqctdrvTD-start: :
call(in-use=2896, alloc=16344), compile(in-use=135184, alloc=153744), execution(in-use=182240, alloc=183416)
TE: Checking validity of table expansion for query block SEL$51F12574 (#1)
TE: Bypassed: No partitioned table in query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=2896, alloc=16344), compile(in-use=135792, alloc=153744), execution(in-use=182240, alloc=183416)
kkqctdrvTD-end:
call(in-use=2896, alloc=16344), compile(in-use=136112, alloc=153744), execution(in-use=182240, alloc=183416)
TE: Checking validity of table expansion for query block SEL$51F12574 (#1)
TE: Bypassed: No partitioned table in query block.
ST: Query in kkqstardrv:******* UNPARSED QUERY IS *******
SELECT "T"."N2" "N2",COUNT(*) "COUNT(*)" FROM "CHRIS"."T" "T" GROUP BY "T"."N2"
ST: not valid since star transformation parameter is FALSE
kkqctdrvTD-start on query block SEL$51F12574 (#1)
kkqctdrvTD-start: :
call(in-use=2896, alloc=16344), compile(in-use=136112, alloc=153744), execution(in-use=182240, alloc=183416)
JF: Checking validity of join factorization for query block SEL$51F12574 (#1)
JF: Bypassed: not a UNION or UNION-ALL query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=2896, alloc=16344), compile(in-use=136720, alloc=153744), execution(in-use=182240, alloc=183416)
kkqctdrvTD-end:
call(in-use=2896, alloc=16344), compile(in-use=137040, alloc=153744), execution(in-use=182240, alloc=183416)
JPPD: Considering Cost-based predicate pushdown from query block SEL$51F12574 (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$51F12574 (#1)
kkqctdrvTD-start: :
call(in-use=2896, alloc=16344), compile(in-use=137040, alloc=153744), execution(in-use=182240, alloc=183416)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=2896, alloc=16344), compile(in-use=137648, alloc=153744), execution(in-use=182240, alloc=183416)
kkqctdrvTD-end:
call(in-use=2896, alloc=16344), compile(in-use=137968, alloc=153744), execution(in-use=182240, alloc=183416)
JPPD: Applying transformation directives
query block SEL$51F12574 (#1) unchanged
FPD: Considering simple filter push in query block SEL$51F12574 (#1)
??
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T"."N2" "N2",COUNT(*) "COUNT(*)" FROM "CHRIS"."T" "T" GROUP BY "T"."N2"
kkoqbc: optimizing query block SEL$51F12574 (#1)
:
call(in-use=2896, alloc=16344), compile(in-use=138936, alloc=153744), execution(in-use=182240, alloc=183416)
kkoqbc-subheap (create addr=0x7f4f408e56d0)
****************
QUERY BLOCK TEXT
****************
SELECT n2, count(*) FROM (SELECT n1, n2 FROM t ORDER BY n1) GROUP BY n2
- 서브쿼리 unnesting(휴리스틱, 비용 기반)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND "T2"."PAD" IS NOT NULL
JE: cfro: T2 objn:148971 col#:1 dfro:T1 dcol#:1
JE: cfro: T2 objn:148971 col#:1 dfro:T1 dcol#:1
JE: cfro: T1 objn:148973 col#:1 dfro:T2 dcol#:1
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND "T2"."PAD" IS NOT NULL
Query block SEL$5DA710D3 (#1) unchanged
PM: Considering predicate move-around in query block SEL$5DA710D3 (#1)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
kkqctdrvTD-start: :
call(in-use=3424, alloc=16344), compile(in-use=138376, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=3424, alloc=16344), compile(in-use=139008, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-end:
call(in-use=3424, alloc=16344), compile(in-use=139328, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
kkqctdrvTD-start: :
call(in-use=3424, alloc=16344), compile(in-use=139328, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=3424, alloc=16344), compile(in-use=139936, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-end:
call(in-use=3424, alloc=16344), compile(in-use=140256, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
kkqctdrvTD-start: :
call(in-use=3424, alloc=16344), compile(in-use=140256, alloc=173928), execution(in-use=204248, alloc=207856)
TE: Checking validity of table expansion for query block SEL$5DA710D3 (#1)
TE: Bypassed: No partitioned table in query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=3424, alloc=16344), compile(in-use=140864, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-end:
call(in-use=3424, alloc=16344), compile(in-use=141184, alloc=173928), execution(in-use=204248, alloc=207856)
TE: Checking validity of table expansion for query block SEL$5DA710D3 (#1)
TE: Bypassed: No partitioned table in query block.
ST: Query in kkqstardrv:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND "T2"."PAD" IS NOT NULL
ST: not valid since star transformation parameter is FALSE
kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
kkqctdrvTD-start: :
call(in-use=3520, alloc=16344), compile(in-use=141184, alloc=173928), execution(in-use=204248, alloc=207856)
JF: Checking validity of join factorization for query block SEL$5DA710D3 (#1)
JF: Bypassed: not a UNION or UNION-ALL query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=3520, alloc=16344), compile(in-use=141808, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-end:
call(in-use=3520, alloc=16344), compile(in-use=142128, alloc=173928), execution(in-use=204248, alloc=207856)
JPPD: Considering Cost-based predicate pushdown from query block SEL$5DA710D3 (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
kkqctdrvTD-start: :
call(in-use=3520, alloc=16344), compile(in-use=142128, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=3520, alloc=16344), compile(in-use=142736, alloc=173928), execution(in-use=204248, alloc=207856)
kkqctdrvTD-end:
call(in-use=3520, alloc=16344), compile(in-use=143056, alloc=173928), execution(in-use=204248, alloc=207856)
JPPD: Applying transformation directives
query block SEL$1 transformed to SEL$5DA710D3 (#1)
FPD: Considering simple filter push in query block SEL$5DA710D3 (#1)
"T2"."ID"="T1"."ID" AND "T2"."PAD" IS NOT NULL
try to generate transitive predicate from check constraints for query block SEL$5DA710D3 (#1)
finally: "T2"."ID"="T1"."ID" AND "T2"."PAD" IS NOT NULL
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND "T2"."PAD" IS NOT NULL
kkoqbc: optimizing query block SEL$5DA710D3 (#1)
:
call(in-use=3808, alloc=16344), compile(in-use=144360, alloc=173928), execution(in-use=204248, alloc=207856)
kkoqbc-subheap (create addr=0x7f1f25745618)
****************
QUERY BLOCK TEXT
****************
SELECT *
FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id AND t2.pad IS NOT NULL)
SELECT "T1"."ID" "ID",
"T1"."N" "N",
"T1"."PAD" "PAD"
FROM "CHRIS"."T2" "T2",
"CHRIS"."T1" "T1"
WHERE "T2"."ID"="T1"."ID"
AND "T2"."PAD" IS NOT NULL
- 서브쿼리 통합(휴리스틱 기반)
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$ACD206C8 (#2)
SU: Passed validity checks.
SU: Transforming EXISTS subquery to a join.
Registered qb: SEL$82F4A621 0x715d9c90 (SUBQUERY UNNEST SEL$1; SEL$ACD206C8)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$82F4A621 nbfros=2 flg=0
fro(0): flg=0 objn=148979 hint_alias="T1"@"SEL$1"
fro(1): flg=0 objn=148981 hint_alias="T2"@"SEL$2"
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in query block SEL$82F4A621 (#1) that are valid to merge.
OJE: Begin: find best directive for query block SEL$82F4A621 (#1)
OJE: End: finding best directive for query block SEL$82F4A621 (#1)
kkqctdrvTD-cleanup: transform(in-use=5304, alloc=8136) :
call(in-use=3784, alloc=16344), compile(in-use=151256, alloc=177944), execution(in-use=213368, alloc=215968)
kkqctdrvTD-end:
call(in-use=3784, alloc=16344), compile(in-use=143496, alloc=177944), execution(in-use=213368, alloc=215968)
SU: Transforming EXISTS subquery to a join.
SJC: Considering set-join conversion in query block SEL$82F4A621 (#1)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
OJE: Begin: find best directive for query block SEL$82F4A621 (#1)
OJE: End: finding best directive for query block SEL$82F4A621 (#1)
JE: Considering Join Elimination on query block SEL$82F4A621 (#1)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
JE: cfro: T2 objn:148979 col#:1 dfro:T1 dcol#:1
JE: cfro: T2 objn:148979 col#:1 dfro:T1 dcol#:1
JE: cfro: T1 objn:148981 col#:1 dfro:T2 dcol#:1
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
Query block SEL$82F4A621 (#1) unchanged
PM: Considering predicate move-around in query block SEL$82F4A621 (#1)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
call(in-use=4760, alloc=16344), compile(in-use=144368, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=4760, alloc=16344), compile(in-use=145160, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-end:
call(in-use=4760, alloc=16344), compile(in-use=145640, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
call(in-use=4760, alloc=16344), compile(in-use=145640, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=4760, alloc=16344), compile(in-use=146408, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-end:
call(in-use=4760, alloc=16344), compile(in-use=146888, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
call(in-use=4760, alloc=16344), compile(in-use=146888, alloc=177944), execution(in-use=213408, alloc=215968)
TE: Checking validity of table expansion for query block SEL$82F4A621 (#1)
TE: Bypassed: No partitioned table in query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=4760, alloc=16344), compile(in-use=147680, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-end:
call(in-use=4760, alloc=16344), compile(in-use=148160, alloc=177944), execution(in-use=213408, alloc=215968)
TE: Checking validity of table expansion for query block SEL$82F4A621 (#1)
TE: Bypassed: No partitioned table in query block.
ST: Query in kkqstardrv:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
ST: not valid since star transformation parameter is FALSE
kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
call(in-use=4904, alloc=16344), compile(in-use=148160, alloc=177944), execution(in-use=213408, alloc=215968)
JF: Checking validity of join factorization for query block SEL$82F4A621 (#1)
JF: Bypassed: not a UNION or UNION-ALL query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=4904, alloc=16344), compile(in-use=148928, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-end:
call(in-use=4904, alloc=16344), compile(in-use=149408, alloc=177944), execution(in-use=213408, alloc=215968)
JPPD: Considering Cost-based predicate pushdown from query block SEL$82F4A621 (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
call(in-use=4904, alloc=16344), compile(in-use=149408, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=4904, alloc=16344), compile(in-use=150176, alloc=177944), execution(in-use=213408, alloc=215968)
kkqctdrvTD-end:
call(in-use=4904, alloc=16344), compile(in-use=150656, alloc=177944), execution(in-use=213408, alloc=215968)
JPPD: Applying transformation directives
query block SEL$1 transformed to SEL$82F4A621 (#1)
FPD: Considering simple filter push in query block SEL$82F4A621 (#1)
"T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
try to generate transitive predicate from check constraints for query block SEL$82F4A621 (#1)
finally: "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
kkoqbc: optimizing query block SEL$82F4A621 (#1)
:
call(in-use=5240, alloc=16344), compile(in-use=151960, alloc=177944), execution(in-use=213408, alloc=215968)
kkoqbc-subheap (create addr=0x7f7752695618)
****************
QUERY BLOCK TEXT
****************
SELECT *
FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.n > 10 AND t2.id = t1.id)
OR EXISTS (SELECT 1 FROM t2 WHERE t2.n < 100 AND t2.id = t1.id)
SELECT "T1"."ID" "ID",
"T1"."N" "N",
"T1"."PAD" "PAD"
FROM "CHRIS"."T2" "T2",
"CHRIS"."T1" "T1"
WHERE "T2"."ID"="T1"."ID"
AND ("T2"."N">10 OR "T2"."N"<100)
- 윈도우 함수를 사용한 서브쿼리 제거(휴리스틱 기반)
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$35430991 (#1)
JPPD: Checking validity of push-down from query block SEL$35430991 (#1) to query block SEL$4F4DF0AE (#2)
Check Basic Validity for Non-Union View for query block SEL$4F4DF0AE (#2)
JPPD: JPPD bypassed: No valid join condition found.
JPPD: No valid views found to push predicate into.
kkqctdrvTD-cleanup: transform(in-use=7072, alloc=8136) :
call(in-use=5768, alloc=16344), compile(in-use=190008, alloc=218312), execution(in-use=256232, alloc=256736)
kkqctdrvTD-end:
call(in-use=5768, alloc=16344), compile(in-use=182608, alloc=218312), execution(in-use=256232, alloc=256736)
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$35430991 (#1)
JPPD: No valid views found to push predicate into.
query block SEL$1 transformed to SEL$35430991 (#1)
FPD: Considering simple filter push in query block SEL$35430991 (#1)
"VW_WIF_1"."VW_COL_5" IS NOT NULL
try to generate transitive predicate from check constraints for query block SEL$35430991 (#1)
finally: "VW_WIF_1"."VW_COL_5" IS NOT NULL
FPD: Considering simple filter push in query block SEL$4F4DF0AE (#2)
"T1"."ID"="T2"."T1_ID"
try to generate transitive predicate from check constraints for query block SEL$4F4DF0AE (#2)
finally: "T1"."ID"="T2"."T1_ID"
OJE: Begin: find best directive for query block SEL$4F4DF0AE (#2)
OJE: End: finding best directive for query block SEL$4F4DF0AE (#2)
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VW_WIF_1"."ITEM_1" "ID","VW_WIF_1"."ITEM_2" "N","VW_WIF_1"."ITEM_3" "ID","VW_WIF_1"."ITEM_4" "N" FROM (SELECT "T1"."ID" "ITEM_1","T1"."N" "ITEM_2","T2"."ID" "ITEM_3","T2"."N" "ITEM_4",CASE "T2"."N" WHEN MAX("T2"."N") OVER ( PARTITION BY "T2"."T1_ID") THEN "T2".ROWID END "VW_COL_5" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T1"."ID"="T2"."T1_ID") "VW_WIF_1" WHERE "VW_WIF_1"."VW_COL_5" IS NOT NULL
kkoqbc: optimizing query block SEL$4F4DF0AE (#2)
:
call(in-use=6056, alloc=32712), compile(in-use=185008, alloc=218312), execution(in-use=256392, alloc=256736)
kkoqbc-subheap (create addr=0x7f1deadf5618)
(중간 생략)
kkoqbc-subheap (create addr=0x7f1deadf5618)
****************
QUERY BLOCK TEXT
****************
SELECT t1.id, t1.n, t2.id, t2.n
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t2.n = (SELECT max(n)
FROM t2
WHERE t2.t1_id = t1.id)
SELECT "VW_WIF_1"."ITEM_1" "ID",
"VW_WIF_1"."ITEM_2" "N",
"VW_WIF_1"."ITEM_3" "ID",
"VW_WIF_1"."ITEM_4" "N"
FROM (SELECT "T1"."ID" "ITEM_1",
"T1"."N" "ITEM_2",
"T2"."ID" "ITEM_3",
"T2"."N" "ITEM_4",
CASE "T2"."N" WHEN MAX("T2"."N") OVER ( PARTITION BY "T2"."T1_ID") THEN "T2".ROWID
END "VW_COL_5"
FROM "CHRIS"."T2" "T2",
"CHRIS"."T1" "T1"
WHERE "T1"."ID"="T2"."T1_ID") "VW_WIF_1"
WHERE "VW_WIF_1"."VW_COL_5" IS NOT NULL
- 조인 제거
SQL> CREATE TABLE t1 (
2 id NUMBER NOT NULL,
3 n NUMBER,
4 pad VARCHAR2(4000),
5 CONSTRAINT t1_pk PRIMARY KEY(id)
6 )
7 PCTFREE 99 PCTUSED 1;
00:00:00.03
SQL>
SQL> CREATE TABLE t2 (
2 id NUMBER NOT NULL,
3 t1_id NUMBER NOT NULL,
4 n NUMBER,
5 pad VARCHAR2(4000),
6 CONSTRAINT t2_pk PRIMARY KEY(id),
7 CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1
8 )
9 PCTFREE 99 PCTUSED 1;
SQL>
SQL> CREATE VIEW v AS
2 SELECT t1.id AS t1_id, t1.n AS t1_n, t2.id AS t2_id, t2.n AS t2_n
3 FROM t1, t2
4 WHERE t1.id = t2.t1_id;
경 과: 00:00:00.01
SQL>
SQL> EXPLAIN PLAN FOR SELECT t2_id, t2_n FROM v;
경 과: 00:00:00.06
SQL>
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 800 | 30 (0)|
| 1 | TABLE ACCESS FULL| T2 | 100 | 800 | 30 (0)|
---------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
- 조인 factorization
SQL> SELECT /*+ FACTORIZE_JOIN(@"SET$1") */ *
2 FROM t1, t2
3 WHERE t1.id = t2.id AND t2.id < 10
4 UNION ALL
5 SELECT *
6 FROM t1, t2
7 WHERE t1.id = t2.id AND t2.id > 990;
SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ FACTORIZE_JOIN(@"SET$1") */ * FROM t1, t2 WHERE t1.id =
t2.id AND t2.id < 10 UNION ALL SELECT * FROM t1, t2 WHERE t1.id = t2.id
AND t2.id > 990
Plan hash value: 1282049862
---------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | VIEW | VW_JF_SET$3DF3FC68 |
| 3 | UNION-ALL | |
| 4 | TABLE ACCESS FULL| T2 |
| 5 | TABLE ACCESS FULL| T2 |
| 6 | TABLE ACCESS FULL | T1 |
---------------------------------------------------
SQL> SELECT /*+ NO_FACTORIZE_JOIN(@"SET$1") */ *
2 FROM t1, t2
3 WHERE t1.id = t2.id AND t2.id < 10
4 UNION ALL
5 SELECT *
6 FROM t1, t2
7 WHERE t1.id = t2.id AND t2.id > 990;
SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /*+ NO_FACTORIZE_JOIN(@"SET$1") */ * FROM t1, t2 WHERE t1.id =
t2.id AND t2.id < 10 UNION ALL SELECT * FROM t1, t2 WHERE t1.id = t2.id
AND t2.id > 990
Plan hash value: 4147288133
------------------------------------
| Id | Operation | Name |
------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | UNION-ALL | |
| 2 | HASH JOIN | |
| 3 | TABLE ACCESS FULL| T1 |
| 4 | TABLE ACCESS FULL| T2 |
| 5 | HASH JOIN | |
| 6 | TABLE ACCESS FULL| T1 |
| 7 | TABLE ACCESS FULL| T2 |
------------------------------------
- outer 조인을 inner 조인으로
KSDBA@DCBIS[kdcbdb1a]> EXPLAIN PLAN FOR
2 SELECT *
3 FROM t1, t2
4 WHERE t1.id = t2.t1_id(+)
5 AND t2.id IS NOT NULL;
KSDBA@DCBIS[kdcbdb1a]>
KSDBA@DCBIS[kdcbdb1a]> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 11900 | 36 (3)|
|* 1 | HASH JOIN | | 100 | 11900 | 36 (3)|
| 2 | TABLE ACCESS FULL| T1 | 10 | 570 | 5 (0)|
| 3 | TABLE ACCESS FULL| T2 | 100 | 6200 | 30 (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- 'PLAN_TABLE' is old version
KSDBA@DCBIS[kdcbdb1a]> EXPLAIN PLAN FOR
2 SELECT /*+ no_outer_join_to_inner */ *
3 FROM t1, t2
4 WHERE t1.id = t2.t1_id(+)
5 AND t2.id IS NOT NULL;
KSDBA@DCBIS[kdcbdb1a]>
KSDBA@DCBIS[kdcbdb1a]> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 11900 | 36 (3)|
|* 1 | FILTER | | | | |
|* 2 | HASH JOIN OUTER | | 100 | 11900 | 36 (3)|
| 3 | TABLE ACCESS FULL| T1 | 10 | 570 | 5 (0)|
| 4 | TABLE ACCESS FULL| T2 | 100 | 6200 | 30 (0)|
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T2"."ID" IS NOT NULL)
2 - access("T1"."ID"="T2"."T1_ID"(+))
Note
-----
- 'PLAN_TABLE' is old version
- full outer 조인 변환(11.1버전 전까지)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$5 (#1)
kkqctdrvTD-start: :
call(in-use=18368, alloc=32712), compile(in-use=189960, alloc=222328), execution(in-use=281248, alloc=285128)
Check Basic Validity for Non-Union View for query block SET$1 (#2)
JPPD: JPPD bypassed: View is a set query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=18368, alloc=32712), compile(in-use=191888, alloc=222328), execution(in-use=281248, alloc=285128)
kkqctdrvTD-end:
call(in-use=18368, alloc=32712), compile(in-use=193536, alloc=222328), execution(in-use=281248, alloc=285128)
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$5 (#1)
JPPD: No valid views found to push predicate into.
query block SEL$5 (#1) unchanged
FPD: Considering simple filter push in query block SEL$5 (#1)
??
FPD: Considering simple filter push in query block SEL$4CF44C19 (#3)
"T2"."T1_ID" IS NULL
try to generate transitive predicate from check constraints for query block SEL$4CF44C19 (#3)
finally: "T2"."T1_ID" IS NULL
FPD: Considering simple filter push in query block SEL$58A6D7F6 (#4)
"T1"."ID"="T2"."T1_ID"(+)
try to generate transitive predicate from check constraints for query block SEL$58A6D7F6 (#4)
finally: "T1"."ID"="T2"."T1_ID"(+)
OJE: Begin: find best directive for query block SEL$4CF44C19 (#3)
OJE: End: finding best directive for query block SEL$4CF44C19 (#3)
OJE: Begin: find best directive for query block SEL$58A6D7F6 (#4)
OJE: Considering outer-join elimination on query block SEL$58A6D7F6 (#4)
OJE: considering predicate"T1"."ID"="T2"."T1_ID"(+)
rejected
OJE: outer-join not eliminated
OJE: End: finding best directive for query block SEL$58A6D7F6 (#4)
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ NO_NATIVE_FULL_OUTER_JOIN */ "from$_subquery$_003"."QCSJ_C000000000300000" "ID","from$_subquery$_003"."QCSJ_C000000000300002" "N","from$_subquery$_003"."QCSJ_C000000000300004" "PAD","from$_subquery$_003"."QCSJ_C000000000300001" "ID","from$_subquery$_003"."T1_ID" "T1_ID","from$_subquery$_003"."T1_ID_NN" "T1_ID_NN","from$_subquery$_003"."QCSJ_C000000000300003" "N","from$_subquery$_003"."QCSJ_C000000000300005" "PAD" FROM ( (SELECT "T1"."ID" "QCSJ_C000000000300000","T1"."N" "QCSJ_C000000000300002","T1"."PAD" "QCSJ_C000000000300004","T2"."ID" "ID","T2"."T1_ID" "T1_ID","T2"."T1_ID_NN" "T1_ID_NN","T2"."N" "N","T2"."PAD" "PAD" FROM "CHRIS"."T1" "T1","CHRIS"."T2" "T2" WHERE "T1"."ID"="T2"."T1_ID"(+)) UNION ALL (SELECT NULL,NULL,NULL,"T2"."ID" "ID","T2"."T1_ID" "T1_ID","T2"."T1_ID_NN" "T1_ID_NN","T2"."N" "N","T2"."PAD" "PAD" FROM "CHRIS"."T2" "T2" WHERE "T2"."T1_ID" IS NULL)) "from$_subquery$_003"
kkoqbc: optimizing query block SEL$58A6D7F6 (#4)
(중간 생략)
kkoqbc-subheap (create addr=0x2a9733a658)
****************
QUERY BLOCK TEXT
****************
SELECT /*+ no_native_full_outer_join */ *
FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id
SELECT /*+ NO_NATIVE_FULL_OUTER_JOIN */
"from$_subquery$_003"."QCSJ_C000000000300000" "ID",
"from$_subquery$_003"."QCSJ_C000000000300002" "N",
"from$_subquery$_003"."QCSJ_C000000000300004" "PAD",
"from$_subquery$_003"."QCSJ_C000000000300001" "ID",
"from$_subquery$_003"."T1_ID" "T1_ID",
"from$_subquery$_003"."T1_ID_NN" "T1_ID_NN",
"from$_subquery$_003"."QCSJ_C000000000300003" "N",
"from$_subquery$_003"."QCSJ_C000000000300005" "PAD"
FROM ( (SELECT "T1"."ID" "QCSJ_C000000000300000",
"T1"."N" "QCSJ_C000000000300002",
"T1"."PAD" "QCSJ_C000000000300004",
"T2"."ID" "ID",
"T2"."T1_ID" "T1_ID",
"T2"."T1_ID_NN" "T1_ID_NN",
"T2"."N" "N",
"T2"."PAD" "PAD"
FROM "CHRIS"."T1" "T1",
"CHRIS"."T2" "T2"
WHERE "T1"."ID"="T2"."T1_ID"(+))
UNION ALL
(SELECT NULL,
NULL,
NULL,
"T2"."ID" "ID",
"T2"."T1_ID" "T1_ID",
"T2"."T1_ID_NN" "T1_ID_NN",
"T2"."N" "N",
"T2"."PAD" "PAD"
FROM "CHRIS"."T2" "T2"
WHERE "T2"."T1_ID" IS NULL)) "from$_subquery$_003"
----- Current SQL Statement for this session (sql_id=95zs2444p67ha) -----
EXPLAIN PLAN FOR
SELECT /*+ no_native_full_outer_join */ *
FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id
sql_text_length=106
sql=EXPLAIN PLAN FOR
SELECT /*+ no_native_full_outer_join */ *
FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 7 | |
| 1 | VIEW | | 2 | 364 | 7 | 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | HASH JOIN OUTER | | 1 | 182 | 5 | 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 1 | 78 | 2 | 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 1 | 104 | 2 | 00:00:01 |
| 6 | TABLE ACCESS FULL | T2 | 1 | 104 | 2 | 00:00:01 |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("T1"."ID"="T2"."T1_ID")
6 - filter("T2"."T1_ID" IS NULL)
----- Current SQL Statement for this session (sql_id=a3gagumsws0j9) -----
EXPLAIN PLAN FOR
SELECT *
FROM t1 FULL OUTER JOIN t2 ON t1.n = t2.n
sql_text_length=68
sql=EXPLAIN PLAN FOR
SELECT *
FROM t1 FULL OUTER JOIN t2 ON t1.n = t2.n
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 5 | |
| 1 | VIEW | VW_FOJ_0| 1 | 182 | 5 | 00:00:01 |
| 2 | HASH JOIN FULL OUTER | | 1 | 182 | 5 | 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | | | 2 | 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 1 | 104 | 2 | 00:00:01 |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T1"."N"="T2"."N")
- 테이블 expansion
----- Current SQL Statement for this session (sql_id=91tcpq99r3wx5) -----
EXPLAIN PLAN FOR SELECT * FROM t WHERE n = 8
sql_text_length=45
sql=EXPLAIN PLAN FOR SELECT * FROM t WHERE n = 8
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
--------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
--------------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 205 | | | |
| 1 | VIEW | VW_TE_2 | 76 | 151K | 205 | 00:00:04 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE OR | | 35 | 4130 | 1 | 00:00:01 | KEY(OR)| KEY(OR)|
| 4 | TABLE ACCESS BY LOCAL INDEX ROWID | T | 35 | 4130 | 1 | 00:00:01 | KEY(OR)| KEY(OR)|
| 5 | INDEX RANGE SCAN | I | 41 | | 1 | 00:00:01 | KEY(OR)| KEY(OR)|
| 6 | PARTITION RANGE ITERATOR | | 41 | 4838 | 204 | 00:00:04 | 2 | 3 |
| 7 | TABLE ACCESS FULL | T | 41 | 4838 | 204 | 00:00:04 | 2 | 3 |
--------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
4 - filter(("T"."D"<TO_DATE(' 2012-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') OR ("T"."D">=TO_DATE(' 2012-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."D"<TO_DATE(' 2013-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))))
5 - access("N"=8)
7 - filter("N"=8)
----- Current SQL Statement for this session (sql_id=91tcpq99r3wx5) -----
EXPLAIN PLAN FOR SELECT * FROM t WHERE n = 8
sql_text_length=45
sql=EXPLAIN PLAN FOR SELECT * FROM t WHERE n = 8
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
---------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
---------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 2 | | | |
| 1 | PARTITION RANGE ALL | | 1 | 2037 | 2 | 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS FULL | T | 1 | 2037 | 2 | 00:00:01 | 1 | 4 |
---------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
2 - filter("N"=8)
- 집합에서 조인으로 변환
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
query block SET$1 transformed to SEL$02B15F54 (#0)
FPD: Considering simple filter push in query block SEL$02B15F54 (#0)
"T1"."ID"="T2"."ID" AND "T1"."N"="T2"."N" AND "T1"."PAD"="T2"."PAD" AND "T1"."N">500 AND "T2"."PAD" LIKE 'A%'
try to generate transitive predicate from check constraints for query block SEL$02B15F54 (#0)
finally: "T1"."ID"="T2"."ID" AND "T1"."N"="T2"."N" AND "T1"."PAD"="T2"."PAD" AND "T1"."N">500 AND "T2"."PAD" LIKE 'A%' AND "T2"."N">500 AND "T1"."PAD" LIKE 'A%'
FPD: transitive predicates are generated in query block SEL$02B15F54 (#0)
"T1"."ID"="T2"."ID" AND "T1"."N"="T2"."N" AND "T1"."PAD"="T2"."PAD" AND "T1"."N">500 AND "T2"."PAD" LIKE 'A%' AND "T2"."N">500 AND "T1"."PAD" LIKE 'A%'
apadrv-start sqlid=2629578423929517488
:
call(in-use=5064, alloc=16344), compile(in-use=79048, alloc=80536), execution(in-use=115616, alloc=117840)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT DISTINCT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T1"."ID"="T2"."ID" AND "T1"."N"="T2"."N" AND "T1"."PAD"="T2"."PAD" AND "T1"."N">500 AND "T2"."PAD" LIKE 'A%' AND "T2"."N">500 AND "T1"."PAD" LIKE 'A%'
kkoqbc: optimizing query block SEL$02B15F54 (#0)
:
call(in-use=5408, alloc=16344), compile(in-use=80320, alloc=80536), execution(in-use=115616, alloc=117840)
kkoqbc-subheap (create addr=0x7f70ce435618)
****************
QUERY BLOCK TEXT
****************
SELECT /*+ SET_TO_JOIN(@SET$1) */ *
FROM t1
WHERE n > 500
INTERSECT
SELECT *
FROM t2
WHERE t2.pad LIKE 'A%'
SELECT DISTINCT "T1"."ID" "ID",
"T1"."N" "N",
"T1"."PAD" "PAD"
FROM "CHRIS"."T2" "T2",
"CHRIS"."T1" "T1"
WHERE "T1"."ID"="T2"."ID"
AND "T1"."N"="T2"."N"
AND "T1"."PAD"="T2"."PAD"
AND "T1"."N">500 AND "T2"."PAD" LIKE 'A%'
AND "T2"."N">500 AND "T1"."PAD" LIKE 'A%'
----- Current SQL Statement for this session (sql_id=28zj3nmxpmddh) -----
EXPLAIN PLAN FOR
SELECT /*+ SET_TO_JOIN(@SET$1) */ *
FROM t1
WHERE n > 500
INTERSECT
SELECT *
FROM t2
WHERE t2.pad LIKE 'A%'
sql_text_length=125
sql=EXPLAIN PLAN FOR
SELECT /*+ SET_TO_JOIN(@SET$1) */ *
FROM t1
WHERE n > 500
INTERSECT
SELECT *
FROM t2
WHERE t2.pad LIKE 'A%'
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 4 | |
| 1 | HASH UNIQUE | | 1 | 156 | 4 | 00:00:01 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 156 | 3 | 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 1 | 78 | 2 | 00:00:01 |
| 5 | INDEX UNIQUE SCAN | T1_PK | 1 | | 0 | |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 78 | 1 | 00:00:01 |
-------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - filter(("T2"."N">500 AND "T2"."PAD" LIKE 'A%'))
5 - access("T1"."ID"="T2"."ID")
6 - filter(("N">500 AND "T1"."PAD" LIKE 'A%' AND "T1"."N"="T2"."N" AND "T1"."PAD"="T2"."PAD"))