If you add a column, then the initial value of each row for the new column is null unless you specify the DEFAULT clause. In this case, Oracle Database <u>updates</u> each row in the new column with the value you specify for DEFAULT.
When DEFAULT is specified as in Scenario 1, Oracle <u>updated</u> each row in the new column with the value we specified for DEFAULT. In scenario 2, we didn’t specify DEFAULT so the new column accepted nulls and Oracle didn’t update each row. This is why scenario 2 completed in less than a second and also AVG_ROW_LEN didn’t change.
h3. DSO Activation
Recently I overheard a statement that
- the activation time would remain the same anytime we try to activate that DSO regardless of what the change is.
Statement (1) is a fact when activated after adding a column with DEFAULT option; statement (2) is not a fact. For example, let us say we want to add an index to that DSO in RSA1; and before we activate, let us say we follow these steps:
Build that index in SQLPLUS using Oracle’s parallelism
After index is built, activate DSO. This activation would probably take a few seconds and not an hour. (Note: If the index is not built in SQLPLUS, then the activation may take much, much longer than 1 hour because it would wait for the index build job(single threaded, no parallelism in SAP) to complete).
In addition to the difference in duration, the tasks performed by the activation is different depending on what is changed. For example, activation of DSO due to index addition doesn’t change the structure or data; however activation due to column addition not only changes the structure but also updates the value of the new column in all existing records. The index is built outside table data/structure whereas the column is added to the table structure.