oracle数据库索引失效

oracle数据库索引失效（精选5篇）

oracle数据库索引失效 第1篇

这篇文章主要介绍了oracle数据库索引失效的原因及如何避免索引失效，有需要的小伙伴参考下，

今天一个同事突然问我索引为什么失效。说实在的，失效的原因有多种：

但是如果是同样的sql如果在之前能够使用到索引，那么现在使用不到索引，以下几种主要情况:

1. 随着表的增长，where条件出来的数据太多，大于15%，使得索引失效(会导致CBO计算走索引花费大于走全表)

2. 统计信息失效需要重新搜集统计信息

3. 索引本身失效需要重建索引

下面是一些不会使用到索引的原因

索引失效

1) 没有查询条件，或者查询条件没有建立索引

2) 在查询条件上没有使用引导列

3) 查询的数量是大表的大部分，应该是30%以上。

4) 索引本身失效

5) 查询条件使用函数在索引列上(见12)

6) 对小表查询

7) 提示不使用索引

8) 统计数据不真实

9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。

10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),

但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.

错误的例子：select * from test where tu_mdn=13333333333;

正确的例子：select * from test where tu_mdn=13333333333;

11)对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

错误的例子：select * from test where id-1=9;

正确的例子：select * from test where id=10;

12)使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.

错误的例子：select * from test where round(id)=10;

说明，此时id的索引已经不起作用了 正确的例子：首先建立函数索引，

create index test_id_fbi_idx on test(round(id));

然后 select * from test where round(id)=10; 这时函数索引起作用了 1, 2,单独的>,<,(有时会用到，有时不会)

3,like “%_” 百分号在前.

4,表没分析.

5,单独引用复合索引里非第一位置的索引列.

6,字符型字段为数字时在where条件里不添加引号.

7,对索引列进行运算.需要建立函数索引.

8,not in ,not exist.

9,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况，

10, 索引失效。

11,基于cost成本分析(oracle因为走全表成本会更小)：查询小表,或者返回值大概在10%以上

12,有时都考虑到了 但就是不走索引,drop了从建试试在

13,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走

14,联合索引 is not null 只要在建立的索引列(不分先后)都会走,

in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,

其他建立索引的列可以是is null(但必须在所有列 都满足is null的时候),

或者=一个值;当建立索引的第一位置是=一个值时,其他索引列可以是任何情况(包括is null =一个值),

以上两种情况索引都会走。其他情况不会走。

oracle数据库索引失效 第2篇

代码如下:

COLUMN COLUMNS format a30 word_wrapped

COLUMN tablename format a15 word_wrapped

COLUMN constraint_name format a15 word_wrapped

SELECT TABLE_NAME,

CONSTRAINT_NAME,

CNAME1 || NVL2(CNAME2, ‘,‘ || CNAME2, NULL) ||

NVL2(CNAME3, ‘,‘ || CNAME3, NULL) ||

NVL2(CNAME4, ‘,‘ || CNAME4, NULL) ||

NVL2(CNAME5, ‘,‘ || CNAME5, NULL) ||

NVL2(CNAME6, ‘,‘ || CNAME6, NULL) ||

NVL2(CNAME7, ‘,‘ || CNAME7, NULL) ||

NVL2(CNAME8, ‘,‘ || CNAME8, NULL) COLUMNS

FROM (SELECT B.TABLE_NAME,

B.CONSTRAINT_NAME,

MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,

MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,

MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,

MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,

MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,

MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,

MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,

MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,

COUNT(*) COL_CNT

FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,

SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,

SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,

POSITION

FROM USER_CONS_COLUMNS) A,

USER_CONSTRAINTS B

WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND B.CONSTRAINT_TYPE = ‘R‘

GROUP BY B.TABLE_NAME, B.CONSTRAINT_NAME) CONS

WHERE COL_CNT >ALL

(SELECT COUNT(*)

FROM USER_IND_COLUMNS I

WHERE I.TABLE_NAME = CONS.TABLE_NAME

AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,

CNAME6, CNAME7, CNAME8)

