B*Tree 인덱스 (by darkturtle) [2012.11.09]
내리차순 인덱스
- 오라클 8i 버전에서 소개
- 오르차순(작은 값에서 큰 값으로) 대신에 내림차순(큰 값에서 작은 값으로)으로 정렬 저장
- 오라클 8i 이전 버전에서는 DESC(내림차순) 키워드는 문법적으로는 제공되지만
데이터가 저장되고 사용하는 데는 아무런 영향이 없음(에러는 없지만 DESC 옵션은 작동 하지 않음)
SQL> drop table t ;
Table dropped.
SQL> create table t
2 as
3 select * from all_objects ;
Table created.
SQL> create index t_idx on t ( owner, object_type, object_name );
Index created.
SQL> exec dbms_stats.gather_Table_stats(user,'T',method_opt=>'for all indexed columns');
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type DESC ;
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 333 | 4995 | 1 (0)|
|* 1 | INDEX RANGE SCAN DESCENDING| T_IDX | 333 | 4995 | 1 (0)|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OWNER">='T' AND "OWNER"<='Z')
filter("OBJECT_TYPE" IS NOT NULL)
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type ASC;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 333 | 4995 | 2 (50)|
| 1 | SORT ORDER BY | | 333 | 4995 | 2 (50)|
|* 2 | INDEX RANGE SCAN| T_IDX | 333 | 4995 | 1 (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER">='T' AND "OWNER"<='Z')
filter("OBJECT_TYPE" IS NOT NULL)
- OWNER, OBJECT_TYPE, OBJECT_NAME으로 구성된 인덱스를 더 이상 정렬 작업에 사용 불가
요청에 따라 OWNER 는 내림차순으로 읽어야 하고, OBJECT_TYPE은 오름차순으로 읽어야 함
따라서 모든 로우를 읽은 후 정렬할 수밖에 없을 것이다.
SQL> create index desc_t_idx on t ( owner desc,object_type asc ) ;
Index created.
SQL> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type ASC ;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 333 | 4995 | 1 (0)|
|* 1 | INDEX RANGE SCAN| DESC_T_IDX | 333 | 4995 | 1 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF') AND
SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))>='T' AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))<='Z' AND "OBJECT_TYPE" IS NOT NULL)
- 정렬된 데이터를 읽을 수 있게 되었고,
실행계획에 추가적인 정렬 단계는 더 이상 필요하지 않게 되었다.
언제 B*Tree 인덱스를 사용해야 하는가 ?
- 전체 테이블 로우 수에 비해 선택하려는 로우 수가 적은 경우는 B*Tree 인덱스만을 사용한다.
- 테이블의 많은 로우를 처리하지만, 인덱스만으로만 처리할 수 잇다면 B*Tree 인덱스를 사용한다.
h2 인덱스를 사용 하는 두 가지 방법
- 테이블의 로우에 접근하는 방법
: 테이블의 로우를 액세스하기 위해 인덱스를 읽을 수 있을 것이다.
전체 로우 수에 비해 매우 적은 로우를 접근하려고 할 때 - 쿼리에 응답하는 수단으로
: 인덱스가 쿼리에 응답하기 위한 충분한 내용이 있는 경우라서 테이블에 접근할 필요가 없는 경우
이 경우 인덱스가 테이블의 또 다른 축소판
1) 테이블의 로우에 접근하는 방법
SQL> set autotrace traceonly explain
SQL> select owner, status
2 from t
3 where owner =user ;
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2283 | 25113 | 1 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2283 | 25113 | 1 (0)|
|* 2 | INDEX RANGE SCAN | DESC_T_IDX | 9 | | 1 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(SYS_OP_DESCEND("OWNER")=SYS_OP_DESCEND(USER@!))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))=USER@!)
- INDEX RANGE SCAN ==> TABLE ACCESS BY INDEX ROWID
오라클이 인덱스 엔트리를 읽고 난 다음에 테이블 로우를 읽기 위해 데이터베이스 블록에
대해 논리적 또는 물리적 I/O를 일으키게 된다는 것이다.
이 점은 대용량 데이터를 인덱스를 경유하여 테이블 T을 읽게 된다면,
효과적이지 못하게 된다는 것을 의미(랜덤 ACCESS I/O)
2) 두번째(테이블을 대체하는 인덱스를 사용하는 경우)는 인덱스만으로 100% 처리할 수 있는 경우
SQL> select count(*)
2 from t
3 where owner =user ;
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 4 (0)|
| 1 | SORT AGGREGATE | | 1 | 6 | |
|* 2 | INDEX RANGE SCAN| T_IDX | 2283 | 13698 | 4 (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"=USER@!)
- 인덱스만으로 쿼리의 결과를 처리
인덱스 구조 자체만 읽음, 테이블에 전혀 접근 하지 않았음 - TABLE ACCESS BY INDEX ROWID(랜덤 Access I/O)를 수행해야만 하는 경우에는
테이블 전체 블록 중 소량만을 읽도록 해야 함 - 만약 테이블 로우의 대부분을 추출해야 하는 경우라면(추출 대상 로우가 20% 이상이라면)
B*Tree를 경유하는 것이 테이블 전체 스캔하는 것보다 더 오래 걸릴 것이다. ( 테이블 건수에 따라 상대적 ) - 빠른 전체 스캔(fast full scan)은 인덱스 블록을 특정 순서대로 읽지 않는다.(Multi Block I/O)
- 몇 개 컬럼으로 구성된 폭이 좁은 테이블은 추출되는 데이터 비율이 매우 낮아야 할 것이다.
(상대적으로 작은 Table Full Scan 이 빠르므로 ) - 인덱스를 사용하는 쿼리는 테이블로부터 2~3% 이하의 로우를 가져오는 데 사용해야 한다.
(상대적으로 큰 Table Full Scan 이 느리므로 ) - 인덱스를 사용하는 쿼리는 테이블로부터 20~25% 정도의 로우를 가져오는 데 사용해야 한다.
( 이건 아닌듯... 로우가 많이 질수록 램던 I/O가 증가해서..)
-.예) 테이블 100,000 Row, 500 Block
인덱스 100,000 Row, 100 Block
20,000 Row Fetch
Table Full Scan - 500 Block Multi Block I/O 후 조건에 맞는 Row 를 Return ==> 봉
Index Range Scan -> Table Access By Rowid ( 200,000 번) 후 조건에 맞는 Row 를 Return ==> 황
물리 구조
- 일반적으로 키본 키 값이 서로 가까운 로우들은 물리적으로도 인접한 위치 (IOT는 100% Sync)
select * from T where primary_key between :x and :y
- 추출하고자 하는 로우가 일반적으로 동일 블록에 저장될 수 있다.
- ASSM 방식을 사용하거나 다수 개의 FREELIST/FREELIST GROUPS를 사용하는 것은
데이터의 디스크 저장방식에 영향을 미친다. 데이터를 다수의 블록에 흩어지게 하고,
기본 키에 의한 자연적인 근접성이 발생하지 않을 수 있게 한다.
확인 방법 : 테이블이 순차적으로 저장 vs 테이블이 non-순차적으로 저장 비교
- colocate : 같은 장소에 배치하다
SQL> create table colocated ( x int , y varchar2(80));
Table created.
SQL> begin
2 for i in 1 .. 100000
3 loop
4 insert into colocated (x,y) values ( i, rpad(dbms_random.random,75,'*'));
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> alter table colocated add constraint colocated_pk primary key (x) ;
Table altered.
SQL> exec dbms_stats.gather_table_stats(user,'COLOCATED');
PL/SQL procedure successfully completed.
SQL> create table disorganized
2 as
3 select x,y
4 from colocated
5 order by y ;
Table created.
SQL> alter table disorganized add constraint disorganized_pk primary key (x ) ;
Table altered.
SQL> exec dbms_stats.gather_table_stats(user,'DISORGANIZED');
PL/SQL procedure successfully completed.
tkprof nis2010t2_ora_17102.trc tg4.txt sys=no aggregate=no
SQL> select * from colocated where x between 20000 and 40000;
Execution Plan
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20213 | 1579K| 57 (2)|
| 1 | TABLE ACCESS BY INDEX ROWID| COLOCATED | 20213 | 1579K| 57 (2)|
|* 2 | INDEX RANGE SCAN | COLOCATED_PK | 20213 | | 9 (0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">=20000 AND "X"<=40000)
select *
from
colocated where x between 20000 and 40000
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 1335 0.10 0.08 0 2899 0 20001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1337 0.10 0.08 0 2899 0 20001
SQL> select /*+ index(disorganized disorganized_pk ) */ * from disorganized where x between 20000 and 40000 ;
Execution Plan
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19896 | 1554K| 4008(1)|
| 1 | TABLE ACCESS BY INDEX ROWID| DISORGANIZED | 19896 | 1554K| 4008(1)|
|* 2 | INDEX RANGE SCAN | DISORGANIZED_PK | 19896 | | 9(0)|
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">=20000 AND "X"<=40000)
select /*+ index(disorganized disorganized_pk ) */ *
from
disorganized where x between 20000 and 40000
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 1335 0.20 0.16 0 21354 0 20001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1337 0.20 0.17 0 21354 0 20001
[표11-5] 데이터 물리 구조가 인덱스 액세스에 미치는 영향
SQL> select a.index_name, b.num_rows, b.blocks, a.clustering_factor
from user_indexes a, user_tables b
where index_name in ('COLOCATED_PK','DISORGANIZED_PK')
and a.table_name = b.table_name
/
INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
COLOCATED_PK 101047 1252 1190
DISORGANIZED_PK 99464 1219 99911
- Clustering Factor 가 중요한 이유
- IOT의 존재 이유 !!!
+ 테스트 디비 생성 시, DBMS EXP/IMP, EXPDP/IMPDP(Reorg됌) 보다는 BACKUP & RECOVERY 방식이 더 타당할듯
+ 많은 블록을 읽는 다는 것은 db buffer 에 대한 latch 를 많이 사용 ==> 확장성이 떨어짐 ==> 확장성이 높은 시스템을 구축하자.
논리 I/O에 미치는 ARRARYSIZE 효과
-. ARRAYSIZE는 클라이언트가 다음 로우를 요청할 때 클라이언트에 전송하는 로우의 숫자
-. 클라이언트는 데이터베이스에 다음 로우의 집합을 요청하기 전까지는 이 로우들을 버퍼링 하여 사용
-. 여러 번의 호출로 동일한 블록을 반복해서 읽어 결과를 추출해야 하는 경우라면,
-. 오라클은 그 블록을 버퍼 캐시에서 다시 추출해야 하며, 이때 ARRAYSIZE는 논리 I/O에 주목할 만한 효과를 나타낸다.
-- Organized 테이블에 대하여
SQL> exec dbms_monitor.session_trace_enable
PL/SQL procedure successfully completed.
SQL> show arraysize
arraysize 15
SQL> select * from colocated a15 where x between 20000 and 40000 ;
..,
20001 rows selected.
SQL> set arraysize 100
SQL> show arraysize
arraysize 100
SQL> select * from colocated a100 where x between 20000 and 40000 ;
...
20001 rows selected.
-- Trace 내용
select *
from
colocated a15 where x between 20000 and 40000
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 1335 0.10 0.07 0 2899 0 20001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1337 0.10 0.07 0 2899 0 20001
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
20001 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=2899 pr=0 pw=0 time=40122 us)
20001 INDEX RANGE SCAN COLOCATED_PK (cr=1374 pr=0 pw=0 time=20097 us)(object id 954366)
STAT #5 id=1 cnt=20001 pid=0 pos=1 obj=954365 op='TABLE ACCESS BY INDEX ROWID COLOCATED (cr=2899 pr=0 pw=0 time=40122 us)'
STAT #5 id=2 cnt=20001 pid=1 pos=1 obj=954366 op='INDEX RANGE SCAN COLOCATED_PK (cr=1374 pr=0 pw=0 time=20097 us)'
********************************************************************************
select *
from
colocated a100 where x between 20000 and 40000
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 202 0.05 0.07 0 684 0 20001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 204 0.05 0.08 0 684 0 20001
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 202 0.00 0.00
SQL*Net message from client 201 0.11 8.36
SQL*Net more data to client 800 0.00 0.01
STAT #3 id=1 cnt=20001 pid=0 pos=1 obj=954365 op='TABLE ACCESS BY INDEX ROWID COLOCATED (cr=684 pr=0 pw=0 time=40113 us)'
STAT #3 id=2 cnt=20001 pid=1 pos=1 obj=954366 op='INDEX RANGE SCAN COLOCATED_PK (cr=245 pr=0 pw=0 time=20097 us)'
==> ARRAYSIZE 를 15 => 100으로 늘림에 따라서, BLOCK I/O 횟수적으로 줄어든다.
-- Disorganized 테이블에 대하여
SQL> exec dbms_monitor.session_trace_enable
PL/SQL procedure successfully completed.
SQL> show arraysize
arraysize 15
SQL> select /*+ index(a15 disorganized_pk ) */ * from disorganized a15 where x between 20000 and 40000 ;
select /*+ index(a15 disorganized_pk ) */ *
from
disorganized a15 where x between 20000 and 40000
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 1335 0.16 0.16 0 21354 0 20001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1337 0.16 0.16 0 21354 0 20001
STAT #4 id=1 cnt=20001 pid=0 pos=1 obj=954407 op='TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=21354 pr=0 pw=0 time=140154 us)'
STAT #4 id=2 cnt=20001 pid=1 pos=1 obj=954408 op='INDEX RANGE SCAN DISORGANIZED_PK (cr=1374 pr=0 pw=0 time=20169 us)'
SQL> exec dbms_monitor.session_trace_enable
PL/SQL procedure successfully completed.
SQL> set arraysize 100
SQL> show arraysize
arraysize 100
SQL> select /*+ index(a100 disorganized_pk ) */ * from disorganized a100 where x between 20000 and 40000 ;
select /*+ index(a100 disorganized_pk ) */ *
from
disorganized a100 where x between 20000 and 40000
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 202 0.19 0.17 0 20225 0 20001
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 204 0.19 0.17 0 20225 0 20001
STAT #3 id=1 cnt=20001 pid=0 pos=1 obj=954407 op='TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=20225 pr=0 pw=0 time=140128 us)'
STAT #3 id=2 cnt=20001 pid=1 pos=1 obj=954408 op='INDEX RANGE SCAN DISORGANIZED_PK (cr=245 pr=0 pw=0 time=158 us)'
- disorganized 테이블의 경우, INDEX 를 통해서 접근하는 테이블 블록이 모두 흩뿌려져 존재 하여
Arraysize 에 의한 큰 차이가 발생하지 않음
클러스터링 팩터
- 인덱스 값의 순서로 기준으로 테이블의 로우가 정렬된 정도를 의미
- 인덱스를 경유하여 전테 테이블 읽기를 수행했을 때 발생하는 논리 I/O의 숫자 표현
- 클러스터링 팩터 값이 블록의 개수와 가깝다면, 테이블은 인덱스의 순서와 잘(나란히) 정렬되어 있다는 의미
==> 동일 리프 블록에 존재하는 인덱스 엔트리는 같은 데이터 블록의 로우를 가리키게 된다. - 클러스터링 팩터가 로우의 개수와 가깝다면, 테이블은 순서 없이 랜덤하게 저장되어 있다는 의미
==> 동일 리프 블록에 존재하는 인덱스 엔트리가 가리키는 로우는 같은 데이터 블록에 거의 저장되어 않는다는 것을 의미
SQL> select a.index_name, b.num_rows, b.blocks, a.clustering_factor
2 from user_indexes a, user_tables b
3 where index_name in ('COLOCATED_PK','DISORGANIZED_PK')
4 and a.table_name = b.table_name ;
INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
COLOCATED_PK 101047 1252 1190
DISORGANIZED_PK 99464 1219 99911
- COLOCATED_PK 인덱스를 이용하여 COLOCATED 테이블의 모든 로우를 읽는다면, 1,190 번 I/O 수행
- DISORGANIZED_PK 인덱스를 이용하면 DISORGANIZED 테이블의 모든 로우를 읽는다면, 99,911 번 I/O 수행
- 인덱스를 경유하는 범위 스캔에서 이렇게 큰 차이가 발생하는 요인은
인덱스에 있는 다음 로우와 이전 로우가 같은 블록에 존재하는가에 따라서 결정 - 버퍼 캐시에 테이블 블록이 존재한다면 추가적인 I/O가 발생하지 않을 것이다.
- 그러나 다음 로우가 동일 블록에 존재하지 않는다면, 이전 로우의 블록을 해제하고
- 새로운 I/O를 수행하여 처리할 다음 블록을 추출하여 블록 캐시에 넣을 것이다.
select count(y)
from
( select /*+ INDEX(COLOCATED COLOCATED_PK) */ * from COLOCATED )
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 2 0.30 1.09 1357 1400 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.30 1.09 1357 1400 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1400 pr=1357 pw=0 time=1095288 us)
100000 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=1400 pr=1357 pw=0 time=322568 us)
100000 INDEX FULL SCAN COLOCATED_PK (cr=210 pr=167 pw=0 time=211970 us)(object id 954366)
select count(y)
from
( select /*+ INDEX(DISORGANIZED DISORGANIZED_PK) */ * from DISORGANIZED)
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 2 0.60 0.93 1401 100121 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.60 0.94 1401 100121 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=100121 pr=1401 pw=0 time=939902 us)
100000 TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=100121 pr=1401 pw=0 time=3045584 us)
100000 INDEX FULL SCAN DISORGANIZED_PK (cr=210 pr=210 pw=0 time=102007 us)(object id 954408)
- 두 경우 모두 인덱스를 읽는 데 I/O가 210 발생하였다(cr=210)
전체 논리 읽기 수(consistent read)에서 210을 빼면 테이블을 읽는 데 수행된 I/O 숫자를 알수 있다.
잘 정렬된어 있는 테이블 = 1400 - 201 = 1199 클러스터링 팩터 1190
순서 없이 램던한 테이블 = 100121 - 201 = 99920 클러스터링 팩터 99911
SQL> set autotrace traceonly explain
SQL> select * from COLOCATED where x between 20000 and 30000 ;
Execution Plan
---------------------------------------------------------------------------------
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10108 | 789K| 28 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| COLOCATED | 10108 | 789K| 28 (0)|
|* 2 | INDEX RANGE SCAN | COLOCATED_PK | 10108 | | 4 (0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("X">=20000 AND "X"<=30000)
SQL> select * from DISORGANIZED where x between 20000 and 30000 ;
Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9949 | 777K| 270 (1)|
|* 1 | TABLE ACCESS FULL| DISORGANIZED | 9949 | 777K| 270 (1)|
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"<=30000 AND "X">=20000)
- 두 테이블은 동일한 구조로 인덱스로 동일하게 생성하였지만,
클러스터링 팩터(clustering factor)는 다르다.
이를 고려하여 옵티마이저는
COLOCATED 테이블을 읽기 위해 인덱스 액세스 실행계획을 수립
DISORGANIZED 테이블에는 테이블 전체 스캔 실행 계획 수립
모든 인덱스에 좋은 클러스터링 팩터를 가지도록 테이블을 재구성(rebuild)하려고 할 수 있다.
그러나 이것은 대부분이 시간 낭비다.
Why 1. 인덱스 범위 스캔을 통한 넓은 범위의 테이블을 읽는 경우만 효과가 있음 ( Index Range Scan )
2. 일반적으로, 테이블은 클러스터링 팩커가 좋은 단 하나의 인덱스만을 가지지 않는다는 점 ( 대개 Data Migration 시 PK 기준 )
B*Tree 요약
- 인덱스 사용 여부에 관건
-. 테이블 전체 데이터 중 추출 데이터의 비율이 어떠한가 ?
-. 데이터가 어떤 구조로 되어 있는가가 ?
- 1. 많은 양의 데이터를 추출하는 경우에도 인덱스를 사용하는 것이 효과적인 경우는
테이블에 대한 추가적인 랜덤 I/O를 피할 수 있는 경우다.( Index 만 접근, Table 접근이 없는 경우 ) - 2. 인덱스를 통해 테이블에 액세스해야 한다면, 전체 테이블 데이터 중 소량의 데이터만을 처리하는지 확인필요
애플리케이션 설계 단계에서 인덱스의 설계와 수행방안을 고려필요