Is it necessary to REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it?

I have a table with approximately 3.5 million rows in Oracle. Every week new data is recharged, truncating the table and then loading the data.

There are 2 or 3 indexes in the table. Should I delete and re-create the indexes or simply rebuild the ones I already have? , some indexed columns are encrypted with TDE.

Thank you

  • If I were you, I'd drop the indexes, truncate the table, load the data, recreate the indexes; doing that should speed up the data inserts.
    – Boneist
    Nov 20 '18 at 9:10

If your data is not sorted (by the indexed columns), the maintenance of indexes during the load of large amount of data may have some serious overhead because of the leaf node splits (not 90-10 splits), so:

  1. truncate the table
  2. alter index ... unusable on all indexes
  3. load data
  4. alter index ... rebuild on all indexes

This will work if SKIP_UNUSABLE_INDEXES is set to true, which is the default value, but keep this in mind:

If an index is used to enforce a UNIQUE constraint on a table, then allowing insert and update operations on the table might violate the constraint. Therefore, this setting does not disable error reporting for unusable indexes that are unique.

Alternatively, yes, you can drop the indexes before the load, and recreate them after.

  • Does this mean if data is sorted with respect to the index field then there is no performance gain if you make the index unusable before the load and rebuild it after the load?
    – miracle173
    Nov 20 '18 at 12:58
  • @miracle173 Here is a simple example: pastebin.com/7qNWiWTz Inserting unsorted data into an indexed table vs unindexed table + index rebuild: 47 seconds vs 8 seconds. Inserting sorted data into an indexed table vs unindexed table + index rebuild: 10 seconds vs 7 seconds. Nov 21 '18 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.