第八章 ORACLE 索引 INDEXES (一）

b3uj3gjdpd

ORACLE DBA INDEXES
日月明王ORACLE DBA空间 http://sunmoonking.spaces.live.com
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，但是，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的匹配程度。
如果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而通过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
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'