- 로우 단위 출력
with connect_by_level_t
as (
select '통신회사' as c1,
'sk델레콤' as c2,
'KT' as c3,
'LGU+' as c4
from dual
)
select * from connect_by_level_t t;
C1 C2 C3 C4
-------- -------- -- ----
통신회사 sk델레콤 KT LGU+
- Connect By Level, Decode 이용하여 로우 -> 컬럼 단위로 출력
with connect_by_level_t
as (
select '통신회사' as c1,
'SK텔레콤' as c2,
'KT' as c3,
'LGU+' as c4
from dual
)
select c1 as "통신회사"
from (
select tt.rno,
max(decode(tt.rno,1,decode(t.c1,'통신회사',decode(t.c2,'SK텔레콤',t.c2))
,2,decode(t.c1,'통신회사',decode(t.c3,'KT',t.c3))
,3,decode(t.c1,'통신회사',decode(t.c4,'LGU+',t.c4)))) as c1
from connect_by_level_t t, (select level as rno from dual connect by level <= 3) tt
group by tt.rno );
통신회사
--------
SK텔레콤
KT
LGU+
select '월(계)' as c1,
substr(c2,1,6) as c2,
sum(c1) as c3
from connect_by_level_sum
where c2 between to_char(trunc(sysdate,'month'),'yyyymmdd')
and to_char(sysdate,'yyyymmdd')
group by substr(c2,1,6)
union all
select '일(계)',c2, sum(c1)
from connect_by_level_sum
where c2 between to_char(trunc(sysdate,'month'),'yyyymmdd')
and to_char(sysdate,'yyyymmdd')
group by c2;
C1 C2 C3
------ ------------ ----------
월(계) 201311 137826
일(계) 20131115 7259
일(계) 20131116 7260
일(계) 20131105 7249
일(계) 20131113 7257
일(계) 20131114 7258
일(계) 20131101 7245
일(계) 20131104 7248
일(계) 20131109 7253
일(계) 20131110 7254
일(계) 20131102 7246
일(계) 20131106 7250
일(계) 20131108 7252
일(계) 20131117 7261
일(계) 20131119 7263
일(계) 20131103 7247
일(계) 20131107 7251
일(계) 20131111 7255
일(계) 20131112 7256
일(계) 20131118 7262
20 개의 행이 선택되었습니다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.09 0.10 263 532 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.10 0.12 263 532 0 20
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65
Rows Row Source Operation
------- ---------------------------------------------------
20 UNION-ALL (cr=532 pr=263 pw=0 time=76093 us)
1 HASH GROUP BY (cr=266 pr=263 pw=0 time=76088 us)
19 FILTER (cr=266 pr=263 pw=0 time=21053 us)
19 TABLE ACCESS FULL CONNECT_BY_LEVEL_SUM (cr=266 pr=263 pw=0 time=21030 us)
19 HASH GROUP BY (cr=266 pr=0 pw=0 time=31374 us)
19 FILTER (cr=266 pr=0 pw=0 time=4422 us)
19 TABLE ACCESS FULL CONNECT_BY_LEVEL_SUM (cr=266 pr=0 pw=0 time=4414 us)
h4.* Cartesian Product
SQL> select count(*) from emp;
COUNT(*)
----------
12
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL> select count(*) from emp, dept;
COUNT(*)
----------
48
h5.SQL 2 - 중복 조회 제거한 효율적인 SQL
select decode(y.rno,1,'일(계)','월(계)') as c1
,decode(y.rno,1,c2,substr(c2,1,6)) as c2
,sum(decode(y.rno,1,c3,c3)) as c3
from (
select c2, sum(c1) as c3
from connect_by_level_sum
where c2 between to_char(trunc(sysdate,'month'),'yyyymmdd')
and to_char(sysdate,'yyyymmdd')
group by c2
) x , (select level as rno from dual connect by level <= 2) y
group by decode(y.rno,1,'일(계)','월(계)'),
decode(y.rno,1,c2,substr(c2,1,6));
C1 C2 C3
------ ------------ ----------
월(계) 201311 137826
일(계) 20131115 7259
일(계) 20131116 7260
일(계) 20131105 7249
일(계) 20131113 7257
일(계) 20131114 7258
일(계) 20131101 7245
일(계) 20131104 7248
일(계) 20131109 7253
일(계) 20131110 7254
일(계) 20131102 7246
일(계) 20131106 7250
일(계) 20131108 7252
일(계) 20131117 7261
일(계) 20131119 7263
일(계) 20131103 7247
일(계) 20131107 7251
일(계) 20131111 7255
일(계) 20131112 7256
일(계) 20131118 7262
20 개의 행이 선택되었습니다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.03 0.07 263 266 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.03 0.09 263 266 0 20
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65
Rows Row Source Operation
------- ---------------------------------------------------
20 HASH GROUP BY (cr=266 pr=263 pw=0 time=77581 us)
38 MERGE JOIN CARTESIAN (cr=266 pr=263 pw=0 time=77135 us)
19 VIEW (cr=266 pr=263 pw=0 time=77105 us)
19 HASH GROUP BY (cr=266 pr=263 pw=0 time=77104 us)
19 FILTER (cr=266 pr=263 pw=0 time=19599 us)
19 TABLE ACCESS FULL CONNECT_BY_LEVEL_SUM (cr=266 pr=263 pw=0 time=19574 us)
38 BUFFER SORT (cr=0 pr=0 pw=0 time=94 us)
2 VIEW (cr=0 pr=0 pw=0 time=27 us)
2 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=23 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us)
h5.SQL 1 cpu ==> 0.10 elapsed ==> 0.12 disk ==> 263 query ==> 532
h5.SQL 2 cpu ==> 0.03 elapsed ==> 0.09 disk ==> 263 query ==> 266
[ 성능 문제 SQL ]
select A.*
from tb_slp A
where status IN ('I', 'T')
and part_id >= :b0 and part_id <= :b1
order by part_id ASC,
slp_dtti ASC,
slp_seq ASC;
[ SQL 의 Index ]
[SQL 수행 내역 [HASH VALUE : 450651061]]
SCHEMA EXEC DISK_READS BUFFER_GETS Rows BUFFER_GETS(1) ELAPSED(1)
------- ------ ----------- ------------ ----- -------------- ----------
OLNAPPB 9318 19288567 1305041823 33311 140056.002 14.1427943
1. buffer get이 140,056 block 인데 데이터 추출 건수는 4(rows/exec)건으로 I/O가 너무 많다.
2. Index의 구성과 조회 조건에 의한 문제
part_id >= :b0 and part_id <= :b1 => 파티션 범위 체크, 범위조건으로 수행됨
status IN ('I', 'T') => 데이터의 상태 체크
status 조건에 대한 데이터가 극소수이지만, 인덱스의 선두 컬럼인 part_id가 범위 조건으로 수행되어
status 조건이 index access predicate 가 아닌 index fileter predicate 로 수행되었다.
즉, 인덱스 part_id 컬럼으로 범위 검색후 , status 컬럼으로 filter 처리 하여 테이블 access 하였다.
1. 인덱스 구성 변경 (part_id, status ==> status, part_id)
=> 가장 효율적인 개선안이지만, 운영 업무에서 part_id가 선두 컬럼인 인덱스가 필요한 경우와 , 테이블 사이즈가 너무 커서 변경이 불가한 경우가 있을 수 있다.
2. 인덱스 필터가 되지 않도록 범위 검색을 IN이나 =(EQUAL)조회로 변경
=> 범위 조건의 값을 Connect By Level을 이용해 데이터 생성 후 , where 절의 part_id에 대한 범위 검색 대신 조인으로 처리하도록 변경하면
인덱스 후행 컬럼이 Index Access Predicate로 수행하여 성능상 비효율을 해결할 수 있다.
[ 개선 후 SQL ]
select /*+ leading(part_id) use_nl(part_id a) index(a tb_slp_ix1) */ a. *
from tb slp a,
( -----> dummy join 추가
select lpad((level-1)+:bo, 2, 0) part id
from dual
connect by level <= (:b1-:bo)+1
) part id
where a.status in ('I', 'T')
and a.part id >= :b0
and a.part id <= :b1
and part id.part id = a.part id -----> dummy table 을 이용한 조인 조건추가
order by part_id ASC,
slp_dtti ASC,
slp_seq ASC;
[ SQL 개선 전과 개선 후 Trace 결과 비교 ]
-- 개선 전
call count cpu elapsed disk query current rows
------ ----- ----- ------- ------ ------ ------- ----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 2 82.33 712.21 479960 561444 0 1
------ ----- ----- ------- ------ ------ ------- ----
total 4 82.34 712.22 479960 561444 0 1
Rows Row Source Operation
----- ----------------------------------------------
1 SORT ORDER BY (cr=561444 pr=479960 pw=O time=712216270 us)
1 FILTER (cr=561444 pr=79960 pw=O time= 712216119 us)
1 PARTITION RANGE ITERATOR PARTITION : KEY KEY (cr=561444 pr=479960 time=7122161l2 us)
1 TABLE ACCESS BY LOCAL INDEX ROWID TB_SLP PARTITION: KEY KEY (cr=561444 pr=479960 ... )
1 INDEX RANGE SCAN TB_SLP_IXl PARTITION: KEY KEY (cr=561442 pr=479959 ... )
--개선 후
call count cpu elapsed disk query current rows
------ ----- ----- ------- ------ ------ ------- ----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 2 0.03 0.40 204 712 0 1
------ ----- ----- ------- ------ ------ ------- ----
total 4 0.04 0.41 204 712 0 1
Rows Row Source Operation
----- ----------------------------------------------
1 SORT ORDER BY (cr=712 pr=204 pw=O time=401047 us)
1 FILTER (cr=712 pr=204 pw=O time=400939 us)
1 TABLE ACCESS BY LOCAL INDEX ROWID TB_SLP (cr=712 pr=204 pw=O time=400929 us)
102 NESTED Lα)PS (cr=711 pr=203 pw=O t뇨ne=883868 us)
100 VIEW (cr=O pr=O pw=O time=3237 us)
100 CONNECT BY WITHOUT FILTERING (cr=O pr=O pw=O time=40 us)
1 FAST DUAL (cr=O pr =O pw=O time=3 us)
1 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=711 pr=203 pw=O time=383495)
1 INLIST ITERATOR (cr=711 pr=203 pw=O time=382716 us)
1 INDEX RANGE SCAN TB_SLP_IX1 PARTITION: KEY KEY (cr=711 pr=203 ... )
select c3, count(*)
from dummy_table_date
group by c3
order by c3;
C3 COUNT(*)
-------- ----------
20110101 100000
20110102 100000
20110103 100000
20110104 100000
20110105 100000
var b1 varchar2(10)
var b2 varchar2(10)
var b3 varchar2(10)
exec :b1 := '20110101'
exec :b2 := '20110105'
exec :b3 := 'A'
select /*+ index(x dummy_table_date_idx01) */ x.*
from dummy_table_date x
where x.c3 between :b1 and :b2
and x.c2 = :b3;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 2 0 0
Fetch 1283 0.07 0.07 0 2800 0 19230
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1285 0.07 0.10 0 2802 0 19230
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
19230 19230 19230 FILTER (cr=2800 pr=0 pw=0 time=69841 us)
19230 19230 19230 INDEX RANGE SCAN DUMMY_TABLE_DATE_IDX01 (cr=2800 pr=0 pw=0 time=29816 us cost=1939 size=350834 card=15947)(object id 58963)
select to_char(to_date(:b1,'yyyymmdd')+level-1,'yyyymmdd') as c3
from dual
connect by level <= (to_date(:b2,'yyyymmdd')-to_date(:b1,'yyyymmdd')+1);
var b1 varchar2(10)
var b2 varchar2(10)
var b3 varchar2(10)
exec :b1 := '20110101'
exec :b2 := '20110105'
exec :b3 := 'A'
select /*+ leading(d) use_nl(d x) index(x dummy_table_date_idx01) */ x.*
from dummy_table_date x,
( select to_char(to_date(:b1,'yyyymmdd')+level-1,'yyyymmdd') as c3
from dual
connect by level <= (to_date(:b2,'yyyymmdd')-to_date(:b1,'yyyymmdd')+1)) d
where d.c3 = x.c3
and x.c2 = :b3;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1283 0.04 0.04 0 1361 0 19230
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1285 0.04 0.04 0 1361 0 19230
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
19230 19230 19230 NESTED LOOPS (cr=1361 pr=0 pw=0 time=67651 us cost=5 size=89292 card=3189)
5 5 5 VIEW (cr=0 pr=0 pw=0 time=216 us cost=2 size=6 card=1)
5 5 5 CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=148 us)
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=4 us cost=2 size=0 card=1)
19230 19230 19230 INDEX RANGE SCAN DUMMY_TABLE_DATE_IDX01 (cr=1361 pr=0 pw=0 time=34776 us cost=3 size=70158 card=3189)(object id 58963)