7. 인덱스 스캔 효율 (by ljh1148) [2010.08.02]
h3 참고자료
http://scidb.tistory.com/118
07인덱스 스캔 효율
- 1. Sequential 액세스의 선택도를 높인다.
- 2. Random 액세스 발생량을 줄인다. (4, 5, 6절에서 함)
Sequential 액세스 선택도
선택도란? 전체 레코드 중에서 조건절에 의해 선택되는 비율을 말함
- 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건저을 만족하는 레코드를 찾기 위해 스캔 할것으로 예상되는 비율(%)
- 유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적을 테이블을 방문할 것으로 예상되는 비율(%)
- 인덱스 스캔한 건수 중 결과로 선택되는 비율을 말하는 것이며, 그 비율이 높아야 효율적이라는 의미는 같은결과 건수를 내기 위해 적은 양을 읽어야 함
(1) 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성
- 선두 컬럼 : 인덱스 구성상 맨 앞쪽에 있는 단 하나의 컬럼을 지칭할 때 사용
- 선행 컬럼 : 상대적으로 앞쪽에 놓인 컬럼을 칭 할때 사용
where col1 = 1 and col2 = 'A' and col3 = '나' and col4 = 'a'
where col1 = 1 and col2 = 'A' and col3 = '나' and col4 >= 'a'
where col1 = 1 and col2 = 'A' and col3 between '가' and '다' and col4 = 'a'
where col1 = 1 and col2 = 'A' and col3 between '가' and '다' and col4 between 'a' and 'b'
- 선행 컬럼이 모두 '=' 조건인 상태에서 첫 번째 나타나는 범위 검색 조건까지만 만족하는 인덱스 레코드만 모두 연속되게 모여 있지만
그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.
where col1 between 1 and 2 and col2 = 'A' and col3 = '나' and col4 = 'a'
- 선두 컬럼이 범위 검색 조건이면 그 조건에 만족하는 레코드만 서로 모여있고, 나머지 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어지게된다.
(2) 인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 발생하는 비효율
SQL> select * from v$version where rownum <2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
SQL> CREATE TABLE 매물아파트매매 AS
2 SELECT ROUND4) DBMS_RANDOM.VALUE(1,22)) AS "해당층"
3 , 1000 + ROUND( DBMS_RANDOM.VALUE(1,20)) * 100 AS "평당가"
4 , '2010'||LPAD(CEIL(ROWNUM/10000), 2, '0') AS "입력일"
5 , 'A0101135'||ROUND(DBMS_RANDOM.VALUE(1000, 2000) ) AS "아파트시세코드"
6 , DECODE(ROUND( DBMS_RANDOM.VALUE(1,10) ),1,12,2,15,3,18,4,21,5,28,6,32,7,42,8,48,9,59,10,68) AS "평형"
7 , DECODE(ROUND( DBMS_RANDOM.VALUE(1,4) ),1,'A',2,'B',3,'C',4,'D') AS "평형타입"
8 , ROUND( DBMS_RANDOM.VALUE(1,4)) -1 AS "인터넷매물"
9 FROM DUAL
10 CONNECT BY LEVEL < 1000000;
테이블이 생성되었습니다.
SQL> desc 매물아파트매매;
이름 널? 유형
----------------------------------------------------------------- -------- ----------------
해당층 NUMBER
평당가 NUMBER
입력일 VARCHAR2(8)
아파트시세코드 VARCHAR2)8)
평형 NUMBER
평형타입 VARCHAR2(1)
인터넷매물 NUMBER
SQL> create index inx_매물아파트매매_01 on 매물아파트매매(아파트시세코드, 평형, 평형타입, 인터넷매물)
2 ;
인덱스가 생성되었습니다.
SQL> create index inx_매물아파트매매_02 on 매물아파트매매(인터넷매물, 아파트시세코드, 평형, 평형타입);
인덱스가 생성되었습니다.
SQL> exec dbms_stats.gather_table_stats(user, '매물아파트매매');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> SELECT * FROM 매물아파트매매 WHERE ROWNUM < 10
2 ;
해당층 평당가 입력일 아파트시세코드 평형 인터넷매물
---------- ---------- -------- ------------------------------------------------ ---------- - -------
4 1200 201001 A01011351613 15 D 2
17 2300 201001 A01011351758 42 D 1
2 2900 201001 A01011351021 18 D 2
21 1900 201001 A01011351574 48 B 2
17 2100 201001 A01011351094 32 D 2
3 2500 201001 A01011351365 21 C 2
18 2500 201001 A01011351980 48 A 2
7 1600 201001 A01011351527 59 D 1
11 1500 201001 A01011351292 32 A 0
9 개의 행이 선택되었습니다.
SQL> SELECT P.SPID SERVER
2 FROM V$PROCESS P, V$SESSION S
3 WHERE P.ADDR = S.PADDR
4 AND S.AUDSID = USERENV('SESSIONID')
5 ;
SERVER
------------
5928
SQL> alter session set events '10046 trace name context forever, level 1';
세션이 변경되었습니다.
-- 실수로 넘버 타입을 바차로 했는데.. 왜 타죠 인덱스 정말신기하내..
select /*+ index(매물아파트매매 INX_매물아파트매매_01) */*
from 매물아파트매매
where 아파트시세코드='A01011351574'
and 평형 = '48'
and 평형타입 = 'A'
and 인터넷매물 between '1' and '2'
order by 입력일 desc
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 3 0.00 0.00 0 19 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 19 0 16
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
16 SORT ORDER BY (cr=19 pr=0 pw=0 time=212 us)
16 TABLE ACCESS BY INDEX ROWID 매물아파트매매 (cr=19 pr=0 pw=0 time=197 us)
16 INDEX RANGE SCAN INX_매물아파트매매_01 (cr=3 pr=0 pw=0 time=64 us)(object id 52944)
--넘버 타입으로 한거임
select /*+ index(매물아파트매매 INX_매물아파트매매_01) */*
from 매물아파트매매
where 아파트시세코드='A01011351574'
and 평형 = 48
and 평형타입 = 'A'
and 인터넷매물 between 1 and 2
order by 입력일 desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 19 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.00 0 19 0 16
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
16 SORT ORDER BY (cr=19 pr=0 pw=0 time=228 us)
16 TABLE ACCESS BY INDEX ROWID 매물아파트매매 (cr=19 pr=0 pw=0 time=229 us)
16 INDEX RANGE SCAN INX_매물아파트매매_01 (cr=3 pr=0 pw=0 time=79 us)(object id 52944)
select /*+ index(매물아파트매매 inx_매물아파트매매_02) */*
from 매물아파트매매
where 아파트시세코드='A01011351574'
and 평형 = 48
and 평형타입 = 'A'
and 인터넷매물 between 1 and 2
order by 입력일 desc
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.00 0.03 3 23 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.04 3 23 0 16
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
16 SORT ORDER BY (cr=23 pr=3 pw=0 time=30389 us)
16 TABLE ACCESS BY INDEX ROWID 매물아파트매매 (cr=23 pr=3 pw=0 time=19884 us)
16 INDEX SKIP SCAN INX_매물아파트매매_02 (cr=7 pr=3 pw=0 time=19752 us)(object id 52945)
SQL> ALTER SESSION SET SQL_TRACE=FALSE;
세션이 변경되었습니다.
(3) BETWEEN 조건을 IN-LIST로 바꾸었을 때 인덱스 스캔 효율
select /*+ index(매물아파트매매 inx_매물아파트매매_02) */*
from 매물아파트매매
where 인터넷매물 in (1 , 2)
and 아파트시세코드='A01011351574'
and 평형 = 48
and 평형타입 = 'A'
order by 입력일 desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 22 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.03 0 22 0 16
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
16 SORT ORDER BY (cr=22 pr=0 pw=0 time=284 us)
16 INLIST ITERATOR (cr=22 pr=0 pw=0 time=259 us)
16 TABLE ACCESS BY INDEX ROWID 매물아파트매매 (cr=22 pr=0 pw=0 time=225 us)
16 INDEX RANGE SCAN INX_매물아파트매매_02 (cr=6 pr=0 pw=0 time=92 us)(object id 52945)
IN-List 갯수를 동적으로..
create table 통합코드 as
select level - 1 as "코드"
, 'CD064' as "코드구분"
from dual
connect by level < 5
테이블이 생성되었습니다.
SQL> select * from 코드
2 ;
코드 코드
----- ----------
CD064 0
CD064 1
CD064 2
CD064 3
select /*+ index(ordered use_nl(b)*/*
from 통합코드 a, 매물아파트매매 b
where a.코드구분 = 'CD064'
and a.코드 between 1 and 2
and b.인터넷매물 = a.코드
and b.아파트시세코드='A01011351574'
and b.평형 = 48
and b.평형타입 = 'A'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 29 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.01 0 30 0 16
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
16 TABLE ACCESS BY INDEX ROWID 매물아파트매매 (cr=29 pr=0 pw=0 time=68 us)
19 NESTED LOOPS (cr=13 pr=0 pw=0 time=794 us)
2 TABLE ACCESS FULL 통합코드 (cr=5 pr=0 pw=0 time=29 us)
16 INDEX RANGE SCAN INX_매물아파트매매_02 (cr=8 pr=0 pw=0 time=35 us)(object id 52945)
between 조건을 IN-List조건으로 바꿀 때 주의 사항
- IN-List 갯수가 많지 않아야 한다.
- between 조건인 선행 컬럼이 많은 리프 블록을 스캔하지만 거기서 선택되는 레코드는 소량일 때 IN-List로의 변환이 효과를 내다
(4) Index Skip Scan을 이용한 비효울 감소
SQL> create table 월별고객별판매집계 as
2 select rownum 고객번호
3 , '2008'||lpad(ceil(rownum/100000),2,'0') 판매월
4 , decode(mod(rownum, 12), 1, 'A','B') 판매구분
5 , round(dbms_random.value(1000,100000), -2) 판매액
6 from dual
7 connect by level <= 1200000;
테이블이 생성되었습니다.
SQL> create index idx_월별고객별판매집계_01 on 월별고객별판매집계( 판매구분, 판매월);
인덱스가 생성되었습니다.
SQL> create index idx_월별고객별판매집계_02 on 월별고객별판매집계( 판매월, 판매구분);
인덱스가 생성되었습니다.
select /*+ index(a idx_월별고객별판매집계_01) */count(*)
from 월별고객별판매집계 a
where 판매구분 = 'A'
and 판매월 between '200801' and '200812'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.11 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 0 281 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.14 0 284 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=281 pr=0 pw=0 time=28693 us)
100000 INDEX RANGE SCAN IDX_월별고객별판매집계_01 (cr=281 pr=0 pw=0 time=20 us)(object id 52949)
select /*+ index(a idx_월별고객별판매집계_02) */count(*)
from 월별고객별판매집계 a
where 판매구분 = 'A'
and 판매월 between '200801' and '200812'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.23 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 1.01 12.19 3089 3090 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 1.01 12.43 3089 3093 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3090 pr=3089 pw=0 time=4662268 us)
100000 INDEX RANGE SCAN IDX_월별고객별판매집계_02 (cr=3090 pr=3089 pw=0 time=216012 us)(object id 52950)
SQL> select /*+ index(a idx_월별고객별판매집계_01) */ count(*)
2 from 월별고객별판매집계 a
3 where 판매구분 = 'A'
4 ;
COUNT(*)
----------
100000
- 테이타 분포도로 인해 레인지 스켄에서 비효율이 발생한다.
IN-List 튜닝
- 11번 더 수직 탐색을하지만 레인지 스켄에서 발생하는 비효율을 줄였다.
select /*+ index(a idx_월별고객별판매집계_02) */count(*)
from 월별고객별판매집계 a
where 판매구분 = 'A'
and 판매월 in ('200801','200802','200803','200804','200805','200806','200807','200808','200809','200810','200811','200812')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.22 8 314 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.24 8 317 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=314 pr=8 pw=0 time=221951 us)
100000 INLIST ITERATOR (cr=314 pr=8 pw=0 time=100018 us)
100000 INDEX RANGE SCAN IDX_월별고객별판매집계_02 (cr=314 pr=8 pw=0 time=163641 us)(object id 52950)
스킵 스켄 튜닝
- 검색조건에 만족하는 데이타 들어 서로 멀리 떨어져 있을때가 Index Skip Scan으로써 효과를 볼 수 있는 전형적인 케이스
select /*+ index_ss(a idx_월별고객별판매집계_02) */count(*)
from 월별고객별판매집계 a
where 판매구분 = 'A'
and 판매월 between '200801' and '200812'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.05 0 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.14 0.16 0 300 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.14 0.21 0 303 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=300 pr=0 pw=0 time=160153 us)
100000 INDEX SKIP SCAN IDX_월별고객별판매집계_02 (cr=300 pr=0 pw=0 time=100068 us)(object id 52950)
(5)범위 검색 조건을 남용할 때 발생하는 비효율
- 구성 인덱스 ( 회사 , 지역, 상품명 )
- 필요 커리
--쿼리 1
select 고객ID, 상품명, 지역,...
from 가입상품
where 회사 = :com
and 지역 = :reg
and 상품명 like :prod||'%'
--쿼리 2
select 고객ID, 상품명, 지역,...
from 가입상품
where 회사 = :com
and 상품명 like :prod||'%'
- 개발자의 귀차니즘 or 미친 일정
select 고객ID, 상품명, 지역,...
from 가입상품
where 회사 = :com
and 지역 like :reg||'%'
and 상품명 like :prod||'%'
튜닝
select 고객ID, 상품명, 지역,...
from 가입상품
where :reg is not null
and 회사 = :com
and 지역 = :reg
and 상품명 like :prod||'%'
union all
select 고객ID, 상품명, 지역,...
from 가입상품
where :reg is null
and 회사 = :com
and 상품명 like :prod||'%'
범위검색 조건만으로 구성된 쿼리 튜닝 사례
- 고객으로부터 콜(Call)이 오면 반경 1km 이내의 가장 가까운 택시에게 시호를 보내는 시스템
- 인덱스 구성 : gis데이터_x01( gis_위도,gis_경도,gis_시작일자,gis_종료이자,gis_지역코드,gis_위치명)
- 데이타는 당일치만 보관
- 아침에 빠르나 시간이 흘러 늦은 밤 시간이 되면 위도상 좌우 1km 이내에 편균 100만개 레코드가 생성
문제 커리
select 위치명
from (select decode(sign(b.우편번호코드-40000),1,'',b.지역명1||' '||
nvl(b.지역명2,'')||' '||a.gis_위치명 위치명
, sqrt(power((a.gis_위도-:승객위도)*111000,2)+
power((a.gis_정도-:승객경도)*88000,2) 거리
from gis데이터 a, 우편번호 b
where a.gis_위도 between :승객위도 - 1 and :승객위도 + 1
and a.gis_경도 between :승객경도 - 1 and :승객경도 + 1
and sysdate between a.gis_시작일자 and a.gis_종료일자
order by 거리)
where rownum <= 1
해결 커리
select decode(sign(b.우편번호코드-40000),1,'',b.지역명1||' '||
nvl(b.지역명2,'')||' '||a.gis_위치명 위치명
from (select 위치명
from (select gis_지역코드, gis_위치명
, sqrt(power((a.gis_위도-:승객위도)*111000,2)+
power((a.gis_정도-:승객경도)*88000,2) 거리
from gis데이터 a, 우편번호 b
where a.gis_위도 between :승객위도 - 0.05 and :승객위도 + 0.05
and a.gis_경도 between :승객경도 - 0.05 and :승객경도 + 0.05
and sysdate between a.gis_시작일자 and a.gis_종료일자
order by 거리)
where rownum <= 1
)
where b.우편번호 = a.gis_지역코드
and rownum <=1;
- 가장 가까운 데 위치한 택시를 찾는게 목적이라 50m 반경에 우선 찾고 없으면... 200m....1km로 커리를 실행 (테이타 양을 줄임)
- 최종 한건만 우편번호랑 조인을 걸었다.
(6) ?은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항
SQL> create table 도서
2 as
3 select rownum 도서번호
4 , '오라클 ' || dbms_random.string('u', 8) 도서명
5 , round(dbms_random.value(1000, 100000), -3) 가격
6 , dbms_random.string('l', 10) 저자
7 , dbms_random.string('u', 10) 출판사
8 , lpad(mod(rownum, 10), 4, '0') || dbms_random.string('l', 4) ISBN
9 from dual
10 connect by level <= 99989;
테이블이 생성되었습니다.
SQL> insert into 도서
2 select 99990 도서번호
3 , '오라클 성능 고도화 원리와 해법 01' 도서명
4 , round(dbms_random.value(1000, 100000), -3) 가격
5 , dbms_random.string('l', 10) 저자
6 , dbms_random.string('u', 10) 출판사
7 , lpad(mod(rownum, 10), 4, '0') || dbms_random.string('l', 4) ISBN
8 from dual;
1 개의 행이 만들어졌습니다.
SQL> insert into 도서
2 select 99990+rownum 도서번호
3 , '오라클 성능 고도화 원리와 해법 ' || lpad(rownum, 2, '0') 도서명
4 , round(dbms_random.value(1000, 100000), -3) 가격
5 , dbms_random.string('l', 10) 저자
6 , dbms_random.string('u', 10) 출판사
7 , lpad(mod(rownum, 10), 4, '0') || dbms_random.string('l', 4) ISBN
8 from dual
9 connect by level <= 10;
10 개의 행이 만들어졌습니다.
SQL> create index 도시명_idx on 도서(도서명);
인덱스가 생성되었습니다.
SQL> exec dbms_stats.gather_table_stats(user, '도서', no_invalidate=>false);
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> column 저자 format a10;
SQL> column 출판사 format a10;
SQL> column isbn format a10;
SQL> column 도서명 format a40;
SQL> select *
2 from (
3 select ROWID ID, rownum rnum, 도서번호, 도서명, 가격, 저자, 출판사, isbn
4 from (
5 select 도서번호, 도서명, 가격, 저자, 출판사, isbn
6 from 도서
7 where 도서명 like ''|| '%'
8 order by 도서명
9 )
10 where rownum <= 110
11 )
12 where rnum >= 91 --> 10 페이지만 출력
13 ;
ID RNUM 도서번호 도서명 가격 저자 출판사 ISBN
------------------ ---------- ---------- ---------------------------------------- ---------- -------
AAAM8uAABAAAVRCABQ 91 52588 오라클 AARUHCXP 77000 caqzwodvjr VCRANMNMXL 0008tnhr
AAAM8uAABAAAVUbAAW 92 78340 오라클 AARUXMGU 60000 rsyykewoya BOYFCUKLRY 0000bfwk
AAAM8uAABAAAVSbABz 93 63207 오라클 AASAKAWZ 70000 ikmqrunmnj PGAROTHVRW 0007sdja
AAAM8uAABAAAVKjAAq 94 13648 오라클 AASBAABO 67000 mxflzzqora IRGQZGFCZD 0008gnky
AAAM8uAABAAAVWgAAY 95 94166 오라클 AASBLLMF 85000 dvttxkrgqv IWYDRTFJRX 0006eogt
AAAM8uAABAAAVJaABP 96 4919 오라클 AASCTDUE 19000 gzinxiivit FZEBHZTVVY 0009qewf
AAAM8uAABAAAVOtAA8 97 34843 오라클 AASEFRCC 86000 pwnoeatzmd BZEBHQXGCX 0003xtfr
AAAM8uAABAAAVVFAAt 98 83361 오라클 AASEJESI 50000 techftlanq DUPFYSWZSU 0001abgt
AAAM8uAABAAAVWMAA4 99 91818 오라클 AASKAFFX 18000 xrcjrjmepx JMDLMFBROP 0008hsyc
--> AAAM8uAABAAAVT8ABJ 100 74703 오라클 AATJENQO 20000 obvttsexjo BZKZEEEOUY 0003uvwi
AAAM8uAABAAAVSzABR 101 66029 오라클 AATJKUKD 31000 csvlocoxef VOFWLYASHA 0009cech
ID RNUM 도서번호 도서명 가격 저자 출판사 ISBN
------------------ ---------- ---------- ---------------------------------------- ---------- -------
AAAM8uAABAAAVWjABr 102 94606 오라클 AATLLCYS 15000 xftntnsgip XAKRCRKRGW 0006ejqn
AAAM8uAABAAAVWiAAH 103 94387 오라클 AATOELMV 69000 rvgmjyzqko WWVYZANSZQ 0007vqov
AAAM8uAABAAAVPqAAB 104 42039 오라클 AATQWSTC 44000 ibzdkrlbrf FFRIDTCLAW 0009axbt
AAAM8uAABAAAVRLAAG 105 53585 오라클 AATREKHY 43000 fszvicsbvf WFIWMETFRJ 0005jxgi
AAAM8uAABAAAVNFAAw 106 22458 오라클 AAUGWQHM 89000 tgyfbkfoxu ELTCEKHZCU 0008ncvs
AAAM8uAABAAAVMjAAS 107 18383 오라클 AAULZZYZ 60000 jpizcoydmh UMKPSPAOSH 0003fnml
AAAM8uAABAAAVKlABh 108 13941 오라클 AAUNLSGM 8000 dtikovpmcn NUPEINJDTC 0001ycpm
AAAM8uAABAAAVJjAAw 109 5975 오라클 AAUWMDUC 77000 vgjktoltaj WPMJLJEOKS 0005jhbp
AAAM8uAABAAAVTlABw 110 72006 오라클 AAVGVZPN 57000 ynptbmapos DABGIFIFMR 0006ndgq
20 개의 행이 선택되었습니다.
explain plan for select *
from (
select /*+ index(도서 도시명_idx) */
rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 like '오라클' || '%'
and 도서명 = '오라클 AATJENQO' -- 이전 페이지에서 출력된 마지막 도서명
and rowid > 'AAAM8uAABAAAVT8ABJ' -- 이전 페이지에서 출력된 마지막 도서의 rowid
union all
select /*+ index(도서 도시명_idx) */
rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 like '오라클'|| '%'
and 도서명 > '오라클 AATJENQO' -- 이전 페이지에서 출력된 마지막 도서명
)
where rownum <= 10
;
RID 도서번호 도서명 가격 저자 출판사 ISBN
------------------ ---------- ---------------------------------------- ---------- ---------- -------
AAAM8uAABAAAVSzABR 66029 오라클 AATJKUKD 31000 csvlocoxef VOFWLYASHA 0009cech
AAAM8uAABAAAVWjABr 94606 오라클 AATLLCYS 15000 xftntnsgip XAKRCRKRGW 0006ejqn
AAAM8uAABAAAVWiAAH 94387 오라클 AATOELMV 69000 rvgmjyzqko WWVYZANSZQ 0007vqov
AAAM8uAABAAAVPqAAB 42039 오라클 AATQWSTC 44000 ibzdkrlbrf FFRIDTCLAW 0009axbt
AAAM8uAABAAAVRLAAG 53585 오라클 AATREKHY 43000 fszvicsbvf WFIWMETFRJ 0005jxgi
AAAM8uAABAAAVNFAAw 22458 오라클 AAUGWQHM 89000 tgyfbkfoxu ELTCEKHZCU 0008ncvs
AAAM8uAABAAAVMjAAS 18383 오라클 AAULZZYZ 60000 jpizcoydmh UMKPSPAOSH 0003fnml
AAAM8uAABAAAVKlABh 13941 오라클 AAUNLSGM 8000 dtikovpmcn NUPEINJDTC 0001ycpm
AAAM8uAABAAAVJjAAw 5975 오라클 AAUWMDUC 77000 vgjktoltaj WPMJLJEOKS 0005jhbp
AAAM8uAABAAAVTlABw 72006 오라클 AAVGVZPN 57000 ynptbmapos DABGIFIFMR 0006ndgq
10 개의 행이 선택되었습니다.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2234641167
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 80460 | 13 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 11 | 88506 | 13 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| 도서 | 1 | 55 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | 도시명_I| 1 | | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 6 | TABLE ACCESS BY INDEX ROWID| 도서 | 10 | 550 | 11 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | 도시명_I| 99999 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
5 - access("도서명"='오라클 AATJENQO')
filter(ROWID>'AAAM8uAABAAAVT8ABJ' AND "도서명" LIKE '오라클%')
7 - access("도서명" LIKE '오라클%')
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
filter("도서명" LIKE '오라클%' AND "도서명">'오라클 AATJENQO')
23 개의 행이 선택되었습니다.
explain plan for select *
from (
select /*+ index(도서 도시명_idx) */
rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 like '오라클' || '%'
and 도서명 = '오라클 AATJENQO' -- 이전 페이지에서 출력된 마지막 도서명
and rowid > 'AAAM8uAABAAAVT8ABJ' -- 이전 페이지에서 출력된 마지막 도서의 rowid
union all
select /*+ index(도서 도시명_idx) */
rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where RTRIM(도서명) like '오라클'|| '%'
and 도서명 > '오라클 AATJENQO' -- 이전 페이지에서 출력된 마지막 도서명
)
where rownum <= 10
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2234641167
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 80460 | 14 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 11 | 88506 | 14 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| 도서 | 1 | 55 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | 도시명_I| 1 | | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 6 | TABLE ACCESS BY INDEX ROWID| 도서 | 10 | 550 | 12 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | 도시명_I| 5000 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
5 - access("도서명"='오라클 AATJENQO')
filter(ROWID>'AAAM8uAABAAAVT8ABJ' AND "도서명" LIKE '오라클%')
7 - access("도서명">'오라클 AATJENQO')
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
filter(RTRIM("도서명") LIKE '오라클%')
23 개의 행이 선택되었습니다.
OR-Expansion을 이용하는 방법과 주의 사항
- 9i까지는 I/O 비용 모델, CPU 비용 모델을 불문하고 뒤쪽에 있는 조건 값을 먼저 실행한다
- 10g CPU 비용 모델에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행한다.( 값 분포에 상관없이 항상 뒤쪽에 있는 조건식이 먼저 처리되도록 하려면 ordered_predicates 힌트 사용)
select /*+ index(도서 도시명_idx) use_concat ordered_predicates */
rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 like '오라클' || '%'
and ((도서명 > '오라클 AATJENQO')
or
(도서명 = '오라클 AATJENQO' and rowid > 'AAAM8uAABAAAVT8ABJ') )
and rownum <= 10
;
rowid를 concatenation하면 결과에 오류 발생
select /*+ index(도서 도시명_idx) */
rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 like :book_nm || '%'
and 도서명 >= :last_book_nm
and lpad(도서명, 50) || rowid > lpad(:last_book_nm, 50) || :last_rid
and rownum <= 10
lpad(도서명, 50) || rowidtochar(rowid) > lpad(:last_book_nm, 50) || :last_rid
select greatest('b', 'a') from dual;
SQL>
SQL> select greatest('AAAH+WAAJAAAHxTAA9', 'AAAH+WAAJAAAHxTAA+') from dual;
GREATEST('AAAH+WAA
------------------
AAAH+WAAJAAAHxTAA9
SQL> select greatest( chartorowid('AAAH+WAAJAAAHxTAA9')
2 , chartorowid('AAAH+WAAJAAAHxTAA+') ) from dual;
GREATEST(CHARTOROW
------------------
AAAH+WAAJAAAHxTAA+
SQL>
인덱스를 스캔하면서 rowid를 발생하는 비효율
- rowid를 가지고 '=' 조건으로 바로 액세스할 댄 어떤 엑세스 보다 빠르지만 인덱스를 스캔하면서 rowid를 필터링 할때는 아니다.
- 인덱스 rowid는 리프 블록에만 있기때문에 이를 필터링하려면 일단 다른 액세스 조건만으로 리프 블록을 찾아가야 한다
- 인덱스 구성 : 도시명_idx on 도서(도서명)
select /*+ index(도서 도시명_idx) */
rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 like '오라클' || '%'
and 도서명 = '오라클 AATJENQO'
and rowid > 'AAAM8uAABAAAVT8ABJ'
- 인덱스 구성 : 도시명_idx on 도서(도서명,도서번호)
- 필터링 감소
- 결합인덱스는 불가능
create index 도시명_idx02 on 도서(도서명);
select /*+ index(도서 도시명_idx) */
rowid rid, 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 like '오라클' || '%'
and 도서명 = '오라클 AATJENQO'
and 도서번호 > 74703
SQL> create table t as select * from all_objects ;
SQL> insert into t select * from t;
SQL> insert into t select * from t;
SQL> insert into t select * from t;
SQL> insert into t select * from t;
SQL> select count(*) from t;
COUNT(*)
----------
799200
SQL> update t set object_id = rownum;
SQL> create index t_owner_idx on t(owner);
SQL> select /*+ index(t t_owner_idx) */ rowid
2 from t
3 where owner = 'SYS'
4 and rowid >= chartorowid('AAAM7MAABAAAUc5AAF')
5 and rownum <= 5 ;
ROWID
------------------
AAAM7MAABAAAUc5AAF
AAAM7MAABAAAUc5AAG
AAAM7MAABAAAUc5AAH
AAAM7MAABAAAUc5AAI
AAAM7MAABAAAUc5AAJ
Statistics
-------------------------------------------------------
0 recursive calls
0 db block gets
772 consistent gets <--
0 physical reads
0 redo size
554 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
SQL> set autotrace off
SQL> create index t_owner_idx2 on t(owner, object_id);
인덱스가 생성되었습니다.
SQL> select * from (
2 select object_id oid from t where owner = 'SYS' order by rowid desc
3 ) where rownum <= 5 ;
OID
----------
799200
799199
799198
799197
799196
SQL> set autotrace on statistics
SQL> select /*+ index(t t_owner_idx2) */ object_id
2 from t
3 where owner = 'SYS'
4 and object_id >= '799196'
5 and rownum <= 5 ;
OBJECT_ID
----------
799196
799197
799198
799199
799200
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
154 consistent gets <--
0 physical reads
0 redo size
497 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
(7) Between과 like 스캔 범위 비교
--select max(고객번호) from 월별고객별판매집계
SQL> insert into 월별고객별판매집계(고객번호, 판매월, 판매구분, 판매액)
2 select 1200000 + rownum as 고객번호
3 , decode(판매월, '200801', '200901', '200802', '200902') as 판매월
4 , 판매구분
5 , 판매액
6 from 월별고객별판매집계
7 where 판매월 between '200801' and '200802';
SQL> set autotrace on statistics
-- 쿼리 1
SQL> select /*+ index(t idx_월별고객별판매집계_02) */ count(*)
2 from 월별고객별판매집계 t
3 where 판매월 between '200901' and '200902'
4 and 판매구분 = 'A';
COUNT(*)
----------
16667
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
86 consistent gets <--
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- 쿼리 2
SQL> select /*+ index(t idx_월별고객별판매집계_02) */count(*)
2 from 월별고객별판매집계 t
3 where 판매월 like '2009%'
4 and 판매구분 = 'A';
COUNT(*)
----------
16667
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
88 consistent gets <--
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-- 쿼리 3
SQL> select /*+ index(t idx_월별고객별판매집계_02) */ count(*)
2 from 월별고객별판매집계 t
3 where 판매월 >= '200901'
4 and 판매월 < '200903'
5 and 판매구분 = 'A';
COUNT(*)
----------
16667
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
88 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off statistics;
테이타 역전 한거임
SQL> create table 월별고객별판매집계2 as
2 select rownum 고객번호
3 , '2008'||lpad(ceil(rownum/100000),2,'0') 판매월
4 , decode(mod(rownum, 12), 1, 'B','A') 판매구분
5 , round(dbms_random.value(1000,100000), -2) 판매액
6 from dual
7 connect by level <= 1200000;
SQL> insert into 월별고객별판매집계2(고객번호, 판매월, 판매구분, 판매액)
2 select 1200000 + rownum as 고객번호
3 , decode(판매월, '200801', '200901', '200802', '200902') as 판매월
4 , 판매구분
5 , 판매액
6 from 월별고객별판매집계2
7 where 판매월 between '200801' and '200802';
200000 개의 행이 만들어졌습니다.
SQL> create index idx_월별고객별판매집계2_02 on 월별고객별판매집계2( 판매월, 판매구분);
--커리 4
SQL> select /*+ index(t idx_월별고객별판매집계2_02) */ count(*)
2 from 월별고객별판매집계2 t
3 where 판매월 between '200901' and '200902'
4 and 판매구분 = 'B';
COUNT(*)
----------
16667
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
384 consistent gets
304 physical reads
0 redo size
426 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--커리 5
SQL> select /*+ index(t idx_월별고객별판매집계2_02) */count(*)
2 from 월별고객별판매집계2 t
3 where 판매월 like '2009%'
4 and 판매구분 = 'B';
COUNT(*)
----------
16667
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
639 consistent gets
255 physical reads
0 redo size
426 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--커리 6
SQL> select /*+ index(t idx_월별고객별판매집계2_02) */ count(*)
2 from 월별고객별판매집계2 t
3 where 판매월 between '200900' and '200902'
4 and 판매구분 = 'B';
COUNT(*)
----------
16667
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
639 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--커리 7 결과 집합이 다름
select /*+ index(t idx_월별고객별판매집계2_02) */count(*)
from 월별고객별판매집계2 t
where 판매월 like '200901%'
and 판매구분 = 'B';
--커리 8 결과 집합이 다름
select /*+ index(t idx_월별고객별판매집계2_02) */count(*)
from 월별고객별판매집계2 t
where 판매월 = '200901'
and 판매구분 = 'B';
데이타 역전된거 아님
--커리 9
SQL> select /*+ index(t idx_월별고객별판매집계_02) */ count(*)
2 from 월별고객별판매집계 t
3 where 판매월 between '200901' and '200902'
4 and 판매구분 = 'B';
COUNT(*)
----------
183333
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
465 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--커리 10
SQL> select /*+ index(t idx_월별고객별판매집계_02) */count(*)
2 from 월별고객별판매집계 t
3 where 판매월 like '2009%'
4 and 판매구분 = 'B';
COUNT(*)
----------
183333
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
466 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--커리 11
SQL> select /*+ index(t idx_월별고객별판매집계_02) */ count(*)
2 from 월별고객별판매집계 t
3 where 판매월 between '200900' and '200902'
4 and 판매구분 = 'B';
COUNT(*)
----------
183333
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
466 consistent gets
0 physical reads
0 redo size
426 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
범위검색 조건의 스캔 시작점 결정 원리
- 범위검색 조건 뒤를 따르는 조건절은 스캔 범위를 줄이는 데에 영향을 미칠 수도 있고 그렇지 않을수도 있다.
- between이 like보다 더 넓은 범위를 스캔하는 경우는 없으므로 가급적 between을 사용하기 바란다는 내용
- 쿼리 5 처럼 라이크 조건으로 검색할 때는 판매월 = '2009'이고 판매구분='B'인 레코드를 수직적 탐색
- 쿼리 4 처럼 라이크 조건으로 검색할 때는 판매월 = '200901'이고 판매구분='B'인 레코드를 수직적 탐색
(8) 선분이력의 인덱스 스캔효율
- 항상 두 개의 부등호 조건을 함께 사용하는 선분이력에서 데이터 특성상 두 번재 부등호 조건이 스캔 범위를 줄이는데 전혀 도움안됨
선분이력이란?
- 고객의 변경이력을 관리할 때 이력의 시작시점만을 관리하는 것을 '점이력' 모델이라고 하고,
시작시점과 종료시점을 함께 관리하는 것을 '선분이력' 모델이라고 한다. - 점이력 : 고객번호 + 변경일자
- 선분이력 : 고객번호 + 시작일자 + 종료일자
--선분이력
select a.고객번호, a.고객명, b.연락처, b.주소, b.연체금액, b.연체개월수
from 고객 a, 고객별연체금액 b
where a.고객번호 = '123'
and b.고객번호 = a.고객번호
and '20090505' between b.시작일 and b.종료일
--점이력
select a.고객번호, a.고객명, b.연락처, b.주소, b.연체금액, b.연체개월수
from 고객 a, 고객별연체금액 b
where a.고객번호 = '123'
and b.고객번호 = a.고객번호
and b.연체변경일자 = (select max(연체변경일자)
from 고객별연체금액
where 고객번호 = a.고객번호
and 변경일자 <= '20090505'
)
- 선분이력 : 쿼리가 간단하면 아무래도 성능상 유리할 때가 많다. 단점은 객체 무결성을 사용자가 직접 관리해야 함(선분이력의 개체 무결성)
고객번호 + 시작일자 or 고객번호 + 종료 : PK 중복은 피할수 있으나 변경이 불가피 또한 선분 중복이 발생
고객번호 + 시작일자 + 종료일자 : 객체무결성 확보가 불가피 하므로 인덱스 스캔이라도 높이기 위해 PK생성
고객번호 + 시작일자 or 고객번호 + 종료 로 PK생성 후 insert/update 잘 관리하여 PK변경 방지 후 고객번호 + 시작일자 + 종료일자 인덱스 생성 - 점이력 : PK 제약설정만으로 객체무결성 확보가능
선분이력 기존 조회 패턴
select 연체개월수, 연체금액
form 고객별연체금액
where 고객번호 = '123'
and :dt between 시작일 and 종료일
--현시점을 조회 맨마지막 이력 레코드는 종료일에 '99991231' 넣는다는 약속
select 연체개월수, 연체금액
form 고객별연체금액
where 고객번호 = '123'
and 종료일 = '99991231'
--선분이력 테이블에 정보를 미리 입력해 두는 경우
select 연체개월수, 연체금액
form 고객별연체금액
where 고객번호 = '123'
and to_char(sysdate, 'yyyymmdd') between 시작일 and 종료일
- 선분이력을 미리 립력해 두는 흔한 사례 : 상품 가격 정보가 익일 0시부터 바뀐다고 할 대, 가격을 변경하는 Job을 걸어 놓더라도
정확히 12시에 트리거링된다는 보장이 없다.
그뿐만 아니라 update가 시작되고 커밋되기 까지 그 짧은 순간에 진행된 주문 트랜잭션은 이전 가격을 기준으로 거래대 데이터 일관성에 문제가
생길수 있다.
고객번호 + 시작일 + 종료일구성일 때 최근 시점 조회
select *
from 고객별연체금액
where 고객번호 = '123'
and '20050131' between 시작일 and 종료일
--최종 레코드
{code:sql}
select /*+ index_desc(a idx_x01)*
from 고객별연체금액 a
where 고객번호 = '123'
and '20050131' between 시작일 and 종료일
and ronum <= 1
고객번호 + 시작년 + 시작일 + 종료일구성일 때 최근 시점 조회
select *
from 고객별연체금액
where 고객번호 = '123'
and 시작년 = substr('20050131',1,4 )
and '20050131' between 시작일 and 종료일
--최종 레코드
{code:sql}
select /*+ index_desc(a idx_x02)*
from 고객별연체금액 a
where 고객번호 = '123'
and 시작년 = substr('20050131',1,4 )
and '20050131' between 시작일 and 종료일
and ronum <= 1
고객번호 + 시작일 + 종료일구성일 때 과거 시점 조회
select /*+ index_desc(a idx_x01)*
from 고객별연체금액 a
where 고객번호 = '123'
and '20020930' between 시작일 and 종료일
and ronum <= 1
고객번호 + 종료일 + 시작일구성일 때 최근 시점 조회
select *
from 고객별연체금액 a
where 고객번호 = '123'
and '20050131' between 시작일 and 종료일
and ronum <= 1
고객번호 + 종료일 + 시작일구성일 때 과거 시점 조회
select *
from 고객별연체금액 a
where 고객번호 = '123'
and '20020930' between 시작일 and 종료일
and ronum <= 1
중간 시점 조회
-- 고객 + 시작일 + 종료일
select /*+ index_desc( a idx_x01)*/ *
from 고객별연체금액 a
where 고객번호 = '123'
and '20031010' between 시작일 and 종료일
and ronum <= 1
-- 고객 + 종료일 + 과거일
select *
from 고객별연체금액 a
where 고객번호 = '123'
and '20031010' between 시작일 and 종료일
and ronum <= 1
Acces Predicae 와 Filter Predicate
SQL> create index emp_x01 on emp(deptno, job, sal, ename, mgr, comm);
인덱스가 생성되었습니다.
SQL> set autotrace traceonly explain;
SQL>
SQL> select /*+ ordered use_nl(e) index(e emp_x01) */ *
2 from dept d, emp e
3 where d.loc = 'CHICAGO'
4 and e.deptno = d.deptno
5 and e.job like 'SALE%'
6 and e.job between 'A' and 'Z'
7 and e.sal >= 1000
8 and e.ename like '%A%'
9 and trim(e.ename) = 'ALLEN'
10 and e.comm >= 300
11 and to_char(e.hiredate, 'yyyymmdd') like '198102%' ;
Execution Plan
----------------------------------------------------------
Plan hash value: 346692358
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 57 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR(INTERNAL_FUNCTION("E"."HIREDATE"),'yyyymmdd') LIKE '198102%')
3 - filter("D"."LOC"='CHICAGO')
4 - access("E"."DEPTNO"="D"."DEPTNO" AND "E"."JOB" LIKE 'SALE%' AND
"E"."SAL">=1000 AND "E"."COMM">=300 AND "E"."SAL" IS NOT NULL)
filter("E"."ENAME" LIKE '%A%' AND "E"."JOB" LIKE 'SALE%' AND TRIM("E"."ENAME")='ALLEN' AND "E"."JOB">='A' AND
"E"."JOB"<='Z' AND
"E"."SAL">=1000 AND "E"."COMM">=300)
SQL>
- 1. 인덱스 단계에서의 Access Predicate(id = 4 access 부분) : 인덱스 스캔 범위를 결정하는 데에 영향을 미치는 조건절
- 2. 인덱스 단계에서의 Filter Predicate(id = 4 filter 부분) : 테이블로의 액세스 여부를 결정짓는 조건절
- 3. 테이블 단계에서의 Filter Predicate(id = 1 filter 부분) : 테이블을 액세스하고 나서 최종 결과집합으로의 포함여부를 결정짓는 조건절
- 4. 테이블 단계에서의 Filter Predicate(id = 3 filter 부분) : 인덱스를 경유하지 않고 테이블 전체를 스캔할 때
(10) index fragmentation
- 오라클에서 인덱스 불균형이 절대 발생하지않는다.
- B*Tree : B는 Balanced의 약자로서, 인덱스 루트에서 리프 블록까지 어떤 값으로탐색하더라고 읽는 블록 수는 같음.(루트에서 리프 블록까지 높이가 동일)
Index Skew
- 인덱스 엔트라가 왼쪽 또는 오른쪽으로 치우치는 현상을 말함.
- 대량의 데이타 삭제 하면 브렌치 블럭은 그대로있구 리프블럭은 프리 리스트 상태가 됨.
- 데이타가 들어오면 재사용 됨
- 새로운 값이 하나라도 입력되기 전 다른 노드에서 인덱스 분할이 발생하면 그것을 위해서라도 재사용된다. 이때는 상위 브랜치
에서 해당 리프 블록을 가리크는 엔트리가 제거돼 다른 쪽 브랜치의 자식 노드로 이동하고, freelist에서 제거된다.
SQL> create table t as select rownum no from dual connect by level <= 1000000 ;
테이블이 생성되었습니다.
SQL> create index t_idx on t(no) pctfree 0;
인덱스가 생성되었습니다.
SQL> delete from t where no <= 500000 ;
500000 행이 삭제되었습니다.
SQL> commit;
커밋이 완료되었습니다.
SQL> set autotrace on statistics
SQL> select * from t where no > 0 and rownum <= 1;
NO
----------
500001
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
3159 consistent gets <-- delete 직후
5 physical reads
0 redo size
420 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
delete from 일별고객판매집계 where 판매일시 < trunc(sysdate) - 2;
insert into 일별고객판매집계
select to_char(sysdate, 'yyyymmdd'), 고객번호, sum(판매량), sum(판매금액);
from 판매
where 판매일시 between trunc(sysdate) and trunc(sysdate+1) -1/24/60/60
group by 고객번호;
commit;
- 커밋이전이라 freelist로 반환 될 수 없다.
- 바로 인서트 과정으로 빈 블록이 많이 필요함에도 앞서 지원 블록들을 사용할 수 없어 새로운 공간을 할당 받게됨
- 인덱스 스캔 효율이 떨어진다.
!1-53.JPG!
{code:sql}
delete from 일별고객판매집계 where 판매일시 < trunc(sysdate) - 2;
commit;
insert into 일별고객판매집계
select to_char(sysdate, 'yyyymmdd'), 고객번호, sum(판매량), sum(판매금액);
from 판매
where 판매일시 between trunc(sysdate) and trunc(sysdate+1) -1/24/60/60
group by 고객번호;
commit;
!1-53.JPG!
{code:sql}
insert into 일별고객판매집계
select to_char(sysdate, 'yyyymmdd'), 고객번호, sum(판매량), sum(판매금액);
from 판매
where 판매일시 between trunc(sysdate) and trunc(sysdate+1) -1/24/60/60
group by 고객번호;
commit;
delete from 일별고객판매집계 where 판매일시 < trunc(sysdate) - 2;
commit;
Index Sparse
- 인덱스 전반에 걸쳐 밀도가 떨어지는 현상
SQL> create table t as select rownum no from dual connect by level <= 1000000 ;
SQL> create index t_idx on t(no) pctfree 0 ;
SQL> select /*+ index(t) */ count(*) from t where no > 0;
COUNT(*)
----------
1000000
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
2083 consistent gets
2000 physical reads
0 redo size
424 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> delete from t where mod(no, 10) < 5 ;
SQL> commit;
SQL> select /*+ index(t) */ count(*) from t where no > 0;
COUNT(*)
----------
500000
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2001 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
- Index Skew처럼 블록이 아예 텅 비면 곧바로 freelist로 반환돼 재사용이 가능하지만.
- Index Sparse는 지원진 자리에 새로운 값이 들어오지않으면 영영 재상용되지 않을 수도 있다.
- 총 레코드 건수가 일정한데도 인덱스 공간 사용량이 개속 커지는 것은 대개 이런경우다.
Index Rebuild
- Fragmentation 때문에 인ㄷ게스 크기가 계속 증가하고 스캔 효율이 나쁠 때는 아래와 같이 coalesce명령을 수행해 주면 된다.
SQL> alter index t_idx coalesce;
SQL>
SQL> select /*+ index(t) */ count(*) from t where no > 0;
COUNT(*)
----------
500000
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1002 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
- coalesce 명령을 수행하면 인덱스 분할과 반대의 작업이 일어난다. 즉 여러 인덱스 블록을하나로 병합하고,
그 결과로 생긴 빈 블록들을 freelist에 반환한다. - coalesce 명려을 수행해도 인덱스 세그먼트에 할당된 미사용 공간(HWM 아래쪽에서 freelist에 등록된 블록과 HWM 위쪽 미사용 블록)
은 반환되지 않는다. - Index Fragmentation를 해소하면서 공간까지 반환하려면 아래와 같이 shrink 명령을 수행하면 된다.(ASSM 서만 가능 )
alter index t_idx shrink space
-- coalsece 같은 명령어됨..
alter index t_idx shrink space compact
--coalesce나 shrink는 레코드를 건건이 지워다가 다시 입력하는 방식을 사용하므로 작업량이 많을때는 rebuild명령을 사용하는 편이 나을수도있다.
alter index t_idx rebuild;
alter index t_idx rebuild online;