메뉴 건너뛰기

bysql.net

7. 인덱스 스캔 효율

2011.03.08 18:45

휘휘 조회 수:16546

I/O 튜닝의 중요점

  1. sequential 액세스의 선택도를 높이다
  2. random 액세스 발생량을 줄인다


(1) 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성

  • 인덱스: 같은 값을 갖는 레코드( = ) 들이 항상 서로 군집
  • 첫 번째 나타나는 범위검색 조건까지만 만족하는 인덱스 레코드는 모두 연속되게 모여 있지만,
  • 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.


(2)인덱스 선행 컬림이 등치(=) 조건이 아닐 때 발생하는 비효율

  • 인덱스 컬럼중 일부가 조건절에서 생략되거나 ‘=’ 조건이 아니더라도, 그것이 뒤쪽 컬럼일대는 비효율이 없다.
  • 선행컬림이 조건절에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스 스캔단계에서 비효율이 발생




select * from 매물아파트매매
where 아파트세세코드=’a0002’
and 평형=’59’
and 평형타입=’A’
and 인터넷매물 between ‘1’ and ‘2’
order by 입력일 desc


인덱스:  아파트 시세+평형+평형타입+인테냇 매물

아파트 시세코드     평형     평형타입     인터넷 매물

a0001             15         A         0
a0001             21         B         1
a0002             59         A         1   
a0002             59         A         2
a0003             22         A         0
a0003             59         D         3
a0004             23         A         1
a0004             33         D         2


인덱스 선행 컬럼이 모두 ‘=’ 조건일때는 필요한 범위만 스캔


인덱스:  인테넷 매물+아파트 시세+평형+평형타입

인터넷 매물     아파트 시세코드     평형     평형타입    

0        a0001             15         A    
0        a0003             22         A    
1        a0001             21         B    
1        a0002             59         A        
       a0004             23         A    
1        a0004             33         D   
2        a0002             59         A    
3        a0003             59         D    



선두컬럼이 BETWEEN 이므로 결과값은 흩어져 있게되어 조건에 만족하지 않는 레코드 까지 스캔하고 서 버려야 하는 비효율이 발생


  • 인덱스 스캔의 시작점 탐색시 인터넷 매물=1 AND 시세 <’A002’ AND 평형 <’59’ AND 평형타입 <’A’ 로
    필터링 후 스캔시작 종료역시 조건 값에 따라 필터링후 스캔 종료





(3) BETWEEN 조건을 IN-List 로 바뀌었을 때 인덱스 스캔 비율


  • IN 으로 변경
    (2)의 예중 인터넷 매물 을 in 조건으로 바꾸면



select * from 매물아파트매매
where 아파트세세코드=’a0002’
   and 평형=’59’
   and 평형타입=’A’
   and 인터넷매물 in (‘1’, ‘2’)
order by 입력일 desc



  • 실행계획상 INLIST ITERATOR 가 발생하게된다
  • 이것은 IN 조건을 = 조건으로 변경하여 UNION ALL 한것과 동일하므로
  • ‘=’ 조건으로 스캔이 일어나게 되어 성능향상의 효과를 볼수 있다.


  • BETWEEN 조건을 IN-LIST 조건으로 바꿀 때  주의 사항

  • IN-LIST 개수가 많지 않아야 함
    • 범위 스캔의 비효율은 사라지지만 인덱스 수직 탐색이 여러번 발생
  • 인덱스 스캔과정에서 선택되는 레코드들이 서로 멀리 떨어져 있을때만 유용
    • BETWEEN 조건의 선행컬럼이 많은 인덱스 리프블록을 스캔하더라도 I/O면에서는 소량일 경우가 많음
      (인덱스 리프블록은 매우 많은 레코드가 담김)



(4) Index Skip Scan을 이용한 비효율 해소


  • 인덱스 선두 컬림이 누락됐을 때뿐만 아니라 부등호,BETWEEN,like 같은 범위검색 조건일때도 유용하게 사용



자료 생성
create table month_sales_sum
as
select rownum cno
,'2008' || lpad(ceil(rownum/100000),2,'0') month
,decode(mod(rownum,12),1,'A','B') styp
,round(dbms_random.value(1000,100000),-2) samt
from dual
connect by level<=1200000;

create index month_sales_sum_idx1 on month_sales_sum(styp,month);
create index month_sales_sum_idx2 on month_sales_sum(month,styp);

