SchemaEditor improvements when dropping columns on Oracle
I’ve been recently working with a couple of customers who were dropping database columns from their schema on Oracle, and the drops were taking a long time to complete.
In one instance, the customer estimated it would take 50 hours to drop a bunch of columns from their contact table.
In another, a customer observed that dropping two columns from table_site_part took over two hours. Dropping 3 columns from table_contact took over 1 hour.
These long execution times can disrupt normal operations – especially those environments with limited maintenance windows.
Dovetail’s schema editing tool (Schema Editor) and Amdocs schema editing tools (ddcomp, SchemaManager) all do the same basic operations when it comes to dropping columns. Basically they all do this: ALTER TABLE table_name DROP COLUMN column_name;
There’s a better way.
Set Unused
On large tables the process of physically removing a column can be very time and resource consuming. For this reason you may decide to logically delete it:
ALTER TABLE table_name SET UNUSED (column_name);
Because this simply sets a flag on the column itself, this operation is very fast.
Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done using the following.
ALTER TABLE table_name DROP UNUSED COLUMNS;
On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option which forces a checkpoint after the specified number of rows have been processed.
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;
Dovetail SchemaEditor improvements
As of version 2.3.3, Dovetail SchemaEditor has a oracleDropColumnStrategy setting that is defined in the .SchemaEditor file.
<
oracleDropColumnStrategy
>
unused
</
oracleDropColumnStrategy
>
This setting allows Oracle users to choose whether columns are dropped or set to unused.
When this optional setting is set to unused, rather then dropping the column from the database table the column will be set as unused. Later the unused physical columns can be removed with a simple SQL operation.
Of course, SchemaEditor still deletes the data from the ADP tables – meaning that your schema stays correct, allowing it to still work correctly with all of your Clarify/Dovetail apps.
Refer to the SchemaEditor documentation for more details on this new setting.
Get It
If you’re a Dovetail SchemaEditor customer, you can download this new release from the My Products page in Dovetail SelfService.
Additional Reading
Basics on dropping columns:
http://www.oracle-base.com/articles/8i/dropping-columns.php
More details:
http://www.scribd.com/doc/16883365/Column-Drop-Oracle
Bunch of Ask Tom posts on this issue
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:623063677753
