创建ORACLE索引对ORACLE内部机制的影响
创建ORACLE索引可以提高数据库的查询效率，那么，创建ORACLE索引对ORACLE内部机制有什么影响呢？阅读下文，您就可以找到答案。
创建索引不会改变已经运行的SQL的执行计划。但是并不是说，创建索引不能给已经运行的SQL语句带来性能的提升。
下面看一个比较特殊的例子：
SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE TABLE TEST1 AS SELECT ROWNUM ID, ROWNUM FID, A.* FROM DBA_SYNONYMS A;
表已创建。
SQL> ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID);
表已更改。
SQL> ALTER TABLE TEST1 ADD CONSTRAINT FK_TEST1_FID FOREIGN KEY (FID) REFERENCES TEST(ID);
表已更改。
SQL> INSERT INTO TEST1 SELECT * FROM TEST1;
已创建1616行。
SQL> INSERT INTO TEST1 SELECT * FROM TEST1;
已创建3232行。
SQL> INSERT INTO TEST1 SELECT * FROM TEST1;
已创建6464行。
SQL> INSERT INTO TEST1 SELECT * FROM TEST1;
已创建12928行。
SQL> INSERT INTO TEST1 SELECT * FROM TEST1;
已创建25856行。
SQL> COMMIT;
提交完成。
SQL> DELETE TEST1;
已删除51712行。
SQL> COMMIT;
提交完成。
SQL> SET TIMING ON
SQL> DELETE TEST;
已删除6208行。
已用时间: 00: 00: 17.03
SQL> ROLLBACK;
回退已完成。
已用时间: 00: 00: 00.06
构造两张表，TEST1的FID建立了参考TEST表ID列的外键。但是这里并没有在外键列上创建ORACLE索引。
向TEST和TEST1表中填入一定数据量的数据，开始测试。这里测试的是删除TEST表的执行时间。首先将TEST1用DELETE命令删除，提交后计算删除TEST表的时间，大约需要17秒，然后将数据回滚。
下面准备进行第二次删除测试，所不同的是，在删除操作开始后，马上在另一个SESSION中给外键列增加索引，通过测试可以发现，几乎在索引创建完的同时，***个SESSION就返回了结果，删除需要的时间缩短到了3秒。
***个SESSION的删除语句：
SQL> DELETE TEST;
已删除6208行。
已用时间:? 00: 00: 03.00
第二个SESSION的索引创建语句：
SQL> CREATE INDEX IND_TEST1_FID ON TEST1(FID);
索引已创建
这个测试中索引的创建影响到了已经在运行的SQL语句，并明显地提高了执行效率。这个现象和上一篇文章中描述的观点并不冲突。对于用户发出的SQL语句，Oracle的执行计划是不变的，但是为了执行用户发出的SQL语句，Oracle在内部做了大量的操作，包括权限的检查、语法的检查、目标对象是否存在，以及维护数据的完整性等等。这个例子中，用户发出的SQL语句的执行计划没有改变，发生改变的是Oracle内部维护操作语句的执行计划。
如果在***个SESSION执行DELETE操作的同时，通过下面的SQL语句检查***个SESSION正在运行的语句，会发现下面的结果（9i及以前版本，如果是10g，则只能看到DELETE TEST）。
- SQL> SELECT SQL_TEXT FROM V$SESSION A, V$SQL B
- 2 WHERE A.SQL_HASH_VALUE = B.HASH_VALUE
- 3 AND A.SQL_ADDRESS = B.ADDRESS
- 4 AND A.SID = 17;
- SQL_TEXT
- ----------------------------------------------------------------------------
- select /**//*+ all_rows */ count(1) from "YANGTK"."TEST1" where "FID" = :1
这个SQL语句就是Oracle用来维护完整性的内部SQL。
回想一下我们的例子，建立了外键，但是没有建立索引。当每删除一条TEST的记录，Oracle都要检查这个主键是否在TEST1中被引用。由于没有索引，Oracle只能通过全表扫描来寻找TEST1中的记录。虽然TEST1没有记录，但是删除TEST时使用的是DELETE而不是TRUNCATE，因此TEST1的高水位线并没有下降，也就是说，每删除一条TEST的记录，都需要全表扫描一张拥有5万条数据的表，这就是为什么那个DELETE操作执行很慢的原因。
而我们建立的索引正是加快了这个步骤，Oracle内部维护的SQL语句在索引可用后选择了索引扫描，因此DELETE操作在索引创建后迅速返回。
【编辑推荐】
创建Oracle索引的方法
C#连接Oracle数据库查询数据
Oracle数据库备份的三个常见误区
Oracle自动备份数据库的三种方式
oracle RMAN备份的优化