각 조건별 결과
SQL> select count(*)
 2  from month_sales_sum t
 3  where styp='A'
 4 and month between '200801' and '200812';

 COUNT(*)
----------
    100000


Execution Plan
----------------------------------------------------------
Plan hash value: 3159671310

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |    10 |   292   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE   |                      |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM_IDX1 |   111K|  1086K|   292   (2)| 00:00:04 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("STYP"='A' AND "MONTH">='200801' AND "MONTH"<='200812')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        28  recursive calls
         0  db block gets
       362  consistent gets
       601  physical reads
         0  redo size
       411  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL>



idx1 사용

  • cost: 292
  • consistent gets: 362 




SQL> select /*+ index ( t MONTH_SALES_SUM_IDX2 ) */ count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month between '200801' and '200812';

 COUNT(*)
----------
    100000


Execution Plan
----------------------------------------------------------
Plan hash value: 1604684706

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |    10 |  3473   (2)| 00:00:42 |
|   1 |  SORT AGGREGATE   |                      |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM_IDX2 |   111K|  1086K|  3473   (2)| 00:00:42 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<='200812')
      filter("STYP"='A')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         4  recursive calls
         0  db block gets
      3169  consistent gets
      3089  physical reads
         0  redo size
       411  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL>

idx2 사용

  • cost: 3473
  • consistent gets: 3169 



SQL> select  count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month in ('200801','200802','200803','200804','200805','200806','200807','200808','200809','200810','200811','200812');


 COUNT(*)
----------
    100000


Execution Plan
----------------------------------------------------------
Plan hash value: 2540619797

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                      |     1 |    10 |   284   (2)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                      |     1 |    10 |            |          |
|   2 |   INLIST ITERATOR  |                      |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| MONTH_SALES_SUM_IDX1 |   111K|  1086K|   284   (2)| 00:00:04 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("STYP"='A' AND ("MONTH"='200801' OR "MONTH"='200802' OR
             "MONTH"='200803' OR "MONTH"='200804' OR "MONTH"='200805' OR "MONTH"='200806' OR
             "MONTH"='200807' OR "MONTH"='200808' OR "MONTH"='200809' OR "MONTH"='200810' OR
             "MONTH"='200811' OR "MONTH"='200812'))

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         4  recursive calls
         0  db block gets
       393  consistent gets
         0  physical reads
         0  redo size
       411  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL>

in-list 로 변경 

  • cost: 284
  • consistent gets: 393 




SQL> select /*+ index_ss ( t MONTH_SALES_SUM_IDX2 ) */ count(*)
2 from month_sales_sum t
3 where styp='A'
4 and month between '200801' and '200812';

 COUNT(*)
----------
    100000


Execution Plan
----------------------------------------------------------
Plan hash value: 3665529068

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |     1 |    10 |   195  (26)| 00:00:03 |
|   1 |  SORT AGGREGATE  |                      |     1 |    10 |            |          |
|*  2 |   INDEX SKIP SCAN| MONTH_SALES_SUM_IDX2 |   111K|  1086K|   195  (26)| 00:00:03 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<='200812')
      filter("STYP"='A')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         4  recursive calls
         0  db block gets
       379  consistent gets
         8  physical reads
         0  redo size
       411  bytes sent via SQL*Net to client
       384  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_ss 사용

  • cost: 195
  • consistent gets : 379  




  • idx1 사용
    • cost 292
    • consistent gets 362 
  • idx2 사용
    • cost 3473
    • consistent gets 3169
  • in-list 사용 (idx2)
    • cost 284
    • consistent gets 393
  • index_ss 사용  (idx2)
    • cost 195
    • consistent gets 379 




(5) 범위검색 조건을 남욜할 때 발생하는 비효율

* like 조건을 기준으로 설명



쿼리1

select 고객id, 상품명, 지역...
from 가입상품
where 회사 = :com
and 지역 =:reg
and 상품명 like :prod || ‘%’;

쿼리2
select 고객 id, 상품명, 지역...
from 가입상품
where 회사 =:com
and 상품명 like :prod || ‘%’;

쿼리3
select 고객 id, 상품명, 지역,...
from 가입상품
where 회사=:com
and 지역 like ;reg || ‘%’
and 상품명 like :prod ||’%’




조건1
회사=’C70’
지역=’02’
상품=’보급'

조건2
회사=’C70’
상품=’보급'

인덱스
회사 + 지역 + 상품명






회사