AND I.COLUMN_POSITION <= CONS.COL_CNT

GROUP BY I.INDEX_NAME)

/

在上面的基础上修改了一下，可以检查所有的用户，

代码如下:

SET linesize 400;

COLUMN OWNER format a10 word_wrapped

COLUMN COLUMNS format a30 word_wrapped

COLUMN TABLE_NAME format a15 word_wrapped

COLUMN CONSTRAINT_NAME format a40 word_wrapped

SELECT OWNER,

TABLE_NAME,

CONSTRAINT_NAME,

CNAME1 || NVL2(CNAME2, ‘,‘ || CNAME2, NULL) ||

NVL2(CNAME3, ‘,‘ || CNAME3, NULL) ||

NVL2(CNAME4, ‘,‘ || CNAME4, NULL) ||

NVL2(CNAME5, ‘,‘ || CNAME5, NULL) ||

NVL2(CNAME6, ‘,‘ || CNAME6, NULL) ||

NVL2(CNAME7, ‘,‘ || CNAME7, NULL) ||

NVL2(CNAME8, ‘,‘ || CNAME8, NULL) COLUMNS

FROM (SELECT B.OWNER,B.TABLE_NAME,

B.CONSTRAINT_NAME,

MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,

MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,

MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,

MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,

MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,

MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,

MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,

MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,

COUNT(*) COL_CNT

FROM (SELECT SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,

SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,

SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,

POSITION

FROM DBA_CONS_COLUMNS WHERE OWNER NOT IN (‘SYS‘,‘SYSTEM‘,‘SYSMAN‘,‘HR‘,‘OE‘,‘EXFSYS‘,‘DBSNMP‘,‘MDSYS‘,‘OLAPSYS‘,‘SCOTT‘,‘EXFSYS‘,‘SH‘,‘PM‘,‘CTXSYS‘)) A,

DBA_CONSTRAINTS B

WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND B.CONSTRAINT_TYPE = ‘R‘

GROUP BY B.OWNER,B.TABLE_NAME, B.CONSTRAINT_NAME) CONS

WHERE COL_CNT >ALL

(SELECT COUNT(*)

FROM DBA_IND_COLUMNS I

WHERE I.TABLE_NAME = CONS.TABLE_NAME AND I.TABLE_OWNER=CONS.OWNER

AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,

CNAME6, CNAME7, CNAME8)

AND I.COLUMN_POSITION <= CONS.COL_CNT

GROUP BY I.INDEX_NAME)

oracle数据库索引失效 第3篇

请看以下实例。创建测试表test1。

设置只显示执行计划。

执行后, 部分显示结果如下:

查询计划表明, 以上查询为全表扫描 (TABLE ACCESS FULL) , Cost为300。

在test1表上创建object_name列的索引为i_test1。

执行后, 部分显示结果如下:

查询计划表明, 同样的查询语句, 以上查询改为为索引范围扫描 (INDEX RANGE SCAN) , 利用了新创建的索引i_test1, Cost为3, 小于前面的全表扫描查询的Cost。

虽然索引通过一组排序后的索引键来取代默认的全表扫描检索方式, 从而提高检索的效率, 但创建和维护索引是有代价的。一是索引需要占物理空间;二是当对表中的记录进行增加、删除和修改时, 索引也要自动维护, 降低了数据的维护速度。因此是否应该在列上创建索引要权衡索引的利弊。

在Oracle数据库中, 索引在查询中起的作用如何, 将受到以下因素的影响的。

1 索引的选择性

索引的选择性是指在索引列里存储不同值的数目和记录数的比。例如:某个表的记录数是10000条, 而该表的索引列的值只有8000个不同的值 (或有2000个是相同的值) , 则此索引的可选择性为8000/10000=0.8。

请看以下实例, 创建测试表test2。

执行后, 显示结果如下:

在test2表的fasme列中, 只有一条记录值为“def”, 其他每条记录的值均为“abc”。

再如, 在test2表上分别创建fsame和object_name列的两个索引i_test2a和i_test2b。

执行后, 显示结果如下:

以上实例说明test2表共有75291条记录, object_name列有46685个不同的值, fsame列有2个不同的值, 即一条记录的值为“def”, 其余记录的值全为“abc”。object_name列的i_test2b索引的可选择性是:46685/75291≈0.62, fsame列的i_test2a索引的可选择性是:2/75291≈0.00003。

设置只显示执行计划。

执行后, 部分显示结果如下:

以上实例说明以object_name列为查询条件时, 执行索引范围扫描 (INDEX RANGE SCAN) , Cost为2。

执行后, 部分显示结果如下:

说明以fsame='abc'为查询条件时, 执行全表扫描 (TABLE ACCESS FULL) , Cost为316。

综述以上实例说明:高选择性列的索引能在执行查询中被利用, 且提高了查询速度, 反之, 低选择性列即使创建了索引, 该索引也不一定会在查询中被利用。而创建和维护索引是有代价的, 因此, 在低选择性的列上, 最好不要创建B树索引。

2 集群因子

集群因子是索引与其基于的表相比较后得出的有序性度量, 它用于检查在索引访问之后执行的表查找的成本。集群因子记录在扫描索引时将读取的块数量。集群因子的计算要求如下:

(1) 按顺序扫描索引。

(2) 将当前索引值指向ROWID的块部分与以前的索引值进行比较。

(3) 如果ROWID指向不同的块, 则集群因子增加1。

如果索引的集群因子值接近于索引中树叶块的数目, 表中的数据就越有序;如果索引的集群因子值接近于表中的行数, 则表中的数据就不是很有序。

请看以下实例, 创建测试用表test3。

在test3表上创建sample_size列的索引i_test3。

分析i_test3索引, 收集统计信息。

执行后, 显示结果如下:

i_test3索引的集群因子为890。

创建另一测试用表test4, 此表中的记录按sample_size列升序插入。

在test4表上创建sample_size列的索引。

SQL>CREATE INDEX i_test4 ON test4 (sample_size) ;

分析i_test4索引, 收集统计信息。

执行后, 显示结果如下:

i_test4索引的集群因子为96, 分析其因是test4表中的记录按sample_size列升序插入, 所以i_test4索引的集群因子小于i_test3索引的集群因子。

设置只显示执行计划。

执行后, 部分显示结果如下:

SQL>SELECT*FROM test4 WHERE sample_size<1;

执行后, 部分显示结果如下:

比较以上两条查询语句的执行计划。由于test4表中的记录是按sample_size列升序插入, 而test3表中的记录没有按sample_size列排序插入, 导致test3表中sample_size列的i_test3索引的集群因子值大于test4表中sample_size列的i_test4索引的集群因子值, 结果可想而知, 虽然test3表与test4表内容完全相同, 查询条件也相同, 但是在test3表中查询时, 采用全表扫描 (TABLE ACCESS FULL) , Cost为29, 而在test4表中查询时, 采用索引范围扫描 (INDEX RANGE SCAN) , Cost仅为3。

通过以上实例说明, 如果索引具有较大的集群因子, 造成邻近行位于不同的块中, 必须访问更多的表数据块才可以获得每个索引块中的行, 因此在查询中可能不会利用该索引, 而采用全表扫描的方式, 由此可见, 具有较大的集群因子的列也不适于创建索引。

3 二元高度

进行索引查找时, 首先从树根开始读数据, 然后通过中间节点, 最后定位到叶节点, 整个过程只能进行单数据块的读取。假设一个索引的二元高度为3, 利用这个索引来查询数据, 则需要读取4个数据块, 其中3个来自索引, 一个来自表。由此, 索引二元高度的每个级别都会有一个读取块, 而且这些块不能按顺序读取。

请看以下实例, 创建测试表test5。

在test5表上创建owner列的索引i_test5。

SQL>CREATE INDEX i_test5 ON test5 (owner) ;

分析i_test5索引, 收集统计信息。

执行后, 显示结果如下:

目前test5表中没有记录, i_test5索引的二元高度为0。向test5表中插入测试数据。

