When is the best time to create indexes?

[PFC]

I've heard that it's best to create an index on an empty table before loading the table via sqlldr.

Fact/Fiction or does it depend on the data? Any tips will be appreciated.

[mbaliga]

Hi,

You should create the index after the data is loaded. This will ensure a balanced index tree to start with.

Also create the index when there is low system activity.

Baliga

[chrisrlong]

Absolutely wait until the table is populated, as mbaliga stated. Not only because of the balanced tree issue, which is *very* important, but also because it is faster to process.

Create Index
Load data
Analyze

is SLOWER than

Load data
Create Index
Analyze

- Chris

[pando]

it's basically because index is a independant segment therefore if you create index first then load data you would essentially inserting *twice*, both data & index segments