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

Question

I needed to fill a table with a huge amount of data (many hours of loading) in an Oracle database, and I was wondering what would be faster if I created an index in the table before or after loading. Initially, I thought that inserting into an indexed table is punishable, but then if I create an index with a full table, it will take a lot of time. What's better?

Answer 1

Creating indexes after loading data is much faster. If you load data into a table with indexes, loading will be very slow due to constant index updates. If you create the index later, it can only be effectively populated once (which, of course, can take some time, but the total should be less).

Similar logic applies to constraints. Also enable them later (unless you expect the data to nullify the constraints and want to know what at an early stage).

Answer 2

The only reason you might want to create an index is to force the use of unique constraints. Otherwise, loading is much faster with a bare table - no indexes, no restrictions, triggers are not included.

Answer 3

Creating an index after loading data is the recommended practice for bulk workloads. You must be sure of the quality of the input, especially if you use unique indexes. The absence of an index means that data validation that occurs due to the presence of unique indexes will not occur. Another issue you should consider: do you have a one-time download or will it be a common thing? If this is common, you can drop the indexes before each data load and recreate them after a successful load.