Is okay to create indexes on a table while insertion

Question

Is it okay to create an index on a table while , lets say when are there some tasks which creates some new rows into the table at the same time?? Would there be any locking issues???

EX: FEEDBACK TABLE --> creating an index on (Name, feedbackrule) while there are any inserts happening simultaneously , is this BAD?? if so what.

I'm assuming, Oracle will just not use this index when the inserts are happening, later this will be used.

Answer 1

Normally, creating an index requires locking the table, so all the DML operations would block; and if there are active transactions on the table when you initiate the index creation, you'd likely get the error "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired".

If the table is small, this may not be much of an issue - transactions would just be blocked for a few moments. But if it is very large it would be a bad idea to try creating an index while the table is in use.

However, if you using Enterprise Edition, you can add the ONLINE keyword to your CREATE INDEX statement, which will allow transactions to proceed against the table while the index is building. It may still cause slower performance.