DDL take a long time (ADD COLUMN)
Oracle Version: Oracle9i Enterprise Edition Release 220.127.116.11.0
I need to add a column in table (11 kk rows), see below:
ALTER TABLE NOTAS_FISCAIS ADD (ID_QUEBRA_AGRUP VARCHAR2(50) DEFAULT 'X' NOT NULL);
But this DDL take a long time, how can I do for this DDL to be faster? Is there any option?
Thanks in advance.
1- Add column as allowing Null values, no default.
2- at a time of low database activity run an update command populating all rows with the default value.
3- Alter the column setting it as Not Null and with a default value.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
you can use dbms_redefinition, it wont be faster but downtime is very low
Click Here to Expand Forum to Full Width