Re: Why can we not delete columns in tables?

Thomas Kyte wrote:
>
> 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
>
>
> 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).

I understand now why, thank you. However not being able to delete a column during development before any production data is loaded can be a pain. If for example one is building a simple prototype as a proof of concept it would be nice to be able to have the ability to alter tables at will.

This inability to drop table columns does however tend to force oneself to the use of CASE tools because there you can do what ever you like to the table representations and at the end make the changes take effect. This is no bad thing. Furthermore, I suppose there is a danger that if you allow the removal of table columns then it will start to be used everywhere without a clear understanding of the efficiency costs.

Tim Eyres
(http://www.genedata.com) Received on Tue Sep 30 1997 - 00:00:00 CDT