Skip to Content

Introduction


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:

    1. Build that index in SQLPLUS using Oracle’s parallelism
    2. 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.

     

     

    To report this post you need to login first.

    1 Comment

    You must be Logged on to comment or reply to a post.

    1. Arun Varadarajan
      Bala,
      Another workaround which I thought about when reading your blog with regard to adding columns to huge ODS Tables could be to :
      1. Create a copy of the active table on the database and make a note of the indices
      2. Make sure that all deltas are loaded
      3. Drop the change log.
      4. Change the structure of the DSO through a transport or directly
      5. now fire inserts from the copy table directly into the active table of the new DSO structure
      6. Continue deltas

      This of course works if the new column is supposed to be blank – if you have simple rules – then you may possibly code it in the SQL or insert the data and then run full repair loads .

      This procedure would not require any activation time – it would only require time for the inserts which is much faster. And we have a backup too…

      Once this is done – the backup table can be dropped. The index would have to be created again – possibly from SQLPlus / TOAD and then if the index has the same naming convention as the actual index created by SAP BW then the index is not created again but the DDIC definitions are updated automatically on transport.

      (0) 

    Leave a Reply