B*Tree Indexes

SQL> SQL> select index_name||' '||blevel||' '||num_rows

2 from dba_indexes where table_name='TM_VEHICLE'

3 /

INDEX_NAME||''||BLEVEL||''||NUM_ROWS

-----------------------------------------------------------------

TM_VEHICLE_LEAVING_DATE 2 3461864

這裡BLEVEL不包括LEAF，也就是說僅代表BRANCH（B）

1* analyze index sbpopt.TM_VEHICLE_LEAVING_DATE validate structure

SQL> /

Index analyzed.

SQL> select height||' '||name from index_stats;

HEIGHT||''||NAME

------------------------------------------------------------------------------

3 TM_VEHICLE_LEAVING_DATE

這裡的HEIGHT代表整個INDEX TREE的高度，包括LEAF NODE。（參考cost of dual

http://sunmoonking.spaces.live.com/blog/cns!E3BD9CBED01777CA!234.entry）

Compression

COMPRESS可能能將INDEX TREE的高度降低，比如從3降到2，但是，ORACLE將花更多的時間在定址上，優點是BUFFER中可以放更多的INDEX ENTRIES，可以提高cache-hit的命中率，物理I/O也會隨之降低。也就是說compress index在提高I/O的同時會消耗更多CPU。

Reverse

REVERSE KEY INDEX能減少leaf block的爭用，尤其是在RAC環境中，可以減少訪問相同塊的機率，同時也就能減少在RAC instance之間傳輸的BLOCK的數量。

Descending

SQL> create table colocated ( x int, y varchar2(80) );

表已建立。

1 begin

2 for i in 1 .. 100000

3 loop

4 insert into colocated(x,y)

5 values (i, rpad(dbms_random.random,75,'*') );

6 end loop;

7* end;

8 /

PL/SQL 過程已成功完成。

SQL> alter table colocated add constraint colocated_pk primary key(x);

表已更改。

SQL> begin

2 dbms_stats.gather_table_stats( user, 'COLOCATED', cascade=>true );

3 end;

4 /

因為BLOCK SIZE是8K，所以，差不多100行每塊。

SQL> select table_name,blocks from user_tables

2 where table_name='COLOCATED'

3 /

TABLE_NAME BLOCKS

------------------------------ ----------

COLOCATED 1252

再來看看INDEX是如何應用的

Set autotrace traceonly

SQL> select x,y from colocated where x<2000

2 /

已選擇1999行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=30 Card=1999 Bytes=1

59920)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED' (Cost=30 Card

=1999 Bytes=159920)

2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (UNIQUE) (Cost=6 Ca

rd=1999)

看到INDEX (RANGE SCAN)後面跟著一個 TABLE ACCESS (BY INDEX ROWID)，ORACLE先讀INDEX，然後根據INDEX ENTIRES讀database block然後得到row data。這種讀法，在資料量小的時候比較有效（thin表1%-3%，fat表1%-20%）

1* select count(*) from colocated where x<2000

SQL> /

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)

1 0 SORT (AGGREGATE)

2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (UNIQUE) (Cost=6 Ca

rd=1999 Bytes=9995)

這種讀法不需要讀DATA BLOCK，僅僅讀INDEX。

透過index去訪問表，我們會發生很多scattered,random I/O,意思是index會告訴我們都block1,block200,block1,block352,block1。。。而不會去順序讀，也就是我們可能多次讀一個block。所以2000行TABLE ACCESS BY ROWID可能會導致2000次table blocks讀。而這2000行資料可能僅僅儲存在20個BLOCK裡（這也就是為什麼前面說（thin表1%-3%，fat表1%-20%）的原因，%和BLOCK所能容納的ROWS數有關，本例中100row/block，那如果2row/block呢？）

CLUSTERING_FACTORUSER_INDEXES中的CLUSTERING_FACTOR表示表中資料的ORDER和INDEX的ORDER的匹配程度。

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')

and a.table_name = b.table_name

INDEX_NAME NUM_ROWS

------------------------------------------------------------ ----------

BLOCKS CLUSTERING_FACTOR

---------- -----------------

COLOCATED_PK 100000

1252 1190

另外建一個按colocated的Y排序的表disorganized，來排亂index的順序。

SQL> create table disorganized as

2 select x,y from colocated order by y;

Table created.

SQL> alter table disorganized

2 add constraint disorganized_pk

3 primary key (x);

Table altered.

SQL> begin

2 dbms_stats.gather_table_stats( user, 'DISORGANIZED', cascade=>true );

3 end;

4 /

PL/SQL procedure successfully completed.

1 select a.index_name,

2 b.num_rows,

3 b.blocks,

4 a.clustering_factor

5 from user_indexes a, user_tables b

6 where index_name in ('COLOCATED_PK','DISORGANIZED_PK')

7* and a.table_name = b.table_name

SQL> /

INDEX_NAME NUM_ROWS

------------------------------------------------------------ ----------

BLOCKS CLUSTERING_FACTOR

---------- -----------------

COLOCATED_PK 100000

1252 1190

DISORGANIZED_PK 100094

1219 99905

大家也可以SQL TRACE下，disorganized的CPU，LOGICAL I/O都會比COLOCATED大很多，可以看到'DISORGANIZED_PK的CLUSTERING_FACTOR和NUM_ROWS很接近。我們如果透過INDEX讀資料從頭讀到尾，會發生99905 I/O，比COLOCATED_PK大很多。

相同的表相同的INDEX如果CLUSTERING_FACTOR不同執行計劃也會不同，甚至相差很大。

SQL> select * from colocated where x between 10000 and 20000;

10001 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=142 Card=10005 Byt

es=800400)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COLOCATED' (TABLE) (Cost

=142 Card=10005 Bytes=800400)

2 1 INDEX (RANGE SCAN) OF 'COLOCATED_PK' (INDEX (UNIQUE)) (C

ost=22 Card=10005)

SQL> select * from DISORGANIZED where x between 10000 and 20000;

10001 rows selected.

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=271 Card=10013 Byt

es=801040)

1 0 TABLE ACCESS (FULL) OF 'DISORGANIZED' (TABLE) (Cost=271 Ca

rd=10013 Bytes=801040)

Bitmap Indexes

Bitmap是7.3版本推出的一種INDEX。Standard Edition.不支援，Enterprise and Personal Editions支援，是為data warehous設計，OLTP不適合。一個index key entry 可以指向多行資料，而B*TREE則是一對一的。Bitmap index儲存null entries。適合low distinct cardinality.

Bitmap Join Indexes

這是oracle9 i推出的新的bitmap index型別。

create bitmap index emp_bm_idx

2 on emp( d.dname )

3 from emp e, dept d

4 where e.deptno = d.deptno

應用時

select emp.*

2 from emp, dept

3 where emp.deptno = dept.deptno4 and dept.dname = 'SALES'

ORACLE INDEXES

相關文章