执行后, 显示结果如下:

目前test5表中已有记录, i_test5索引的二元高度变为2。

执行后, 显示结果如下:

虽然删除了test5表中的大量数据, 但test5表中i_test5索引的二元高度为仍2。

设置只显示执行计划。

执行后, 部分显示结果如下:

重建i_test5索引。

SQL>ALTER INDEX i_test5 REBUILD;

关闭执行计划的显示。

执行后, 显示结果如下:

重建索引后, 表test5中i_test5索引的二元高度为变为1。

设置只显示执行计划。

执行后, 部分显示结果如下:

相同的查询语句, 随着i_test索引二元高度的降低, 其Cost值也变小。

以上实例说明:索引的二元高度对把ROWID返回给用户进程时所要求的I/O量起到关键作用, 也就是说, 二元高度增加一个级别都会增加一个额外的读取快, 且在通常情况下由于这些块不能够按照顺序读取, 都会要求一个独立的I/O操作, 所以索引二元高度的值越大, 其I/O争用的现象也会越频繁, 此时其数据库索引的性能就会越低。

二元高度中要注意的是, 一是删除表中记录的操作, 并不会自动降低该表索引的二元高度, 此时重建索引是降低二元高度的一个办法。建议如果删除了表中记录数超过25%, 可重建索引以降低其二元高度;二是数据库块尺寸与索引二元高度的值是成反比, 数据块尺寸越大, 则索引的二元高度就越低;三是二元高度的值还会随着索引列中的非NULL数量以及索引列中值的范围狭窄程度而变化, 一般来说, 索引列中非NULL的数量越多, 则其二元高度的值越低, 即索引列中的值越靠近, 范围越小, 则其二元高度的值也就越低。

4 直方图

统计学中直方图通常会画成以数量为底边, 以频度为高度的一系列连接起来的矩形图。在Oracle数据库中直方图是一种对数据分布质量情况进行描述的工具, 它会按照某一列不同值出现数量多少或者出现的频率高低等绘制数据的分布情况, 以便指导优化器根据数据的分布做出正确的选择。

请看以下实例。创建测试用表test6。

执行后, 显示结果如下:

表test6共有10000行记录, 其中B列值为5的有9992行, 其余8行的值分别为1、2、3、4、9997、9998、9999和10000。

在test6表上创建B列的索引i_test6。

SQL>CREATE INDEX i_test6 ON test6 (b) ;

分析表, 强制使B列不产生直方图。

执行后, 显示结果如下:

EDNPOINT_NUMBER是累计值。EDNPOINT_VALUE是列的值。列B上只有最大值、最小值两条记录分别对应端点号 (endpoint_number) 0和1, 这种显示说明列B没有直方图信息

设置只显示执行计。

执行后, 部分显示结果如下:

执行后, 部分显示结果如下:

在没有直方图的情况下, 在B列上进行等值查询的时候, 都是索引范围扫描 (INDEX RANGE SCAN) 。

关闭执行计划的显示。

SQL>SET AUTOT OFF;

分析表, 收集直方图信息。

执行后, 显示结果如下:

由于收集了B列直方图, 显示出B列值的实现分布情况。

执行后, 部分显示结果如下:

执行后, 部分显示结果如下:

当查询条件为B=1时候采用索引范围扫描 (INDEX RANGE SCAN) , 而当查询条件为B=5时, 已经采用全表扫描 (TABLE ACCESS FULL) , 说明直方图起了作用。

当Where子句引用了列值分布存在明显偏差的列时, 应该使用直方图来帮助优化器来修正执行路径, 使优化器选择不同的执行计划。

参考文献

[1]谭怀远.让Oracle跑得更快:Oracle 10g性能分析与优化思路.北京:电子工业出版社, 2010.

[2][德]Norbert Debes.未公开的Oracle数据库秘密.蒋海鸥, 李浩, 金海, 等, 译.北京:人民邮电出版社, 2011.

[3]汪照东.Oracle 11g数据库管理与优化宝典.北京:电子工业出版社, 2008.

