Oracle 11G - Performance effect of indexing at insert -

objective

verify if true insert records without pk/index plus create thme later faster insert pk/index.

note
point here not indexing takes more time (it obvious), total cost (insert without index + create index) higher (insert index). because taught insert without index , create index later should faster.

environment

windows 7 64 bit on dell latitude core i7 2.8ghz 8g memory & ssd hdd
oracle 11g r2 64 bit

background

i taught insert records without pk/index , create them after insert faster insert pk/index.

however 1 million record inserts pk/index faster creating pk/index later, approx 4.5 seconds vs 6 seconds, experiments below. increasing records 3 million (999000 -> 2999000), result same.

conditions

• the table ddl below. 1 bigfile table space both data , index.
  (tested separate index tablespace same result & inferior overall perforemace)
• flush buffer/spool before each run.
• run experiment 3 times each , made sure results similar.

sql flush:

alter system checkpoint; alter system flush shared_pool; alter system flush buffer_cache; 

question

would true "insert witout pk/index + pk/index creation later" faster "insert pk/index"?

did make mistakes or missed conditions in experiment?

insert records pk/index

truncate table tbl2; alter table tbl2 drop constraint pk_tbl2_col1 cascade; alter table tbl2 add  constraint pk_tbl2_col1 primary key(col1) ;  set timing on insert tbl2 select i+j, rpad(to_char(i+j),100,'a') (   data2(j) (       select 0 j dual       union       select j+1000 data2 j < 999000   )   select j data2 ), (   data1(i) (       select 1 dual       union       select i+1 data1 < 1000   )   select data1 ); commit;  1,000,000 rows inserted. elapsed: 00:00:04.328 <----- insert records pk/index 

insert records without pk/index , create them after

truncate table tbl2; alter table &tbl_name drop constraint pk_tbl2_col1 cascade;  set timing on insert tbl2 select i+j, rpad(to_char(i+j),100,'a') (   data2(j) (       select 0 j dual       union       select j+1000 data2 j < 999000   )   select j data2 ), (   data1(i) (       select 1 dual       union       select i+1 data1 < 1000   )   select data1 ); commit; alter table tbl2 add constraint pk_tbl2_col1 primary key(col1) ;  1,000,000 rows inserted. elapsed: 00:00:03.454 <---- insert without pk/index  table tbl2 altered. elapsed: 00:00:02.544 <---- create pk/index 

table ddl

create table tbl2 (     "col1" number,     "col2" varchar2(100 byte),     constraint "pk_tbl2_col1" primary key ("col1") ) tablespace "tbs_big" ; 

it's true faster modify table if not have modify 1 or more indexes , possibly perform constraint checking well, largely irrelevant if have add indexes. have consider complete change system wish effect, not single part of it.

obviously if adding single row table contains millions of rows foolish drop , rebuild indexes.

however, if have empty table going add several million rows can still slower defer indexing until afterwards.

the reason such insert best performed direct path mechanism, , when use direct path inserts table indexes on it, temporary segments built contain data required build indexes (data plus rowids). if temporary segments smaller table have loaded faster scan , build indexes from.

the alternative, if have 5 index on table, incur 5 full table scans after have loaded in order build indexes.

obviously there huge grey areas involved here, done for:

1. questioning authority , general rules of thumb, and
2. running actual tests determine facts in own case.

edit:

further considerations -- run backup while indexes dropped. now, following emergency restore, have have script verifies indexes in place, when have business breathing down neck system up.

also, if absolutely determined not maintain indexes during bulk load, not drop indexes -- disable them instead. preserves metadata indexes existence , definition, , allows more simple rebuild process. careful not accidentally re-enable indexes truncating table, render disabled indexes enabled again.