Oracle

February 23, 2011

Caution while dropping unused columns with checkpoint

Filed under: Uncategorized — srivenu @ 9:19 am

Some of the optional clauses in SQL statements sound so naive and misleading while actually hiding the immense risk involved in using them. I think oracle should use appropriate terminology for such clauses.

One such clause is the “CHECKPOINT” in the DDL “ALTER TABLE …. DROP UNUSED COLUMNS CHECKPOINT”.
May be it would have been more appropriate if it was “ALTER TABLE …. DROP UNUSED COLUMNS CHECKPOINT YOU ARE STUCK HERE TILL COMPLETION DUMBO” !

Case in point –
In one of the databases, we have a 6TB partitioned table into 100GB is loaded every day. A couple of months ago, one of the columns in the table was set to UNUSED as the developers wanted it to be dropped. During one of the weekend downtimes, one of the DBA’s wanted to drop the unused column. Being unsure of the time it would take and to avoid any UNDO issue, he decided to use the CHECKPOINT clause (to be fair, I might have too).

He cancelled the session immediately, but trouble manifested soon after. Any sort of access to the table starting throwing the error “table in not usable state”. We could not parallelize the column drop session. We could not afford to proceed in one serial session as it would have taken 18 days to complete!

Quick search on the net revealed this note. We could not implement the workaround suggested in the node immediately as it involved dictionary modification and had to suffer several hours of critical functionality downtime working with oracle support.

I’m not saying that the clause is useless. All i’m saying is that the term appears very innocent for the risk involved and doesnt caution its usage and also the risk associated is not properly documented.

This is from the 11g Release 2 Database Administrators Guide – Not a peep in it

Removing Unused Columns
The ALTER TABLE…DROP UNUSED COLUMNS statement is the only action allowed
on unused columns. It physically removes unused columns from the table and
reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is
specified. This clause causes a checkpoint to be applied after processing the specified
number of rows, in this case 250. Checkpointing cuts down on the amount of undo
logs accumulated during the drop column operation to avoid a potential exhaustion of
undo space.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

Ofcourse the 11g Release 2 SQL Language Reference Guide mentions it

DROP UNUSED COLUMNS Clause

CHECKPOINT Specify CHECKPOINT if you want Oracle Database to apply a
checkpoint for the DROP COLUMN operation after processing integer rows; integer
is optional and must be greater than zero. If integer is greater than the number of
rows in the table, then the database applies a checkpoint after all the rows have been
processed. If you do not specify integer, then the database sets the default of 512.
Checkpointing cuts down the amount of undo logs accumulated during the DROP
COLUMN operation to avoid running out of undo space. However, if this statement is
interrupted after a checkpoint has been applied, then the table remains in an unusable
state. While the table is unusable, the only operations allowed on it are DROP TABLE,
TRUNCATE TABLE, and ALTER TABLE DROP … COLUMNS CONTINUE (described in
sections that follow).
You cannot use this clause with SET UNUSED, because that clause does not remove
column data.

I feel that this caution should be mentioned in the Administrators Guide and that too in bold.
I would raise a Documentation bug

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: