Is it better to create Oracle SQL indexes before or after data loading?

Question

I need to populate a table with a huge amount of data (many hours loading) on an Oracle database, and i was wondering which would be faster, to create an index on the table before loading it or after loading it. I initially thought that inserting on an indexed table is penalized, but then if i create the index with the full table, it will take a lot of time. Which is best?

Answer 1

Creating indexes after loading the data is much faster. If you load data into a table with indexes, the loading will be very slow because of the constant index updates. If you create the index later, it can be efficiently populated just once (which may of course take some time, but the grand total should be smaller).

Similar logic applies to constraints. Also enable those later (unless you expect data to fail the constraints and want to know that early on).

Answer 2

The only reason why you might want to create the index first is to enforce unique constraints. Otherwise, loading is much faster with a naked table - no indexes, no constraints, no triggers enabled.

Answer 3

Creating an index after the data load is the recommended practice for bulk loads. You must be sure about the incoming data quality though especially if you are using unique indices. The absence of the index means that data validation that occurs due to the presence of unique indexes will not happen. Another issue for you to consider is whether you have a one time load operation or is it going to be a regular affair? If it is a regular affair, then you can drop the indexes before each data load and recreate them after a successful load.