B*Tree Indexes 在平衡B*TREE index的所有leaf block都在同一級別，這LEVEL代表著IDNEX的高度。也就是從ROOT查找到LEAF BLOCK都要訪問相同數量的BLOCK，在百萬級別的數據上一般B*TREE索引會是2到3LEVEL 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，但是，O
RACLE將花更多的時間在尋址上， 優點是BUFFER中可以放更多的INDEX ENTRIES，可以提高cache-hit的命中率，物理I/O也會隨之降低。也就是說compress index在提高I/O的同時會消耗更多CPU。
Reverse
REVERSE KEY INDEX能減少leaf block的爭用，尤其是在RAC環境中，可以減少訪問相同塊的幾率，同時也就能減少在RAC instance
n style="FONT-FAMILY: 宋體">之間傳輸的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 / ND: #d9d9d9; COLOR: black; FONT-FAMILY: Tahoma">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'
e="3"> 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)
ahoma"> 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 blocksn>讀。而這2000行數據可能僅僅保存在20個BLOCK裡（這也就是為什麼前面說（thin表1%-3%，fat表1%-20%）的原因，%和BLOCK所能容納的ROWS數有關，本例中100row/block，那如果2row/block呢？ ）
ONT-SIZE: 13pt; FONT-FAMILY: Utopia-Semibold">CLUSTERING_FACTORUSER_INDEXES中的CLUSTERING_FACTOR表示表中數據的ORDER和INDEX的ORDER的匹配程度。
如果clustering_factor的值和表中的塊數目大致一樣，那麼你的表和索引的順序是一樣的，也就是說一個leaf block的index entrIEs幾乎指向相同data block。不過，如果clustering_factor 的值接近表中的行數目，那就表明表格中的行和索引的順序是很隨機的。
">
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
可以看到CLUSTERING_FACTOR和BLOCKS很接近，而和num_rows相差很大。我們如果通過INDEX讀數據從頭讀到尾，會發生1190 I/O，因為NEXT VAL和CURR VAL在同一個BLOCK上的幾率很大。相反，如果CLUSTERING_FACTOR和NUM_ROWS接近，則會釋放本BLOCK
nt>而通過I/O獲得其他的BLOCK。
另外建一個按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
ACKGROUND: #d9d9d9; COLOR: black; FONT-FAMILY: Tahoma"> 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
D: #d9d9d9; COLOR: black; FONT-FAMILY: Tahoma"> 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類型。
/p>
EN-US" style="BACKGROUND: #d9d9d9; COLOR: black; FONT-FAMILY: Tahoma">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'
