Drop Columns Without Performing a Data Copy

The no datacopy parameter to the alter table drop column allows you to drop columns from a table without performing a data copy, and reduces the amount of time required for alter table drop column to run.

The syntax is:

alter table [[database.][owner].table_name
	{add column_name datatype}
. . . }

	modify column_name 
	drop {column_name [, column_name]... 
		with exp_row_size=num_bytes
			| transfer table [on | off]} 
			| no datacopy

Instead of immediately removing the columns from the table, no datacopy updates the system tables, indicating the affected rows will be reformatted the next time you run reog rebuild or another datacopy operation (the space allocated for dropped columns (including large objects) is not freed until the next time you run reorg rebuild).

This example drops the total_sales column from the titles table without a data copy:

alter table titles
drop total_sales
with no datacopy