Oracle 8i - DROP COLUMN Facility

PURPOSE
=======
 
This document describes the new functionality in Oracle8i which allows a 
column to be dropped from either a table or an Index Organised Table (IOT).
 

RELATED DOCUMENTS
=================

 Note:72071.1 PL/SQL Example: How to Drop Column from Table
 Note:104417.1 Is the DROP COLUMN's freed space coalesced?

 Oracle Server SQL Reference Guide, Chapter 7 Alter Table
 Oracle Server Concepts Guide, Chapter 10 How Table Data Is Stored
 Oracle8i New Features, Chapter 2 Database Features / Drop Column 


DROP COLUMN Clause
==================

The DROP COLUMN facility is new functionality in Oracle 8i that allows a column
to be dropped from a table (including Index Organised Tables or IOT).

Columns can either be dropped immediately, or flagged as UNUSED and physically
dropped later when it is more convenient to perform. the processing. Dropping
a column visits all allocated blocks of the table to erase column values
and reclaim space whereas setting column flag to UNUSED is just an update
in a dictionary table.

Once flagged as UNUSED a column will not be accessible nor visible in the data
dictionary views or seen in the output of DESCRIBE.

Once a column is dropped or the UNUSED flag is set, it is not possible to
rollback the operation.

If a DROP COLUMN operation is interrupted, the column is left in a
PARTIALLY DROPPED state.

Data dictionary views are provided to allow interrogation of tables which have 
columns in the UNUSED or PARTIALLY DROPPED state. 

A column which has been set to UNUSED is actually renamed to a system
generated name in col$ and flags set in the col$.property column to indicate the
column's new status.

If a column is dropped, then all other columns in the table marked as 'UNUSED'
will also be dropped at the same time.

Examples
========

Here are some examples of DROP COLUMN:

o ALTER TABLE EMP DROP COLUMN ENAME;

This will remove the ENAME column from the EMP table. 

o ALTER TABLE EMP DROP (ENAME, SAL);

This will drop both the ENAME and SAL columns from EMP.

o ALTER TABLE EMP DROP COLUMN ENAME CHECKPOINT 100;
 
Again this removes the ENAME column the 'CHECKPOINT 100' clause forces 
a checkpoint to be performed after 100 rows have been deleted. The intention
is that less rollback space is required to perform. the overall operation.

The default value for CHECKPOINT is 512.

If a DROP COLUMN with the CHECKPOINT option is interrupted, the table is left
in a partially dropped state. At this stage only the following commands can be
used against the table:

- DROP TABLE
- TRUNCATE TABLE
- ALTER TABLE DROP COLUMNS CONTINUE

o ALTER TABLE EMP SET UNUSED COLUMN ENAME;

This marks the ENAME column as unusable. A describe of EMP will not show this
column, nor will USER_TAB_COLUMNS. The rows will be physically deleted by the  
command:

o ALTER TABLE EMP DROP UNUSED COLUMNS;

This command will also drop any other columns marked as 'UNUSED'.

Data Dictionary Views
=====================
The following data dictionary views are used to check which tables have
columns in the UNUSED state:

- USER/ALL/DBA_UNUSED_COL_TABS

These views tell you how many columns in each table have been set to UNUSED but
not which columns.

The following data dictionary views show tables having columns in the 
PARTIALLY DROPPED state:

- USER/ALL/DBA_PARTIAL_DROP_TABS



Things to be aware of:
======================

Dropping a column also drops all indexes and constraints on the target columns 
without any warning.
 
There is no such thing as 'undropping' so make sure you have taken the usual
backup precautions before issuing the command.

The DROP COLUMN operation is not (yet) performed in parallel. 

Columns can be dropped from IOTs as long as the columns are not part of the
primary key.

You cannot drop columns from object tables.

You should re-analyze the table after dropping a column.