<div>
mbevilacqua,<br />
&nbsp; &nbsp; Because Oracle must maintain a read-consistent view of this object while you are modifying it, there will be significant rollback generated and there will be enough redo to maintain transactional and instance-recovery. &nbsp;Since the insertion of this column may cause row chaining, it is possible that the changes will be significant.<br />
<br />
&nbsp; &nbsp;An alternative to using a default would be to leave the column as nullable and use a view over top of the field to translate nulls into 0. &nbsp;This would reduce storage and eliminate the issue you are concerned about.<br />
<br />
Good luck!<br />

</div>


<div>
Thanks doc.<br />
How about adding the column as NULLable, update the value to 0 in batches, and then alter the column to NOT NULLable? Will this approach address both RBS and REDO concerns?
</div>


<div>
<div class="content wysiwyg-content">
We are conducting a database schema migration and need to create a new column that is not null with a default value of 0. The table being altered will be signiicant in row count, up to 200 million rows with large width (30 columns, all ints and numbers). We believe that DDL is not logged, yet what impact does a default value cause to the redo log and rollback segments when altering a table? We are concerned about the impact to the redo and RBS filling up. Is this a valid concern? Any suggestions on ways to implement this to minimize the impact to our customers production systems?<br />

<pre><code class="code-1 nolinks" id="code-124606"><span>ALTER TABLE foo</span>
<span> ADD (new_column  INTEGER                           </span>
<span>       DEFAULT 0</span>
<span>       NOT NULL);</span>
</code></pre>
</div>
</div>


We are conducting a database schema migration and need to create a new column that is not null with a default value of 0. The table being altered will be signiicant in row count, up to 200 million rows with large width (30 columns, all ints and numbers). We believe that DDL is not logged, yet what impact does a default value cause to the redo log and rollback segments when altering a table? We are concerned about the impact to the redo and RBS filling up. Is this a valid concern? Any suggestions on ways to implement this to minimize the impact to our customers production systems?

Oracle - DDL impact to redo with not null column with default

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.