-
DDL take a long time (ADD COLUMN)
Environment:
Oracle Version: Oracle9i Enterprise Edition Release 9.2.0.7.0
OS: Linux
Hi all,
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.
Leonardo.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|