지역

상품명

쿼리1

쿼리2

쿼리3

쿼리3




조건1

조건2

조건1

조건2

C60

03

일반




 

C70

02

고급




 

C70

02

보급1

V

V

V

V

C70

02

보급2

V

V

V

V

C70

02

일반

V

V

V

V

C70

02

일반


V

V

V

C70

02

일반


V

V

V

C70

02

일반


V

V

V

C70

02

일반


V

V

V

C70

03

고급


V

V

V

C70

03

보급1


V


V

C80

02

고급

 

V

 

V


  • 쿼리3의 조건 1과 쿼리 1의 조건 1을 비교해보면 LIKE 연산을 사용함으로서 스캔 범위가 늘어남을 볼수 있으므로
  • 무분별한 LIKE 조건의 사용을 자제 하는것이 좋다.


(6) 같은 컬럼에 두개의 범위검색 조건 사용 시 주의 사항

  • 도서 table에 도서명으로 index가 있을경우

WHERE 도서명 like :book_nm || ‘%’

and 도서명 > :last_book_nm


의경우  book_nm으로  도서명으로 처음부터 스캔하다가
:last_book_nm보다 큰 레코드를 찾고서 stop


WHERE rtim(도서명) like :book_nm || ‘%’
and 도서명 > :last_book_nm


부등호 조건을 만족하는 첫번재 레코드 부터 스캔을 시작


or-expansion을 이용하는 방법과 주의 사항

  • use_concat 힌트를 사용하면 or 조건에 대한 expansion(union all 분기) 이 일어나게 되어 union all을 사용할때보다
    sql코딩량을 줄일수 있음
  • 주의 점
  • 9i 까지
    •   i/o 비용 모델, cpu비용모델을 불문하고 뒤쪽 조건을 먼저 실행
  • 10g 부터
    •  cpu비용 모델에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행
      뒤쪽조건을 먼저 처리하도록 하려면 ordered_predicates 힌트를 명시해야함
  • rowid 를 concatenation하면 결과에 오류 발생
lpad (도서명,50) || rowid > lpad (:last_book_nm,50) || :last_rid and rownum<=10;


을 사용하면 내부적으로 아래와 같은 형태로 변환이 일어난다.


lpad(도서명,50)||rowidtochar(rowid) > lpad(:last_book_nm,50) || :last_rid


하지만 이과정에서 order by등 정렬시 문자형과 rowid형의 크기값이 달라
원하지 않는 결과가 나올수 있으므로 주의가 필요하다.   




SQL> select greatest('AAAH+WAAJAAAHxTAA9','AAAH+WAAJAAAHxTAA+') from dual;

