Does creating index on table containing million of rows causes any issue? Any danger exists for doing the same in live production DB?

Question

Is there any dangers for creating index on a table containing millions of rows in live production db ? Does it causes any table locks or any performance issue while creating index? Is there any adverse effect it causes?

Answer 1

Yes -- it will create locks and impact other users unless you use the ONLINE keyword in the CREATE INDEX command. The ONLINE keyword will allow the index creation to happen without impacting DML operations on the table. DDL on the table will be impacted, even with the ONLINE keyword (but DDL should not be happening on your table in production, usually).

If there is a lot of DML going on, it will slow down your index creation.

Also, you'll be using system resources to build the index (I/Os and sorts, etc).