Oracle Index 的三个问题[Oracle防范]

索引( Index )是常见的数据库对象,它的设置好坏、利用能否得当,极大地影响数据库利用程序和Database 的性能.固然有很多资料讲索引的用法, DBA 和 Developer 们也常常与它打交道,但笔者发现,还是有不少的人对它存在曲解,因此针对利用中的常见问题,讲三个问题.此文全部示例所用的数据库是 Oracle 8.1.7 OPS on HP N series ,示例全部是真实数据,读者不需求注意具体的数据大小,而应注意在利用差别的办法后,数据的对比.本文所讲基本都是陈词谰言,但是笔者试图通过实际的例子,来真正让您懂得事情的关键.

第一讲、索引并非老是最佳挑选

假如发现Oracle 在有索引的情形下,没有利用索引,这并非Oracle 的优化器出错.在有些情形下,Oracle 确切会挑选全表扫描（Full Table Scan）,而非索引扫描（Index Scan）.这些情形普通有:

1. 表未做statistics, 大概 statistics 陈旧,招致 Oracle 判断失误.

2. 按照该表拥有的记录数和数据块数,实际上全表扫描要比索引扫描更快.

对第1种情形,最常见的例子,是以下这句sql 语句:

select count(*) from mytable;

在未作statistics 之前,它利用全表扫描,需求读取6000多个数据块（一个数据块是8k）, 做了statistics 之后,利用的是 INDEX (FAST FULL SCAN) ,只需求读取450个数据块.但是,statistics 做得不好,也会招致Oracle 不利用索引.

第2种情形就要复杂得多.普通概念上都认为索引比表快,对比难以理解什么情形下全表扫描要比索引扫描快.为了讲清楚这个问题,这里先介绍一下Oracle 在评价利用索引的代价（cost）时两个重要的数据:CF(Clustering factor) 和 FF(Filtering factor).

CF: 所谓 CF, 通俗地讲,就是每读入一个索引块,要对应读入多少个数据块.

FF: 所谓 FF, 就是该sql 语句所挑选的后果集,占总的数据量的百分比.

大约的计算公式是:FF * (CF + 索引块个数) ,由此预计出,一个查询, 假如利用某个索引,会需求读入的数据块块数.需求读入的数据块越多,则 cost 越大,Oracle 也就越大概不挑选利用 index. （全表扫描需求读入的数据块数等于该表的实际数据块数）

其核心就是, CF 大概会比实际的数据块数目大.CF 遭到索引中数据的布列方法影响,普通在索引刚成立时,索引中的记录与表中的记录有杰出的对应关系,CF 都很小;在表经过大量的插入、改正后,这种对应关系越来越乱,CF 也越来越大.此时需求 DBA 重新成立大概组织该索引.

假如某个sql 语句从前一向利用某索引,较长时间后不再利用,一种大概就是 CF 已经变得太大,需求重新整理该索引了.

FF 则是Oracle 按照 statistics 所做的预计.比方, mytables 表有32万行,其主键myid的最小值是1,最大值是409654,考虑以下sql 语句:

Select * from mytables where myid>=1; 和

Select * from mytables where myid>=400000

这两句看似差不多的 sql 语句,对Oracle 而言,却有宏大的差别.因为前者的 FF 是100%, 此后者的 FF 大概只有 1%.假如它的CF 大于实际的数据块数,则Oracle 大概会挑选完好差别的优化方法.而实际上,在我们的数据库上的测试考证了我们的猜测. 以下是在HP 上履行时它们的 explain plan:

第一句:

SQL> select * from mytables where myid>=1;

已挑选325917行.

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3132 Card=318474 Byt es=141402456)

1 0 TABLE ACCESS (FULL) OF 'MYTABLES' (Cost=3132 Card=318474 Byt es=141402456)

Statistics

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

7 recursive calls

89 db block gets

41473 consistent gets

19828 physical reads

0 redo size

131489563 bytes sent via SQL*Net to client

1760245 bytes received via SQL*Net from client

21729 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

325917 rows processed

第二句:

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=346 Card=663 Bytes=2 94372)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYTABLES' (Cost=346 Card=663

Bytes=294372)

2 1 INDEX (RANGE SCAN) OF 'PK_MYTABLES' (UNIQUE) (Cost=5 Card=663)

Statistics

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

1278 recursive calls

0 db block gets

6647 consistent gets

292 physical reads

0 redo size

3544898 bytes sent via SQL*Net to client

42640 bytes received via SQL*Net from client

524 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

7838 rows processed

显而易见,第1句没有利用索引,第2句利用了主键索引pk_mytables. FF的宏大影响由此可见一斑.由此想到,我们在写sql 语句时,假如预先预计一下 FF, 你就几近可以预见到 Oracle 会否利用索引.

　　以上是“Oracle Index 的三个问题[Oracle防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：