GREATEST('AAAH+WAA
------------------
AAAH+WAAJAAAHxTAA9

SQL> select greatest(chartorowid('AAAH+WAAJAAAHxTAA9'),chartorowid('AAAH+WAAJAAAHxTAA+')) from dual;

GREATEST(CHARTOROW
------------------
AAAH+WAAJAAAHxTAA+

SQL>






인덱스를 스캔하면서 rowid를 필터링 할 때 발생하는 비효율

도서명 = :last_book_nm
and rowid > :last_rid


위 조건에서 인덱스 rowid는 리프 블록에만 있기때문에 이를 필터링하려면
다른 액세스 조건만(도서명) 으로 리프 블록을 찾아가야하며 이때 같은 도서명을 가진 레코드가
많을 경우 비효율이 발생할수 있다



(7) Between 과 Like 스캠 범위 비교




SQL> create index month_sales_sum2_idx2 on month_sales_sum2(month,styp);

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> select  /*+ index( t month_sales_sum2_idx2 ) */ count(*)
2 from month_sales_sum2 t
3 where month between '200801' and '200802'
4 and styp='A';
 COUNT(*)
----------
    16667


Execution Plan
----------------------------------------------------------
Plan hash value: 1365354869

-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |     1 |    10 |   590   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE   |                       |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM2_IDX2 | 17237 |   168K|   590   (2)| 00:00:08 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<='200802')
      filter("STYP"='A')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
       304  consistent gets
       304  physical reads
         0  redo size
       413  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> select  /*+ index( t month_sales_sum2_idx2 ) */  count(*)
from month_sales_sum2 t
where month like '2008%'
and styp='A';  2    3    4

 COUNT(*)
----------
    16667


Execution Plan
----------------------------------------------------------
Plan hash value: 1365354869

-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |     1 |    10 |   590   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE   |                       |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM2_IDX2 | 17237 |   168K|   590   (2)| 00:00:08 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH" LIKE '2008%' AND "STYP"='A')
      filter("STYP"='A' AND "MONTH" LIKE '2008%')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
       560  consistent gets
       256  physical reads
         0  redo size
       413  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL> select /*+ index( t month_sales_sum2_idx2 ) */ count(*)
from month_sales_sum2 t
where month >= '200801'
and month < '200803'
and styp='A';
 2    3    4    5
 COUNT(*)
----------
    16667


Execution Plan
----------------------------------------------------------
Plan hash value: 1365354869

-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |     1 |    10 |   590   (2)| 00:00:08 |
|   1 |  SORT AGGREGATE   |                       |     1 |    10 |            |          |
|*  2 |   INDEX RANGE SCAN| MONTH_SALES_SUM2_IDX2 | 17237 |   168K|   590   (2)| 00:00:08 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("MONTH">='200801' AND "STYP"='A' AND "MONTH"<'200803')
      filter("STYP"='A')

Note
-----
  - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
       560  consistent gets
         0  physical reads
         0  redo size
       413  bytes sent via SQL*Net to client
       384  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

SQL>




  • between 연산자의 쿼리1은 2008년 1월 데이터를 모두 읽고 2월 데이터는 구분 ‘A’에서 멈춤

  • like 는 둘다 모두 읽음

  • 쿼리3의 등호 역시 like와 같은 스캔량을 보임



범위 검색 조건의 스캔 시작점 결정 원리

  • 실제 사용에서는 상황에 따라다르지만 적어도 between이 like보다
    더 넓은 범위를 스캔하지는 않으므로 between을 사용하는것이 유리하다.


  • 인덱스 스캔
    •  브랜치 블록에서 뒤쪽 엔트리부터 스캔을 시작해 비교값보다 작은 값을 만나는 순간에 가리키는 하위 노드로 이동
  • like 조건으로 검색할경우 ‘판매월=’2009’이고 판매구분=’B’인 레코드를 목표로 수직적탐색
    • 하위노드를 탐색하다가 ’2009’보다 작은  ‘2008’||’12’||’B’레코드가 담긴 마지막 리프블록에 도달한후 멈춤
  • between 일때는 ‘판매월=’200901’ 이고 판매구분 =’B;인 레코드를 목표로 수직적 탐색
    • 하위노드를 탐색하다가 ‘200901’||’A’가 담긴 마지막 리프블록에 도달한후 멈춤
  • like라도 인덱스에서 실제 찾아지는값 (‘200801’) 을 입력하면 스캔 범위를 줄일수 있으면
  • between 이라도 실제 없는 값 (‘200800’) 으로 조회하면 like와 같은 스캔량을 가지게 됨


(8) 선분이력의 인덱스 스캔 비율



선분이력이란?

  • 점이력
    • 이력의 시작점만을 관리하는 모델 {고객번호+변경일자}
  • 선분이력
    • 시작지점과 종료시점을 함께 관리 {고객번호+시작일자+종료일자}
      최근 이력의  마지막 종료일이 없으므로 종료일은 ‘99991231’




  • 장점
    • 선분형태로 관리하면 between 조인을 이용하여 간단하게 이력시점을 조회할수 있다.
    • ‘20090505’ between b.시작일 and b.종료일 형태인 간단한 쿼리로 처리할수 있어  성능상 유리해질수도 있다
  • 단점
    • 이력이 추가될때마다 최종 이력의 종료일자도 같이 변경해 주어야함으로 DML성능이나
      데이터 관리 프로그램등이 복잡해질수 있다(과거이력 일괄 보정)
    • 개체 무결성을 사용자가 직접 관리해 주어야 함


선분이력 기본 조회 패턴



SELET 연체개월수, 연체금액
FROM 고객별연체금액
where 고객번호 = ‘123’
and :dt between 시작일 and 종료일




  • 현 시점은 ‘99991231’등으로 두어야 함으로 현시점을 검색할때도
  •  and 종료일=’99991231’   만으로 검색가능
  • 하지만 자료가 미리 등록되어있는 등의 경우 ( 가격변경등) 에는 to_char(sysdate,’yyyymmdd’)등을 활용하여야 한다.


{시작일+종료일} 구성일 때 최근 시점 조회


where 고객번호=’123’
and ‘20050131’ between 시작일 and  종료일


을 풀어쓰면


where 고객번호 =’123’
and 시작일 <=’20050131’
and 종료일 >=’20050131’


  • 이 되어 인덱스 스캔시 = 조건을 찾은후 시작일 컬럼의 <= 을 모두 스캔하고 종료일 >=  조건을 모두 스캔하므로
  • 두 조건 모두 범위를 줄이는데 기여하지 못하게 된다.
  • 하지만 두 조건 모두 index 컬럼에 포함되어있어 테이블 random 액세스 없이 필터링 할수 있지만 이력이 많은 고객일경우
  • 한건을 스캔하기 위해 많은 인덱스를 스캔해야 한다.
  • 그럴경우  index_desc 힌트에 rownum<=1 조건을 추가해주만 index를 거꾸로 스캔하면 빠르게 찾을수 있다.


{시작일+종료일} 구성일 때 과거 시점 조회

  • index_desc 힌트를 주고 rownum<=1 조건을 추가해 주면 스캔범위를 한건으로 줄일수 있음



{종료일+시작일} 구성일 때 최근 시점 조회

 

  • index:  { 고객번호+종료일+시작일 }


where 고객번호=’123’
and ‘20050131’ between 시작일 and  종료일


  • = 조건 다음 종료일이 >= 이므로 2005년 1월 31일 이후 이력 레코드를 모두 스캔해야하지만 
  • rownum<=1 조건을 추가해 준다면 스캔범위를 1건으로 줄일수 있음

 


{종료일+시작일} 구성일때 과거 시점 조회


where 고객번호=’123’
and ‘20020930’ between 시작일 and  종료일


  • ‘=’ 조건 다음 종료일 >= 조건이므로 2002년 9월 30일 이력을 모두 스캔하여야 하지만
  • rownum<=1 조건을 추가해 준다면 1건으로 줄일수 있음

중간시점 조회

  • 중간시점 역시 위와 같이 인덱스 구성에따라
  • index_desc 힌트 + rownum<=1 을 사용하거니 rownum<=1을 사용하면 빠르게 찾을수 있음


선분 이력 스캔 효율을 높이는 방법 요약

  • 최근데이터를 주로 조회한다면 {종료일+시작일}
  • 과거데이터를 주로 조회한다면 {시작일+종료일}  순으로 구성하는 것이 효과적

  • {시작일+종료일} 일 경우 index_desc 힌트와 rownum<=1조건을 추가
  • {종료일+시작일} 일 경우 rownum<=1 조건만 추가해주면 된다

 

실험  - 김범석 추가


CREATE TABLE IDX_DESC_TEST ( USER_ID VARCHAR2(20), STT_DTM DATE, END_DTM DATE);

 

COMMIT;


DECLARE
    I NUMBER := 1;
    J NUMBER := 1;
    BEGIN
        WHILE I < 500001 LOOP
        INSERT INTO IDX_DESC_TEST  SELECT '1'|| LPAD(MOD( I, 2) + 1, 5, '0') AS USER_ID, TO_DATE(SYSDATE, 'YY-MM-DD') +  I AS STT_DTM, TO_DATE(SYSDATE, 'YY-MM-DD') + (I + J) AS END_DTM FROM DUAL;
        I := I + 1;
        END LOOP;
    END;
/

 

COMMIT;

 

-- 1.인덱스 => 고객번호 + 시작일 + 종료일
CREATE INDEX IDX_ID_STT_END ON IDX_DESC_TEST(USER_ID, STT_DTM, END_DTM);

-- 2.인덱스 => 고객번호 + 종료일 + 시작일
CREATE INDEX IDX_ID_END_STT ON IDX_DESC_TEST(USER_ID, END_DTM, STT_DTM);

 

SELECT * FROM IDX_DESC_TEST;

 

-- 1.인덱스 => 고객번호 + 시작일 + 종료일
-- 시작일 + 종료일 구성일 때 최근 시점 조회
SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND '3131-08-07' BETWEEN STT_DTM AND END_DTM;

/*
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |    31 |   930 |   251   (1)| 00:00:04 |
|*  1 |  INDEX RANGE SCAN| IDX_ID_END_STT |    31 |   930 |   251   (1)| 00:00:04 |
-----------------------------------------------------------------------------------
*/

 

SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND STT_DTM <= '3131-08-07' AND END_DTM >= '3131-08-07';

/*
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |    31 |   930 |   251   (1)| 00:00:04 |
|*  1 |  INDEX RANGE SCAN| IDX_ID_END_STT |    31 |   930 |   251   (1)| 00:00:04 |
-----------------------------------------------------------------------------------
*/

 

SELECT /*+ INDEX_DESC(A IDX_ID_STT_END) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '3131-08-07'  BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |    34   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_STT_END |    31 |   930 |    34   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/

 

-- 2.인덱스 => 고객번호 + 종료일 + 시작일
-- 시작일 + 종료일 구성일 때 과거 시점 조회
SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM;

/*
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |    10 |   300 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_ID_STT_END |    10 |   300 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
*/

 

SELECT *
FROM IDX_DESC_TEST
WHERE USER_ID = '100002' AND STT_DTM <= '2011-03-19' AND END_DTM >= '2010-03-19';

/*
-----------------------------------------------------------------------------------
| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                |    10 |   300 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_ID_STT_END |    10 |   300 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
*/

 

SELECT /*+ INDEX_DESC(A IDX_ID_END_STT) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |   125   (1)| 00:00:02 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_END_STT |    10 |   300 |   125   (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------
*/

-- 1. 최신날짜로 검색을 했을시에는 앞에서 만들어 졌던 IDX_ID_END_STT 인덱스로 범위스캔
-- 2. 과거날짜로 검색을 했을시에는 앞에서 만들어 졌던 IDX_ID_STT_END 인덱스로 범위스캔
-- 한다는 사실을 알게되었다.

 

-- 결국 둘이 비교했을 경우

SELECT /*+ INDEX_DESC(A IDX_ID_STT_END) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '2011-03-19'  BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_STT_END |    10 |   300 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/


SELECT /*+ INDEX_DESC(A IDX_ID_END_STT) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '2011-03-19' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |   125   (1)| 00:00:02 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_END_STT |    10 |   300 |   125   (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------
*/


SELECT /*+ INDEX_DESC(A IDX_ID_STT_END) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '3131-08-07'  BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |    34   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_STT_END |    31 |   930 |    34   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/


SELECT /*+ INDEX_DESC(A IDX_ID_END_STT) */ *
FROM IDX_DESC_TEST A
WHERE USER_ID = '100002' AND '3131-08-07' BETWEEN STT_DTM AND END_DTM AND ROWNUM <= 1;

/*Plan hash value: 1689508773

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    30 |    11   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING| IDX_ID_END_STT |    31 |   930 |    11   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
*/

 




(9)  Access Predicate 와 Filter Predicate

  • dbms_xplan패키지를 통해 실행 계획을 출력해보면 Predicate 정보를 확인할 수있다.




SQL> set autotrace traceonly explain;

select /*+ ordered use_nl(e) index(e emp_x01) */ *
from   dept d, emp e
where  d.loc = 'CHICAGO'
and    e.deptno = d.deptno
and    e.job like 'SALE%'
and    e.job between 'A' and 'Z'
and    e.sal >= 1000
and    e.ename like '%A%'
and    trim(e.ename) = 'ALLEN'
and    e.comm >= 300
and    to_char(e.hiredate, 'yyyymmdd') like '198102%' ;
SQL> SQL>   2    3    4    5    6    7    8    9   10   11
Execution Plan
----------------------------------------------------------
Plan hash value: 3070931621

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |     1 |    57 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID  | EMP      |     1 |    37 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |          |     1 |    57 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT     |     1 |    20 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | DEPT_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | EMP_IDX  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("E"."ENAME" LIKE '%A%' AND TRIM("E"."ENAME")='ALLEN' AND
             TO_CHAR(INTERNAL_FUNCTION("E"."HIREDATE"),'yyyymmdd') LIKE '198102%' AND
             "E"."SAL">=1000 AND "E"."COMM">=300)
  4 - access("D"."LOC"='CHICAGO')
  5 - access("E"."DEPTNO"="D"."DEPTNO" AND "E"."JOB" LIKE 'SALE%')
      filter("E"."JOB" LIKE 'SALE%' AND "E"."JOB">='A' AND "E"."JOB"<='Z')

SQL>





1. 인덱스 단계 Access Predicate    5
2. 인덱스 단계 Filter Predicate        5
3. 테이블 단계 Filter Predicate        1




1. 인덱스 단계에서의 Access Predicate

  • 인덱스 스캔 범위를 결정하는데에 영향을 미치는 조건절
  • 범위 검색 조건을 뒤따르는 조건이거나 선행 컬럼이 조건에서 누락될지라도 액세스 범위를 결정하는데 기여
  • 수직적 탐색과정에서 모든 인덱스 컬럼을 비교 조건으로 사용
  • 영향을 안 미치는 경우
    • 좌변 컬럼을 가공한 조건절
      trim(e.ename)=’ALLEN’
    • 왼쪽 ‘%’ 또는 양쪽 ‘%’ 기호를 사용한 LIKE 조건절
      ename like ‘%A%’
    • 같은 컬럼에 대한 조건절이 두개 이상일때, 액세스 조건으로 선택( job like ‘SALES%’) 되지 못한 조건 
      job between ‘A’ and ‘Z’

2. 인덱스 단계에서의 Filter Predicate

  • 테이블로의 액세스 여부를 결정짓는 조건
  • 첫번째 나타나는 범위 검색 조건부터 이후 모든 조건절 컬럼들이 포함하며
    조건절에서 누락된 컬럼 뒤쪽에 놓인 인덱스 컬럼들도 포함


3. 테이블 단계에서의 Filter Predicate   

  • 테이블을 액세스 하고 나서 최종 결과집합으로의 포함여부를결정짓는 조건절
to_char(e.hiredate,’yyyymmdd’) like ‘198102%’와 ‘d.loc=’CHICAGO’
  • 인덱스를 경유하지 않고 테이블 전체를 스캔할때는 테이블 단계에서의 Filter Predicate만 나타 남


(10) Index Fragmentation

  • B*Tree 인텍스의 ‘B’ 는 ‘Balanced’의 약자로 인덱스 루트에서 리프 블록까지 어떤 값으로 탐색하더라도
    읽는 블록 수가 같음을 의미 (모든 리프블록까지의 높이(height) 가 동일)
  • 불균형은 없지만 index fragmentation에 의한 index skew 또는 sparse현상이 생길수 있음



Index skew 

  • 인덱스 엔트리가 왼쪽또는 오른쪽에 치우치는 현상
  • 대량의 delete작업을 마치고 나면 왼쪽에 있는 리프 블록들은 텅비고 오른쪽 블록은 꽉차는 등의 현상이 나타 날수도 있음
  • 텅빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만 인덱스 구조상에는 그대로 남께 되면
  • 언제든 재사용이 가능하지만 다시 채워질때 까지 인덱스 스캔(대개 Index Full Scan) 효율이 낮아지게 된다.


  • 통 계성 테이블등과 같이 매일 delete & insert 가 있을경우
    delete , insert 작업을 완료후 commit 을 하게 되면 왼쪽 블록은 항상 빈상태로 남아있으므로
    delete , commit , insert , commit 순으로 작업을 하게 된다면 삭제된 블록이 재사용되어 효율적이다.
  • insert & commit & delete & commit 는 의미가 없다.


Index Sparse

  • 인덱스 블록 전반에 걸쳐 밀도 (density)가 떨어지는 현상
  • 블록이 텅비면 곧바로 freelist 로 반환되 언제든 재사용 되지만 index sparse는
    지워진 자리에 새로운 값이 입력되지 않으면 영영 재사용되지 않을 수도 있다.


Index Rebuild

  • Fragmentation 때문에 인덱스 크기가 계속 증가하고 스캔 효율이 나쁠대는
    coalesce 명령을 수행하여 처리
alter index t_idx coalesce;

  • 여러 인덱스 블록을 하나로 병합하고, 결과로 생긴 빈블록들은 freelist에 반환한다.
    단 인덱스 세그먼트에 할당된 미사용공간 은 반환되지 않는다.


alter index t_idx shrink space;

  • index fragmentation을 해소하면서 공간까지 반환할경우 사용 단 ASSM에서만 동작한다.


alter index t_idx shrink space compact;
  • 공간은 반환하지 않으므로 coalesce와 같은 명령어가 된다.


  • colaesce와 shrink는 레코드를 건건이 지웠다가 입력하므로 작업량이 많을 경우
    rebuild를 사용하는것이 효율적일수 있다.


alter index t_idx rebuild;
alter index t_idx rebuild online;

  • 이와 같이 인덱스 구조를 슬림화하면 저장효율과 스캔효율은 좋아지지만 블록 공간이 전혀없으면
    인덱스 분할이 자주 발생해 DML성능을 떨어뜨릴수 있으므로 상황에 맞게 사용하여야한다.
  • 인덱스 분할에 의해 Shared 모두 enq:TX-index contention 대기 이벤트가 자주나타면
    pctfree를 높이고 나서 인덱스를 rebuild를 하면 경합을 줄일수 있다.
    (인덱스에서 pctfree는 인덱스를 처음 생성하거나 rebuild때만 적용)


인덱스  rebuild를 고려 사항

  • 인덱스 분할에 의한 경합이 현저히 높을때
  • 자주 사용되는 인덱스 스캔 효율을 높일때 (NL조인에서 반복 액세스되는 인덱스 높이가 증가했을때)
  • 대량의 delete작업후 다시 입력될때까지 장기간이 필요할때
  • 레코드수는 일정한대 인덱스가 계속 커질때




인덱스 freelist

  • 테이블
    • pctfree와 pctused는 각각 freelist에서 제외되는 시점과, 다시 등록되는 시점을 지정
  • 인덱스
    • 값이 입력될때마다 freelist를 참조하지 않아도 되며 분할로  빈블록이 필요할 때만 참조, pctused  파라미터는 없음



PCTFREE

  • 테이블
    • 블록에 더이상 INSERT가 발생하지 못하도록 FREELIST에서 제외되는 시점이며 남겨진 공간은 UPDATE를 위해 사용
  • 인덱스
    • 지정한 비율만큼 공간을 남겨두었다가 나중에 INSERT 를 위해 사용


PCTUSED

  • FREELIST에서 제거된 테이블 블록에 빈공간이 일정 수준 이상 확보되었을경우 다시 FREELIST에
    등록하기 위한 파라미터
  • PCTFREE가 10이고 PCTUSED가 80이면 freelist에서 제거된 블록에
    10%정도 delete가 발생하는 순간 해당 블록을 다시 freelist에 등록
  • {100-pctfree} 만큼의 공간이 차면 freelist에서 제거
    pctused만큼만 남기고 레코드가 모두 지워질때 다시 freelist에 등록


  • 인덱스에서의 freelist는 인덱스 분할에 사용 가능 빈 블록들을 관리
  • delete에 의해 지워진 인덱스 블록은 커밋 시점에 freelist에 반환되지만
  • insert 시점에 다시 값이 입력되더라도 곧바로 freelist에서 제거되지 않음.
  • freelist에 그대로 두었다가 인덱스 분할때문에 freelist를 스캔하는 프로세스에 의해 정리
  • (freelist에서 얻은 블록이 비어있지 않으면 다른 블록을 재요청하기 전에 일단 해당 블록을 freelist에서 제거- 커밋 시점에 수행해야할 일량을 최소화 하기 위함)






  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 남송휘 (휘휘)
  • 최초작성일: 2011년 3월 8일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^


번호 제목 글쓴이 날짜 조회 수
60 Front Page file 운영자 2011.02.15 149185
59 3. 인덱스 파티셔닝 darkbeom 2011.06.19 53288
58 3. 다양한 인덱스 스캔 방식 file 멋진넘 2011.02.18 33360
57 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.29 30396
56 2. 파티션 Pruning 실천하자 2011.06.21 25631
55 3. 뷰 Merging 실천하자 2011.05.15 22997
54 3. 해시 조인 file darkbeom 2011.03.20 21008
53 2. 서브쿼리 Unnesting darkbeom 2011.05.15 19168
52 4. 통계정보 Ⅰ darkbeom 2011.04.26 17800
» 7. 인덱스 스캔 효율 [1] 휘휘 2011.03.08 16546
50 7. Sort Area 크기 조정 실천하자 2011.06.13 14670
49 4. 테이블 Random 액세스 부하 [1] file darkbeom 2011.02.23 14322
48 4. 조인 순서의 중요성 운영자 2011.03.27 13877
47 1. 인덱스 구조 [1] file 실천하자 2011.02.16 13868
46 1. 기본 개념 멋진넘 2011.06.28 12993
45 8. 고급 조인 테크닉-1 [1] file darkbeom 2011.04.03 12917
44 9. 비트맵 인덱스 file 실천하자 2011.03.05 12047
43 1. 옵티마이저 file 실천하자 2011.04.18 10914
42 6. 히스토그램 실천하자 2011.04.24 10658
41 6. Sort Area를 적게 사용하도록 SQL 작성 file 실천하자 2011.06.13 8577