by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

7. 인덱스 스캔 효율




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명령을 수행해 주면 된다.
    Unable to find source-code formatter for language: slq. Available languages are: actionscript, html, java, javascript, none, sql, xhtml, xml
    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;
    

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.