| 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, 06 Oct 1997 21:25:27 -0700, Phil Bradley <pbradley_at_pacific.net.sg> wrote:
>
>
>Martin Haltmayer wrote:
>
>> I can imagine a few good reasons:
>> 1. It is not allowed in ANSI-SQL.
>
>ANSI SQL specifically provides for this feature. I refer you to Date's 'Guide
>to the SQL Standard'
>
>> 2. If you drop a column, you have to invalidate all the checks,
>> constraints, indexes, triggers, views, procedures and functions that use
>> that column. That's a lot more work to do than just dropping these
>> objects when a table is dropped.
>
>A properly constructed schema would permit a 'where used' query. All dependant
>objects would have to deleted first. A 'cascade delete' could also be
>provided.
>
>> 3. As another Oraclist mentioned, it would be a very time consuming
>> procedure that may lock the data dictionary unintentionally for a very
>> long time. You may just estimate the amount of time doing an update on
>> that column to null.
>
>Don't know what 'unintentionally' means in this sentence, or for that matter a
>'very long time'.
>
>I asked Oracle why they do no provide this capability, which to me is integral
>to the whole SQL model. I initially got an answer along the lines of 'to
>ensure data integrity'. When I knocked down this argument, they dried up.
>
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).
>I don't know why they do not provide this facility. My suspicion it will break
>their applications, Forms, and probably their DBMS big time.
>
wouldn't affect application/forms. It affects the dbms in that dropping a column forces you to rewrite the entire table.
>Does any one know if they provided ALTER TABLE DROP COLUMN in previous
>versions.
>
No. Not in v6-8 anyway.
>> Phil B.
>> --
>> Martin Haltmayer
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |