Take the 2-minute tour ×

I need to insert data and the amount of data is pretty huge. There are some indexes that I need to define on this table. So, my question is...which is better and why

  1. Create table --> Insert Data --> Create Indexes
  2. or, Create table --> Create Indexes --> Insert Data

Thnx, Vabs

share|improve this question
Possible duplicate of stackoverflow.com/q/1983979/257972 , which also explains why... –  FallenAngel Apr 27 '11 at 10:40

3 Answers 3

up vote 5 down vote accepted

choice #1 will be faster because it's more efficent to build an index from scratch than by adding records one after another. That again is because the records will be sorted beforehand and the index blocks are filled up and written in an ordered manner. In oracle you can also use ´create index ... nologging´ to avoid creating the redo log.

share|improve this answer
And why is that? –  Magnus Apr 27 '11 at 10:34
@Magnus: because indexes will only be maintained once. If the indexed are present during all the insert, each INSERT statement requires an update of the index. Creating the index in "one go" for all rows is less overhead than updating it each time a row is inserted. –  a_horse_with_no_name Apr 27 '11 at 10:50

When you build an index oracle can sort the whole table in it's temporary sort space, then build the index from this.

If you have the index built already, then for each row inserted, it has to lookup the position in the index where the new value will go, then add the value to the index.

So it's a lot quicker to load the data then build the index

share|improve this answer

Choice #1 will usually be faster, but there are cases where Choice #2 could be faster - i.e. where you have multiple INSERTs, and one or more subsequent inserts query the same table and use one or more of the indexes.

Another consideration, of course, is if there will be concurrent activity from other sessions before/while the data is loaded - they might suffer if they have no indexes to take advantage of.

share|improve this answer

Your Answer


