OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.11.0.0
방법 : sqlp 실기튜닝실습 17-1 PUSH PREDICATE 문제 devDo
devDo님의 블로그를 참고하여 테이블 생성 후 실습함
sqlp 실기 준비 스터디를 진행하면서 나온 스터디원의 답안과 모법답안을 작성함
* 문제 쿼리는 문제와 동일한 실행계획을 표현하기위해 힌트를 사용해서 고정함
* 쿼리 실행 유저는 IMSI 유저를 사용함(다른 유저명으로 생성해서 사용해도 문제없음)
* 본문 샘플데이터의 분포, DB 버전이 문제와 동일하지 않기 때문에 문제와 답안의 성능(결과)가 devDo님 결과와 다를수있음
문제) 비효율을 찾아 튜닝을 하시오.
전제조건
[고객(emp_p)] 테이블 70,000건, 고객코드(empcd) 'z0005' 은 0.5% 분포도를 가짐
[주문(order_p)] 테이블은 1,500,000건
인덱스구성
|
1
2
|
idx_emp_p_01 : empcd
idx_order_p_01 empid + orderdt
|
SQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SQL>
select /*+ gather_plan_statistics leading(a b) use_nl(b) index(b emp_p_pk) no_merge(a) */
a.empid, cnt 총주문횟수, price 총주문금액, qty 총주문량, dt 최종주문일자, b.empnm, b.addr, b.phone
from
(
select /*+ full(order_p) */ empid, count(*) cnt, sum(orderpric) price, sum(orderqty) qty, max(orderdt) dt
from order_p
where orderdt between '20140901' and '20140903'
group by empid
) a, emp_p b
where a.empid = b.empid
and b.empcd = 'z0005';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2002 (100)| | 3 |00:00:00.08 | 6605 | 5169 | | | |
| 1 | NESTED LOOPS | | 1 | 1 | 94 | 2002 (2)| 00:00:01 | 3 |00:00:00.08 | 6605 | 5169 | | | |
| 2 | NESTED LOOPS | | 1 | 11590 | 94 | 2002 (2)| 00:00:01 | 621 |00:00:00.08 | 5984 | 4850 | | | |
| 3 | VIEW | | 1 | 11590 | 656K| 1456 (2)| 00:00:01 | 12607 |00:00:00.09 | 5291 | 4850 | | | |
| 4 | HASH GROUP BY | | 1 | 11590 | 226K| 1456 (2)| 00:00:01 | 12607 |00:00:00.08 | 5291 | 4850 | 2132K| 1219K| 2899K (0)|
|* 5 | TABLE ACCESS FULL | ORDER_P | 1 | 11591 | 226K| 1454 (2)| 00:00:01 | 12607 |00:00:00.07 | 5291 | 4850 | | | |
|* 6 | INDEX UNIQUE SCAN | EMP_P_PK | 12607 | 1 | | 0 (0)| | 621 |00:00:00.01 | 693 | 0 | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| EMP_P | 621 | 1 | 36 | 1 (0)| 00:00:01 | 3 |00:00:00.01 | 621 | 319 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("ORDERDT">='20140901' AND "ORDERDT"<='20140903'))
6 - access("A"."EMPID"="B"."EMPID")
7 - filter("B"."EMPCD"='z0005')
|
샘플 데이터 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
|
-------샘플데이터 생성 시작-------
-- 테이블 생성
drop table emp_p purge;
drop table sample_p purge;
drop table order_p;
create table sample_p (empid number, empnm varchar2(5), addr varchar2(10), phone varchar2(13), empcd varchar2(5)) nologging;
create table order_p (empid number, orderdt varchar2(8), orderpric number, orderqty number) nologging;
-- 데이터 생성(1/10)
DECLARE
TYPE tbl_ins IS TABLE OF sample_p%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..69650 LOOP
w_ins(i).empid := i;
w_ins(i).empnm := dbms_random.string('x',5);
w_ins(i).addr := 'addr';
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).empcd := dbms_random.string('x',1)||ceil(dbms_random.value(1000, 9999));
END LOOP;
FORALL i in 1..69650 INSERT INTO sample_p VALUES w_ins(i);
COMMIT;
END;
/
DECLARE
TYPE tbl_ins IS TABLE OF sample_p%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 69651..70000 LOOP
w_ins(i).empid := i;
w_ins(i).empnm := dbms_random.string('x',5);
w_ins(i).addr := 'addr';
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).empcd := 'z0005';
END LOOP;
FORALL i in 69651..70000 INSERT INTO sample_p VALUES w_ins(i);
COMMIT;
END;
/
create table emp_p nologging as select * from sample_p order by dbms_random.random;
DECLARE
TYPE tbl_ins IS TABLE OF order_p%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1500000 LOOP
w_ins(i).empid := i;
w_ins(i).orderdt := '2014'|| to_char(round(dbms_random.value(1,12)) , 'FM09') || to_char( round(dbms_random.value(1,28)) , 'FM09');
w_ins(i).orderpric := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).orderqty := ceil(dbms_random.value(1, 10));
END LOOP;
FORALL i in 1..1500000 INSERT INTO order_p VALUES w_ins(i);
COMMIT;
END;
/
alter table emp_p logging;
alter table order_p logging;
drop index emp_p_pk;
drop index emp_p_ix01;
drop index order_p_ix01;
create unique index emp_p_pk on emp_p(empid);
create index emp_p_ix01 on emp_p(empcd);
create index order_p_ix01 on order_p(empid, orderdt);
conn / as sysdba
select table_name, segment_created, last_analyzed from dba_tables where table_name in ('EMP_P','ORDER_P');
exec dbms_stats.gather_table_stats('IMSI', 'emp_p', cascade=>TRUE);
exec dbms_stats.gather_table_stats('IMSI', 'order_p', cascade=>TRUE);
select table_name, segment_created, last_analyzed from dba_tables where table_name in ('EMP_P','ORDER_P');
conn imsi/imsi
|
스터디원 답안
P님 답안
- 문제점 파악
order_p 테이블이 건수가 많지안 full scan 함
emp_p 테이블의 empcd='z0005'가 분포도가 0.5%인데 이 컬럼이 있는 인덱스로 사용하지 않고 empid 유니크 인덱스를 사용함
- 개선 방안
emp_p 테이블에서 empcd로 적은양을 찾고 empid 까지 인덱스에 포함
후행 테이블에는 조인키인 empid를 먼저 오게하고 orderdt가 오게끔 생성되어있는 인덱스로 이용하여 성능에 유리하게끔 작성
- 쿼리
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SQL>
drop index emp_p_ix02;
create index emp_p_ix02 on emp_p(empcd, empid);
select /*+ gather_plan_statistics leading(b a) use_nl(a) index(b emp_p_ix02) merge(a) */
a.empid, cnt 총주문횟수, price 총주문금액, qty 총주문량, dt 최종주문일자, b.empnm, b.addr, b.phone
from
(
select /*+ order_p(index order_p_ix01) */ empid, count(*) cnt, sum(orderpric) price, sum(orderqty) qty, max(orderdt) dt
from order_p
where orderdt between '20140901' and '20140903'
group by empid
) a, emp_p b
where a.empid = b.empid
and b.empcd = 'z0005';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 3 |00:00:00.01 | 381 | 6 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 56 | 8 (13)| 00:00:01 | 3 |00:00:00.01 | 381 | 6 | 836K| 836K| 789K (0)|
| 2 | NESTED LOOPS | | 1 | 1 | 56 | 7 (0)| 00:00:01 | 3 |00:00:00.01 | 381 | 6 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 56 | 7 (0)| 00:00:01 | 3 |00:00:00.01 | 380 | 6 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP_P | 1 | 1 | 36 | 4 (0)| 00:00:01 | 350 |00:00:00.01 | 353 | 2 | | | |
|* 5 | INDEX RANGE SCAN | EMP_P_IX02 | 1 | 1 | | 2 (0)| 00:00:01 | 350 |00:00:00.01 | 3 | 2 | | | |
|* 6 | INDEX RANGE SCAN | ORDER_P_IX01 | 350 | 1 | | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 27 | 4 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | ORDER_P | 3 | 1 | 20 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 1 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."EMPCD"='z0005')
6 - access("EMPID"="B"."EMPID" AND "ORDERDT">='20140901' AND "ORDERDT"<='20140903')
|
J님 답안1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SQL>
drop index emp_p_ix02;
select /*+ gather_plan_statistics leading(b) index (b emp_p_ix01) */ a.empid, cnt, price, qty, dt, b.empnm, b.addr, b.phone
from (
select /*+ no_merge push_pred index (p order_p_ix01) */
empid, count(*) cnt, sum(orderpric) price, sum(orderqty) qty, max(orderdt) dt
from order_p p
where orderdt between '20140901' and '20140903'
group by empid
) a, emp_p b
where a.empid = b.empid
and b.empcd = 'z0005';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 3 |00:00:00.01 | 923 |
| 1 | NESTED LOOPS | | 1 | 1 | 81 | 7 (0)| 00:00:01 | 3 |00:00:00.01 | 923 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | EMP_P | 1 | 1 | 36 | 3 (0)| 00:00:01 | 350 |00:00:00.01 | 235 |
|* 3 | INDEX RANGE SCAN | EMP_P_IX01 | 1 | 1 | | 1 (0)| 00:00:01 | 350 |00:00:00.01 | 3 |
| 4 | VIEW PUSHED PREDICATE | | 350 | 1 | 45 | 4 (0)| 00:00:01 | 3 |00:00:00.01 | 688 |
|* 5 | FILTER | | 350 | | | | | 3 |00:00:00.01 | 688 |
| 6 | SORT AGGREGATE | | 350 | 1 | 20 | | | 350 |00:00:00.01 | 688 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_P | 350 | 1 | 20 | 4 (0)| 00:00:01 | 3 |00:00:00.01 | 688 |
|* 8 | INDEX RANGE SCAN | ORDER_P_IX01 | 350 | 1 | | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 686 |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."EMPCD"='z0005')
5 - filter(COUNT(*)>0)
8 - access("EMPID"="B"."EMPID" AND "ORDERDT">='20140901' AND "ORDERDT"<='20140903')
|
J님 답안2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL>
drop index emp_p_ix02;
select /*+ gather_plan_statistics leading(b) index (b emp_p_ix01) */ a.empid, cnt, price, qty, dt, b.empnm, b.addr, b.phone
from (
select /*+ index (p order_p_ix01) */
empid, count(*) cnt, sum(orderpric) price, sum(orderqty) qty, max(orderdt) dt
from order_p p
where orderdt between '20140901' and '20140903'
group by empid
) a, emp_p b
where a.empid = b.empid
and b.empcd = 'z0005';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 3 |00:00:00.01 | 920 | | |
| 1 | HASH GROUP BY | | 1 | 1 | 56 | 7 (15)| 00:00:01 | 3 |00:00:00.01 | 920 | 836K| 836K| 786K (0)|
| 2 | NESTED LOOPS | | 1 | 1 | 56 | 6 (0)| 00:00:01 | 3 |00:00:00.01 | 920 | | |
| 3 | NESTED LOOPS | | 1 | 1 | 56 | 6 (0)| 00:00:01 | 3 |00:00:00.01 | 919 | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP_P | 1 | 1 | 36 | 3 (0)| 00:00:01 | 350 |00:00:00.01 | 234 | | |
|* 5 | INDEX RANGE SCAN | EMP_P_IX01 | 1 | 1 | | 1 (0)| 00:00:01 | 350 |00:00:00.01 | 2 | | |
|* 6 | INDEX RANGE SCAN | ORDER_P_IX01 | 350 | 1 | | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 685 | | |
| 7 | TABLE ACCESS BY INDEX ROWID | ORDER_P | 3 | 1 | 20 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 1 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."EMPCD"='z0005')
6 - access("EMPID"="B"."EMPID" AND "ORDERDT">='20140901' AND "ORDERDT"<='20140903')
|
S님 답안
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SQL>
select /*+ gather_plan_statistics no_merge(a) push_pred(a) index(a emp_idx01) index(b order_idx01) */
a.empid, cnt 총주문횟수, price 총주문금액, qty 총주문량, dt 최종주문일자,
b.empnm, b.addr,b.phone from
(
select empid, count(*) cnt , sum(orderpric) price, sum(orderqty) qty,
max(orderdt) dt from order_p
where orderdt between '20140901' and '20140903'
group by empid
) a, emp_p b
where a.empid=b.empid
and b.empcd='z0005';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 3 |00:00:00.01 | 923 |
| 1 | NESTED LOOPS | | 1 | 1 | 81 | 7 (0)| 00:00:01 | 3 |00:00:00.01 | 923 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | EMP_P | 1 | 1 | 36 | 3 (0)| 00:00:01 | 350 |00:00:00.01 | 235 |
|* 3 | INDEX RANGE SCAN | EMP_P_IX01 | 1 | 1 | | 1 (0)| 00:00:01 | 350 |00:00:00.01 | 3 |
| 4 | VIEW PUSHED PREDICATE | | 350 | 1 | 45 | 4 (0)| 00:00:01 | 3 |00:00:00.01 | 688 |
|* 5 | FILTER | | 350 | | | | | 3 |00:00:00.01 | 688 |
| 6 | SORT AGGREGATE | | 350 | 1 | 20 | | | 350 |00:00:00.01 | 688 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_P | 350 | 1 | 20 | 4 (0)| 00:00:01 | 3 |00:00:00.01 | 688 |
|* 8 | INDEX RANGE SCAN | ORDER_P_IX01 | 350 | 1 | | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 686 |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."EMPCD"='z0005')
5 - filter(COUNT(*)>0)
8 - access("EMPID"="B"."EMPID" AND "ORDERDT">='20140901' AND "ORDERDT"<='20140903')
|
모범답안1
merge 힌트는 따로 쓰지 않음
leading 힌트로 테이블 읽는 순서만 조정
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL>
select /*+ gather_plan_statistics leading(b) use_nl(a) index(b emp_p_ix01) index(a order_p_ix01) */
a.empid, cnt 총주문횟수, price 총주문금액, qty 총주문량, dt 최종주문일자, b.empnm, b.addr, b.phone
from
(
select empid, count(*) cnt, sum(orderpric) price, sum(orderqty) qty, max(orderdt) dt
from order_p
where orderdt between '20140901' and '20140903'
group by empid
) a, emp_p b
where a.empid = b.empid
and b.empcd = 'z0005';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 3 |00:00:00.01 | 920 | | |
| 1 | HASH GROUP BY | | 1 | 1 | 56 | 7 (15)| 00:00:01 | 3 |00:00:00.01 | 920 | 836K| 836K| 779K (0)|
| 2 | NESTED LOOPS | | 1 | 1 | 56 | 6 (0)| 00:00:01 | 3 |00:00:00.01 | 920 | | |
| 3 | NESTED LOOPS | | 1 | 1 | 56 | 6 (0)| 00:00:01 | 3 |00:00:00.01 | 919 | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP_P | 1 | 1 | 36 | 3 (0)| 00:00:01 | 350 |00:00:00.01 | 234 | | |
|* 5 | INDEX RANGE SCAN | EMP_P_IX01 | 1 | 1 | | 1 (0)| 00:00:01 | 350 |00:00:00.01 | 2 | | |
|* 6 | INDEX RANGE SCAN | ORDER_P_IX01 | 350 | 1 | | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 685 | | |
| 7 | TABLE ACCESS BY INDEX ROWID | ORDER_P | 3 | 1 | 20 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 1 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."EMPCD"='z0005')
6 - access("EMPID"="B"."EMPID" AND "ORDERDT">='20140901' AND "ORDERDT"<='20140903')
|
모범답안2
push_pred 활용
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SQL>
select /*+ gather_plan_statistics leading(b a) use_nl(a) index(b emp_p_ix01) index(a order_p_ix01) no_merge(a) push_pred(a)*/
a.empid, cnt 총주문횟수, price 총주문금액, qty 총주문량, dt 최종주문일자, b.empnm, b.addr, b.phone
from
(
select empid, count(*) cnt, sum(orderpric) price, sum(orderqty) qty, max(orderdt) dt
from order_p
where orderdt between '20140901' and '20140903'
group by empid
) a, emp_p b
where a.empid = b.empid
and b.empcd = 'z0005';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 3 |00:00:00.01 | 923 |
| 1 | NESTED LOOPS | | 1 | 1 | 81 | 7 (0)| 00:00:01 | 3 |00:00:00.01 | 923 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | EMP_P | 1 | 1 | 36 | 3 (0)| 00:00:01 | 350 |00:00:00.01 | 235 |
|* 3 | INDEX RANGE SCAN | EMP_P_IX01 | 1 | 1 | | 1 (0)| 00:00:01 | 350 |00:00:00.01 | 3 |
| 4 | VIEW PUSHED PREDICATE | | 350 | 1 | 45 | 4 (0)| 00:00:01 | 3 |00:00:00.01 | 688 |
|* 5 | FILTER | | 350 | | | | | 3 |00:00:00.01 | 688 |
| 6 | SORT AGGREGATE | | 350 | 1 | 20 | | | 350 |00:00:00.01 | 688 |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_P | 350 | 1 | 20 | 4 (0)| 00:00:01 | 3 |00:00:00.01 | 688 |
|* 8 | INDEX RANGE SCAN | ORDER_P_IX01 | 350 | 1 | | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 686 |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"."EMPCD"='z0005')
5 - filter(COUNT(*)>0)
8 - access("EMPID"="B"."EMPID" AND "ORDERDT">='20140901' AND "ORDERDT"<='20140903')
|
* 본문 샘플데이터의 분포, DB 버전이 문제와 동일하지 않기 때문에 문제와 답안의 성능(결과)가 devDo님 결과와 다를수있음
* 상세 설명은 devDo님 설명참조
참조 :
https://blog.naver.com/oracledo/220412608959
https://blog.naver.com/oracledo/220425668814
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c itas 시 full scan과 index scan 속도 비교 테스트 (2) | 2022.04.13 |
|---|---|
| 오라클 19c 실행계획 비교 DBMS_XPLAN.COMPARE_PLANS (0) | 2022.03.21 |
| sqlp 실기튜닝실습 20-1 인덱스 최적화 구성 devDo (0) | 2022.03.06 |
| 오라클 11gR1 이상버전에서 보이는 Nested Loop 조인 실행계획(Prefetch, vector I/O) (0) | 2021.11.16 |
| 오라클 19c dbms_xplan.display_cursor Hint Report 포맷 (0) | 2021.10.23 |