drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
commit;
--请从这里开始注意累加的时间(从建索引到插入记录完毕)
set timing on
create index idx_t_owner on t(owner);
create index idx_t_obj_name on t(object_name);
create index idx_t_data_obj_id on t(data_object_id);
create index idx_t_created on t(created);
create index idx_t_last_ddl on t(last_ddl_time);
--语句1(t表有6个索引)
insert into t select * from t;
commit;
--以下进行试验2
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
commit;
---也从这里开始这里开始注意累加的时间(从插入记录完毕到建索引完毕)
set timing on
--语句1(t表有6个索引,此时先不建)
insert into t select * from t;
create index idx_t_owner on t(owner);
create index idx_t_obj_name on t(object_name);
create index idx_t_data_obj_id on t(data_object_id);
create index idx_t_created on t(created);
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
commit;
--请从这里开始注意累加的时间(从建索引到插入记录完毕)
set timing on
create index idx_t_owner on t(owner);
create index idx_t_obj_name on t(object_name);
create index idx_t_data_obj_id on t(data_object_id);
create index idx_t_created on t(created);
create index idx_t_last_ddl on t(last_ddl_time);
--语句1(t表有6个索引)
insert into t select * from t;
commit;
--以下进行试验2
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
commit;
---也从这里开始这里开始注意累加的时间(从插入记录完毕到建索引完毕)
set timing on
--语句1(t表有6个索引,此时先不建)
insert into t select * from t;
create index idx_t_owner on t(owner);
create index idx_t_obj_name on t(object_name);
create index idx_t_data_obj_id on t(data_object_id);
create index idx_t_created on t(created);
create index idx_t_last_ddl on t(last_ddl_time);
解析:一个表在建立多个索引情况下,插入数据统计时间。
一个表先插入数据,后建立索引。
前者时间远大于后者,因为索引是有顺序的,当插入数据时候也需要维护顺序,当大量插入数据时候就会产生偏移,插入一个会维护一次,所以速度慢。
解决:做多个数据库,生产数据库和查询数据库,生产数据库不建索引,所有的查询动作都在查询数据库实现。