I have to populate a table with a lot of data (many hrs loading) with an Oracle database, and i'm wondering which may be faster, to produce a catalog up for grabs before loading it or after loading it. I initially believed that placing with an indexed table is punished, however basically produce the index using the full table, it will require considerable time. That is best?
Creating indexes after loading the information is a lot faster. Should you load data right into a table with indexes, the loading can be really slow due to the continual index updates. Should you produce the index later, it may be effectively populated only once (which might obviously take a while, however the grand total ought to be more compact).
Similar logic is applicable to constraints. Also enable individuals later (unless of course you anticipate data to fail the restrictions and wish to realize that in early stages).
The only real reason you might like to produce the index first would be to enforce unique constraints. Otherwise, loading is a lot faster having a naked table - no indexes, no constraints, no triggers enabled.
Creating a catalog following the data load may be the suggested practice for bulk loads. You have to make sure concerning the incoming data quality though especially if you work with unique indices. The lack of the index implies that data validation that happens because of the existence of unique indexes won't happen. Another problem that you should consider is whether or not you've got a once load operation or will it be considered a regular affair? If it's a normal affair, you'll be able to drop the indexes before each data load and recreate them following a effective load.