关于数据库索引的探讨 第4篇

【关键词】数据库索引；聚集索引；非聚集索引；查询优化

由于计算机网络技术的迅猛发展，企业间数据交流的各种数据量的急剧增长，不但要求处理的结果要准确，而且也要求处理速度及时，这对数据库的处理能力能力方面提出了更高的要求，如何设置有效的数据库索引达到数据库优化是本文要讨论的重点。

应用索引的过程其实类似于查新华字典，比照数据库的概念，新华字典里的拼音检字法和部首检字法就是新华字典的两种不同的索引，而新华字典的正文则相当于表同时创建索引并不会改变表中的数据的物理位置，它只是创建了一个新的数据结构指向数据表。比起逐一查阅字典正文查找某一个具体的汉字，显然不管使用哪种检字法都可以很快地在字典正文中找到这个汉字，这就是使用索引的给我们带来的好处。如何准确高效地从海量的信息中查询到想要的数据，已成为数据库设计人员的首要任务。

所以我们可以从逻辑上简单地认为，索引是一个单独的、物理的数据结构，它主要包含两列内容，第一列是从表或视图中的列或列的组合所生成的键值的集合，且根据键值以升序或降序排列。这一列类似于新华字典的音序，它以字母升序排列，即A在最前，而Z在最后。索引的另外一列则是指向表中这些值的数据页的逻辑指针的集合。这一列则类似于对应音序的页码。索引依赖于表，作为表的组成部分，由数据库系统自动维护，是对数据库表中一个或多个列的值进行排序的数据结构，不同的索引对应不同的排序方法。一个表的存储是由两部分组成的，一部分是用来存放数据的数据页面，另一部分是用来存放索引的索引索引页面，通常索引页面比数据页面小得多。

假设表中的数据在磁盘上存储是有序的，那么当在数据库在进行插入数据、删除数据和更新数据时，则一定会导致数据的顺序会发生变化，为了保证数据的连续性和有序性，就需要重新移动数据，改变它们的物理位置，而种移动将会导致增大磁盘的I/O操作，使得整个数据库运行非常缓慢；使用索引的主要目的是使数据逻辑有序。为了实现数据逻辑有序，实际上索引的物理结构是一个双向链表，使用双向链表来保证数据的逻辑顺序，如果要对表中的一个已有结点进行更新则仅需修改该节点的前驱和后继，而且无需修改该节点的物理位置；如果要在两个节点中插入一个新的节点只需修改节点的前驱和后继，而且无需修改新节点的物理位置；如果要删除一个已有结点，则仅需修改其前驱结点的后继为该被删除结点的后继。总的来说，索引保存着具体数据的物理地址值。

索引从大的方面分为聚集索引和非聚集索引。所谓聚集索引是指表中数据的物理顺序是和索引的顺序是一至的，数据页是聚集索引的叶节点，数据页之间通过双向链表的形式连接起来，而且实际的数据都存储在数据页中。查询时，数据库首先根据索引查找，找到索引值后，接着查找该索引的数据页（叶节点）获取具体数据。如果没有索引，则查询时会进行全表的遍历。第二类索引则称为非聚集索引，非聚集索引是物理存储不按照索引排序，非聚集索引的叶节点（IndexLeafPages）包含着指向具体数据行的指针或聚集索引，数据页之间没有连接是相对独立的页。具体地来说，非聚集索引又分为：①堆表非聚集索引在没有聚集索引的情况下，表中的数据页是通过堆（Heap）形式进行存储，堆是不含聚集索引的表；SQLServer中的堆存储是把新的数据行存储到最后一个页中。非聚集索引通过双向链表连接，而叶节点包含指具体数据行的指针。堆表中查询信息时，首先遍历索引，获取到指针信息，再根据指针信息获取相应数据页中的数据。②聚集表非聚集索引当表上存在聚集索引时，任何非聚集索引的叶节点就不是指针值，而是包含聚集索引的索引值。非聚集索引依然通过双向链表连接，但叶节点包含的是索引表的索引值。在聚集表中查询信息时，首先遍历索引，获取索引值，然后根据索引值获取相应数据页中的数据。

