第八章 ORACLE 索引 INDEXES (一）

来源：互联网发布：mysql循环语句编辑：程序博客网时间：2024/05/22 07:01

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'