Is there any downside to creating a index on a already populated date field?

Will the new index perform the same as it would if it was created when the table was?

Would it be costly from a database perspective to add this?

Edit : the table has just under 10 million entries.

  • Is the table Partitioned ? If so, you might consider a LOCAL index ...(if not, you might consider partitioning a large table like that ;) ) – Ditto Apr 4 '17 at 13:17
  • Haha it's not parititoned. I need to do that as well for future records. Thanks anyway – Michael Oddie Apr 4 '17 at 13:21

There is no downside in creating an index at a later moment. The index will perform the same way like it would when it was created when the table was created.

You mentioned that the table has already 10 million rows. Therefore I would suggest that you create it during a 'calm' period. This because Oracle needs to lock the table. This cannot be done if there is a transaction that does an insert, update, or delete in the table.

  • Great thanks. When you say calm do you mean when there isn't a big load on the database or when there's no records been added? – Michael Oddie Apr 4 '17 at 11:19
  • No records being edited, removed, or added. I added this to my answer. – Marco Apr 4 '17 at 11:47
  • In case of Enterprise edition index can be created with ONLINE option which will allow DML operations during the creation. – a1ex07 Apr 4 '17 at 14:36
  • is it possible that the index would be less fragmented if it's created later? – Joe Obbish Apr 4 '17 at 19:16
  • @JoeObbish in my experience, initially it will. But, as data is added/changed/delete, it will move to a more running state, which is not bad, and will still perform. I don't subscribe to the idea of continually rebuilding indexes under normal conditions. – unleashed Apr 7 '17 at 15:11

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.