数据库查询表主要通过以下五种方式：

①TableScan：扫描整个表，这个操作将会逐行检查整个表，直到找到所匹配的记录行或者扫描完整个表。可以看出，这种查找记录的方式效率是最差的。

②IndexScan：根据索引，按照一定的算法从表中过滤出来一部分记录，在过滤出来的这一部分记录中进行查找所匹配的记录行，显然这种方式比第一种方式的查找范围要小，因此比TableScan的查找效率高。

③IndexSeek：根据索引，直接定位（获取）记录的存放位置，然后根据获取的记录的存放位置，直接取得记录，因此，比TableScan、IndexScan快。

④ClusteredIndexScan：与TableScan相似，这种方式也是要遍历整个表，但是它与TableScan不同的是数据表中的记录已经按照聚集索引来排列了，即记录实际就是按聚集索引的来顺序存放的。而TableScan扫描的表只是没有建立聚集索引，表中的记录没有按照一定的顺序存放，因此这两个操作本质上是一样的。

⑤ClusteredIndexSeek：这种方式是直接根据聚集索引获取记录，因为表中的记录已经按照聚集索引排列了，所以是最快的查询方法。

一个表是不是索引越多越好呢，当然不是。因为增加索引后，会增加维护该索引的时空开销，修改数据表时，必须要更新相应字段的索引。当一个表中的索引过多时，也严重会影响性能。一般会考虑在经常查询的列上建立相关索引并及时删除不需要的索引。

总之，索引使数据库引的查询操作执行的速度更快，它可以有针对性的数据检索，而不是简单地整个表扫描（FullTableScan）。在数据库中，为表添加必要的索引会提高查询的执行效率，但是过多的索引必然需要更新和维护索引表，这将会导致系统性能下降，所以必须控制索引的数量及时删除不必要的索引。

参考文献

[1]曹素丽，杨延广.ORACLE数据库索引的设计与维护研究[J].微型电脑应用，2012（11）：29-31.

[2]马守东.关系数据库索引的研究和探索[J].信息与电脑（理论版），2011（10）：159-160.

[3]张效尉，姜静.内存数据库技术研究[J].软件导刊，2011 （10）：147-148

[4]涂刚，刘华清，傅伟.数据表结构的研究[J].天水师范学院学报，2009（5）：86-88.

[5]岳国华.提高ORACLE8i数据库响应速度的若干技术对策[J].计算机应用与软件，2004（5）：110-112.

oracle数据库索引失效 第5篇

关键词：索引块,索引段,平衡树

1. 前言

在Oracle数据库中,当用户创建索引时,Oracle会自动地在表空间中创建索引段来存储索引的数据。用户可以通过以下方式控制索引段的空间分配和使用:

◆设置索引段的存储参数来控制如何为此索引段分配数据扩展

◆为索引段设置PCTFREE参数,来控制组成数据扩展的各个数据块的可用空间情况。

索引段使用的表空间既可以是索引所有者的默认表空间,也可以是在CREATE INDEX语句中指定的表空间。索引无需和其相关的表位于同一表空间中。相反,如果将索引与其相关表存储在不同磁盘上能够提升使用此索引的查询性能,因为此时Oracle能够并行地访问索引及表数据。

2. PCTFREE和PCTUSED参数

2.1 PCTFREE参数

PCTFREE参数用来设置一个数据块中至少需要保留多少可用空间(百分比值),为数据块中已有数据更新时可能发生的数据量增长做准备。例如,当用户用CREATE TABLE语句创建表时指定了以下参数:

PCTFREE 20

这个参数设定了此表对应的数据段中的每个数据块至少保留20%的可用空间,以备块中已有数据更新时使用。只要数据块中行数据区与数据块头的容量之和不超过数据块总容量的80%,用户就可以向其中插入新数据,数据行被放入行数据区,相关信息被写入数据块头。

2.2 PCTUSED参数

