Re: Why can we not delete columns in tables?

Thomas Kyte wrote:
> I do not speak for Oracle, I speak for myself but....
>
> it is 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).

One good reason for the feature would be that in the initial design you have a few columns that you think you are going to use, or will possibly use in future expansion. You may later find out that those fields are no longer useful, or that the fields will never be used for future expansion. These extra columns never had data inserted into them. They are all NULL anyway. It would be nice to be able just drop them if they have no data in them.

I can see though that for very large systems, this could be a dangerous maneuver. I have mostly dealt with small scale models so far, so being able to do this would not present a hazard to our projects.

---Mark
mmiller_at_nyx.net Received on Mon Oct 06 1997 - 00:00:00 CDT