一、创建索引对执行计划的影响
在sql开始执行之前,oracle会确定sql语句的执行计划,并按照执行计划的步骤访问相应的表和索引。
一旦执行计划确定下来,oracle会按照这个执行计划完成sql语句的执行,在sql语句执行开始之后建立的索引不会改变sql语句的执行计划。
因此,创建索引不会对执行计划有任何的影响,也就不会对运行中的sql语句有影响。下面通过一个例子简单验证一下:
sql> create table test (id number, fid number, name varchar2(30), bid_columns char(2000));
table created.
sql> insert into test values (1, 0, 'object', '0');
1 row created.
sql> insert into test values (2, 1, 'table', '0');
1 row created.
sql> insert into test values (3, 1, 'index', '0');
1 row created.
sql> insert into test values (4, 1, 'view', '0');
1 row created.
sql> insert into test values (5, 1, 'synonym', '0');
1 row created.
sql> insert into test values (6, 1, 'source', '0');
1 row created.
sql> insert into test select 20000+rownum, 2, table_name, '0' from dba_tables;
874 rows created.
sql> insert into test select 30000+rownum, 3, index_name, '0' from dba_indexes;
1074 rows created.
sql> insert into test select 40000+rownum, 4, view_name, '0' from dba_views;
2929 rows created.
sql> insert into test select 50000+rownum, 5, table_name, '0' from dba_synonyms;
2437 rows created.
sql> insert into test select 60000+rownum, 6, name, '0' from dba_source;
99717 rows created.
sql> commit;
commit complete.
sql> set timing on
sql> select count(*) from test
2 start with id = 1
3 connect by prior id = fid;
count(*)
----------
107037
elapsed: 00:02:03.84
构造一个树状查询,然后记录这个树状查询的运行时间。
sql> select count(*) from test
2 start with id = 1
3 connect by prior id = fid;
count(*)
----------
107037
elapsed: 00:05:26.15
再次运行查询,在查询运行开始,马上在另一个session创建索引。通过观察执行时间可以发现,创建索引不会对运行中的sql语句带来性能提升,而且很可能由于系统资源的争用造成查询速度变慢。如果在io分布的比较合理的系统中,可以看到,创建索引可以很快完成,而且随后执行同样的查询由于会使用索引,也会很快的返回结构,但是索引的创建不会加快已经处于运行状态的语句的速度。
session2:
sql> set timing on
sql> create index ind_test_id on test(id) tablespace users;
索引已创建。
已用时间:000: 01: 56.92
sql> create index ind_test_fid on test(fid) tablespace users;
索引已创建。
已用时间: 00: 02: 00.57
建立索引后,同样的查询速度得到明显的提升。
sql> select count(*) from test
2 start with id = 1
3 connect by prior id = fid;
count(*)
----------
107037
已用时间: 00: 01: 02.11
上面建立两个索引的语句和查询语句是在单独的session2上运行的。session2上的三个操作——创建两个索引和执行相同的查询语句——都执行完成了,而第一个会话的的运行结果仍然没有返回。
二、创建索引对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列的外键。但是这里并没有在外键列上建立索引。
向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操作在索引创建后迅速返回。
三、小结
创建索引对于用户已发出的正在运行的sql不会带来性能的提升。这是由于用户执行的语句要按照执行计划来运行,而执行计划在运行开始的时候就确定下来了,且不会在sql语句的运行过程中发生变化。
对于sql执行过程中,oracle内部执行的用于维护的sql语句,是有可能从新创建的索引中获得性能提升的。
新闻热点
疑难解答