oracle更新时可以加索引吗,索引是否也能提高UPDATE,DELETE,INSERT速度

索引除了提高查询速度外，也能提高DML操作的速度，如有不对之处求指正。。

例如，

操作1：UPDATE,DELETE,INSERT 中含WHERE条件，且where后面的字段建索引，DML操作执行计划显示走索引。DML的时间是TIME1。

操作2：UPDATE,DELETE,INSERT 中含WHERE条件，where后面的字段没索引，DML操作执行计划显示走全表。DML的时间是TIME2。

TIME1

以UPDATE为例：

SQL> set timing on;

SQL> --加索引

SQL> drop table t;

Table dropped

Executed in 16.099 seconds

SQL> create table t(id,name) as select object_id,object_name  from dba_objects;

Table created

Executed in 0.109 seconds

SQL> insert into t select * from t;

11264 rows inserted

Executed in 0.031 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> insert into t select * from t;

22528 rows inserted

Executed in 0.031 seconds

SQL> commit;

Commit complete

Executed in 0.015 seconds

SQL> insert into t select * from t;

45056 rows inserted

Executed in 0.047 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> insert into t select * from t;

90112 rows inserted

Executed in 0.094 seconds

SQL> commit;

Commit complete

Executed in 0.015 seconds

SQL> insert into t select * from t;

180224 rows inserted

Executed in 0.172 seconds

SQL> commit;

Commit complete

Executed in 0.015 seconds

SQL> insert into t select * from t;

360448 rows inserted

Executed in 0.546 seconds

SQL> commit;

Commit complete

Executed in 0.016 seconds

SQL> insert into t select * from t;

720896 rows inserted

Executed in 0.609 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> select count(*) from t;

COUNT(*)

----------

1441792

Executed in 0.062 seconds

SQL> create index idx_t on t (id);

Index created

Executed in 5.678 seconds

SQL> analyze table t compute statistics for all columns for all indexes ;

Table analyzed

Executed in 9.126 seconds

SQL> update   t set name='tree' where id =20;   --走索引

128 rows updated

Executed in 0.016 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> --不加索引

SQL> drop table t;

Table dropped

Executed in 16.068 seconds

SQL> purge recyclebin;

Done

Executed in 0.093 seconds

SQL> create table t(id,name) as select object_id,object_name  from dba_objects;

Table created

Executed in 0.109 seconds

SQL> insert into t select * from t;

11264 rows inserted

Executed in 0.032 seconds

SQL> commit;

Commit complete

Executed in 0.016 seconds

SQL> insert into t select * from t;

22528 rows inserted

Executed in 0.031 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> insert into t select * from t;

45056 rows inserted

Executed in 0.046 seconds

SQL> commit;

Commit complete

Executed in 0.016 seconds

SQL> insert into t select * from t;

90112 rows inserted

Executed in 0.093 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> insert into t select * from t;

180224 rows inserted

Executed in 0.156 seconds

SQL> commit;

Commit complete

Executed in 0.015 seconds

SQL> insert into t select * from t;

360448 rows inserted

Executed in 0.344 seconds

SQL> commit;

Commit complete

Executed in 0.016 seconds

SQL> insert into t select * from t;

720896 rows inserted

Executed in 1.716 seconds

SQL> commit;

Commit complete

Executed in 0.015 seconds

SQL> select count(*) from t;

COUNT(*)

----------

1441792

Executed in 0.063 seconds

SQL> update t set name='tree' where id =20;  --走全表

128 rows updated

Executed in 0.062 seconds

SQL> commit;

Commit complete

Executed in 0.016 seconds