创建ORACLE索引对ORACLE内部机制的影响

数据库
创建索引提高了数据库的查询效率,下文对创建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)。

  1. SQL> SELECT SQL_TEXT FROM V$SESSION A, V$SQL B  
  2.  
  3. 2 WHERE A.SQL_HASH_VALUE = B.HASH_VALUE  
  4.  
  5. 3 AND A.SQL_ADDRESS = B.ADDRESS  
  6.  
  7. 4 AND A.SID = 17;  
  8.  
  9. SQL_TEXT  
  10.  
  11. ----------------------------------------------------------------------------  
  12.  
  13. 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备份的优化

责任编辑:段燃 来源: 互联网
相关推荐

2010-10-26 16:33:54

创建Oracle索引

2010-11-16 10:21:25

Oracle创建表

2009-11-19 15:32:50

Oracle索引

2011-05-25 14:35:47

Oracle缓冲区调整性能

2010-03-31 15:38:33

Oracle用户

2011-05-25 14:42:37

Oracle空间管理系统性能

2009-06-30 15:02:41

磁盘排序Oracle数据库性能

2011-04-08 15:12:22

Oracle索引

2010-10-26 17:28:15

创建Oracle索引

2010-05-10 17:07:54

Oracle SQL条

2010-03-29 11:31:45

2010-10-26 17:34:03

Oracle索引

2010-04-12 16:50:47

Oracle索引聚簇表

2010-04-22 12:46:55

Oracle hint

2010-05-10 14:33:11

Oracle proc

2010-11-16 09:18:39

oracle重建索引

2010-10-27 13:47:50

Oracle索引

2017-09-22 11:01:00

Oracle数据库中直方图

2009-06-11 13:12:59

Oracle索引创建索引

2019-08-20 22:06:32

Oracle数据库索引
点赞
收藏

51CTO技术栈公众号