| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Why can we not delete columns in tables?
On Mon, 29 Sep 1997 17:17:10 +0200, Tim Eyres <tim.eyres_at_genedata.com> wrote:
>Does anybody know the justification for not being able to delete columns
>from tables when using Oracle? Is there a genuine relational database
>principle behind it or is it just an Oracle failing?
>
>Tim
Just a matter of efficiency. Adding a column is easy in Oracle. If a trailing column in a row is NULL, that column is not stored on a block at all. When you add a column to a table, it will always be NULL -- hence adding a column is a simple data dictionary modification and happens very fast. No blocks need to be rewritten.
Dropping a column on the other hand potentially requires rewriting every single block in the table. We need to go out and physically remove the column from the table. In effect, the entire table needs to be rebuilt. This is drastic and could take a long long time (locking the data dictionary while this is occurring). Since dropping a column is so drastic, and could potentially lock up a system for a long time, we don't do it. In order to drop a column, you must rebuild the table (create table as select, sql*plus copy command, unload to flat file and sqlldr the data in again, etc).
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |