Problem solve Get help with specific problems with your technologies, process and projects.

Index on table that is truncated/repopulated daily

Since I truncate and repopulate the data in my table every day, is the index of much use?

I have a table that gets rebuilt every day after truncate. Have created a composite index idx1 on col1 and col2 (not clustered) on that and also two more indexes to speed up the processing.

This table is a continuously growing, like 400 new index values for idx1. And now it has around 170,000 records. So since I truncate and repopulate the data, is the index of much use? On truncate will the space utilized for index get released?

When you truncate the table, all but the INITIAL extent of the table will be released as the space is no longer needed. Fortunately, the same is true of the table's indexes as well. If you do not want to release space from the table and its indexes, use the REUSE STORAGE clause of the TRUNCATE TABLE command. You may (or may not) find it preferrable to keep the allocated space since it may be reused again.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.