PCTUSED参数用于决定一个数据块是否可被用于插入新数据,她的依据是数据区与数据块头的容量之和占数据块全部容量的最大百分比。当一个数据块中的可用空间比例小于PCTFREE参数的规定时,Oracle就认为此数据块无法被用于插入新数据,直到数据块中的占用容量比例小于PCTUSED参数的限定。在占用容量比例大于PCTUSED参数的限定之前,Oracle只在更新数据块内已有数据时才会使用此数据块的可用空间。例如,当用户用CREATE TABLE语句创建表时指定了以下参数:

PCTUSED 40

在例子中,当此表的某数据块占用容量比例高于40%时,Oracle不会将此数据块用于插入新数据行。

3. 索引如何存储

3.1 索引块的格式

一个数据块内可用于存储索引数据的空间等于数据块容量减去数据块管理开销,索引条目管理开销,rowid,及记录每个索引值长度的1字节。

当用户创建索引时,Oracle取得所有被索引列的数据并进行排序,之后将排序后索引值和与此值相对应的rowid按照从下到上的顺序加载到索引中。例如,以下语句:

Oracle先将employees表按last_name列排序,再将排序后的列及相应的rowid按从下到上的顺序加载到索引中。使用此索引时,Oracle可以快速地搜索已排序的last_name值,并使用相应的rowid去定位包含用户所查找的last_name值的数据行。

3.2 索引的内部结构

Oracle使用平衡树存储索引以便提升数据访问速度。当不使用索引时,用户必须对数据进行顺序扫描来查找指定的值。如果有n行数据,那么平均需要扫描的行为n/2。因此当数据量增长时,这种方法的开销将显著增长。

如果将一个已排序的值列划分为多个区间,每个区间的末尾包含指向下个区间的指针,而搜索树中则保存指向每个区间的指针。此时在n行数据中查询一个值所需的时间为log(n)。这就是Oracle索引的基本原理。

在一个平衡树索引中,最底层的索引块(叶块)存储了被索引的数据值,以及对应的rowid。叶块之间以双向链表的形式相互连接。位于叶块之上的索引块被称为分支块,分枝块中包含了指向下层索引块的指针。如果被索引的列存储的是字符数据,那么索引值为这些字符数据在当前数据库字符集中的二进制值。

对于唯一索引,每个索引值对应着唯一的一个rowid。对于非唯一索引,每个索引值对应着多个已排序的rowid。因此在非唯一索引中,索引数据是按照索引键及rowid共同排序的。键值全部为NULL的行不会被索引,只有簇索引例外。在数据表中,如果两个数据行的全部键值都为NULL,也不会与唯一索引相冲突。

3.3 索引的属性

有两种类型的索引块:◆用于搜索的分支块

◆用于存储索引数据的叶块

(1)分支块

分支块中存储以下信息:

◆最小的键值前缀,用于在本块的两个键值之间做出分支选择

◆指向包含所查找键值的子块的指针

包含n个键值的分支块含有n+1个指针。键值及指针的数量同时还受索引块容量的限制。

(2)叶块

所有叶块相对于其根分支块的深度是相同的。叶块用于存储以下信息:

◆数据行的键值

◆键值对应数据行的ROWID

所有的键值-ROWID对都与其左右的兄弟节点向链接,并按照(key,ROWID)的顺序排序。

3.4平衡树结构的优势

平衡树数据结构具有以下优势:

◆平衡树内所有叶块的深度相同,因此获取索引内任何位置的数据所需的时间大致相同。

◆平衡树索引能够自动保持平。

◆平衡树内的所有块容量平均在总容量的3/4左右。

◆在大区间范围内进行查询时,无论匹配个别值还是搜索一个区间,平衡树都能提供较好的查询性能。

◆数据插入,更新,及删除的效率较高,且易于维护键值的顺序。

◆大型表,小型表利用平衡树进行搜索的效率都较好,且搜索效率不会因数据增长而降低。

参考文献

[1]赵伯山Oracle 9i中文版实用培训教程[M].清华大学出版社2002