Dropping a Column from a Table

Dropping a Column from a Table

Administration Tips

Dropping a Column from a Table Dropping a column directly can only be done in 8i or above (see below for a workaround in earlier versions). The basic command is: ALTER TABLE BLAH DROP COLUMN BLAHCOL;

That command is a piece of DDL -so there's an implied commit after it, and its effects cannot therefore be reversed. It will cause a complete read of the referenced table, with each row having the offending piece of data physically removed from it, before being written back to disk. It's therefore going to cause a significant performance hit on your database as it runs (all that I/O being performed), and is likely to take an age to complete if the table is reasonably large. On the plus side, you are actually physically removing data from the table, so when the whole thing's finished, the table really will be smaller than it was originally.

Unfortunately, dropping a column from a large table generates a lot of redo (the before image of the column being dropped, and the after image of, practically, nothing)... and potentially, you could have an Instance crash in the middle of the operation. Instance Recoveries require all redo to be re-performed after the time of the last checkpoint -so if you're generating stacks of redo during a 'drop column' operation, there could be a significant Instance Recovery awaiting you.

To minimise that danger, the drop column syntax allows you to request additional checkpoints after every so-many rows. For example: ALTER TABLE BLAH DROP COLUMN BLAHCOL CHECKPOINT

1000;

...Now, after processing every 1000 rows, a checkpoint will be issued, thus keeping potential Instance Recoveries down to something manageable.

Another thing to watch out for: You can't drop a column that is used as the parent of a foreign key relationship. Doing so would create orphan records, and that's not allowed.

However, you can use this syntax: ALTER TABLE BLAH DROP COLUMN BLAHCOL CHECKPOINT

1000

CASCADE CONSTRAINTS;

...and that will drop the foreign key constraints for you, and thereby allow the nowstandalone column to be dropped without further protest. (You can use the 'cascade constraints' clause without the 'checkpoint' clause, of course, and vice versa).

Notice that in all these examples, the command is "drop column" -in the singular. You cannot drop more than one column at a time using this syntax. That's extremely unfortunate, because it means that the second and third (and so on) drop column Copyright Š Howard Rogers 2001

10/17/2001

Page 1 of 4

Dropping a Column from a Table

Administration Tips

statements will have to completely re-perform the full table scan that the first one occasioned. You could therefore expect to be there until Christmas waiting for the thing to finish.

Fortunately, but slightly confusingly, you could issue the following command to achieve a multi-column drop in one pass: ALTER TABLE BLAH DROP

(BLAHCOL,

BLAHCOL2, BLAHCOL3);

Notice that the keyword "column" is missing from this syntax. However, you can add in the 'cascade constraints' or the 'checkpoint X' clauses, as before.

In all these cases, data is physically removed from the table -and that's a relatively expensive operation. This is not the sort of command you'd want to let loose on a big table during the middle of a busy day -your Users will almost certainly notice the massive slow-down in database performance. Fortunately, there is a cheaper alternative: ALTER TABLE BLAH SET UNUSED COLUMN BLAHCOL;

This simply marks a column as being unusable in the data dictionary. Again, there is a multi-column variant: ALTER TABLE BLAH SET UNUSED

(BLAHCOL1,

BLAHCOL2);

...which sets 2 columns unused with one command (and again, confusingly, misses out the "column" keyword).

The only effect of the command is to make a small change to the data dictionary, and therefore, however enormous the table you are altering might be, the command completes in about a second. No full table scans are performed, no massive reading and writing from disk thus ensues, and the whole thing is over before you know it. Since the data is not physically removed, though, don't expect to free up any space by performing such an operation. That comes later!

Setting a column unused is also a piece of DDL -so it is just as irreversible as physically dropping it. And no, there is not a 'set used' command to reverse it, either. As soon as the command is issued, all subsequent selects on that table will not be able to read the data in that column (even though, physically, it's still there in all the rows). Nor will any User be able to insert values into that column. It is as good as non-existent.

Just as with actually dropping the column, therefore, you can't 'set unused' a column which is the parent in a foreign key relationship -unless you use the 'cascade constraints' variant: ALTER TABLE BLAH SET UNUSED COLUMN BLAHCOL CASCADE CONSTRAINTS;

Copyright Š Howard Rogers 2001

10/17/2001

Page 2 of 4

Dropping a Column from a Table

Administration Tips

Now the real neat trick comes with the following command: ALTER TABLE BLAH DROP UNUSED COLUMNS;

What this does is to now physically remove from the table all columns which have been previously marked as unused, in one pass of the table. It's just as expensive as a single drop column would be, because it's genuinely having to read all rows, remove the affected column data, and write it back to disk. But since it's removing all unused columns in one pass, it is no more expensive than a single column drop would be.

The trick to dropping multiple columns as cheaply as possible, therefore is to mark them all unused first (and performance won't be affected at all, so you can do that at any time of the day). Then, at some unearthly hour when no-one will be affected by a sudden surge in I/O activity, run a single 'drop unused columns' job.

Incidentally, just as when you dropped a single column you were able to induce additional checkpoints to minimise a subsequent Instance Recovery, so you can with the 'drop unused' command: ALTER TABLE BLAH DROP UNUSED COLUMNS CHECKPOINT

1000;

There is no 'cascade constraints' clause, though -since those were dropped when you set the column to be unused in the first place.

Versions of Oracle before 8i Since there was no 'drop column' command in 8.0 or 7.x, you have to use the CTAS (Create Table ... As Select...) command to achieve something similar: CREATE

(COL1, COL2, COL3) COL1,COL2,COL4 FROM OLDBLAH;

TABLE NEWBLAH

AS SELECT

That example creates a new 3-column table, grabbing the information out of the old table -but skipping the old table's third column. Notice that you don't define data types for the new table's columns (you'll get an error if you try) -the types are going to be determined by whatever data types the relevant columns in the original table happen to be.

Having achieved the extraction of the right columns, you'll probably want to perform the following three actions: DROP TABLE OLDBLAH;

Copyright Š Howard Rogers 2001

10/17/2001

Page 3 of 4

Dropping a Column from a Table

CREATE TABLE OLDBLAH AS SELECT

Administration Tips

*

FROM NEWBLAH;

DROP TABLE NEWBLAH

That gets us back to having what is effectively a brand new table, but with exactly the same name as the original.

So the end result can be achieved, but it's not exactly cheap: dropping the "oldblah" table after the first CTAS means Users can't access the data until the entire process (and the second CTAS to get the old name back again) has completed. And all the usual caveats about CTAS apply, too: no constraints, apart from NOT NULLS, come along for the ride, so they have to be re-defined. No indexes come along for the ride, so they need to be recreated from scratch. And no permissions go along for the ride, either, so they all have to be re-granted (even when, at the end of the entire process, the net effect is that the table name hasn't changed).

Copyright Š Howard Rogers 2001

10/17/2001

Page 4 of 4