| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Column Drop
A copy of this was sent to Andrew Babb <andrewb_at_mail.com>
(if that email address didn't require changing)
On Sun, 25 Apr 1999 10:37:49 +0800, you wrote:
>Correction to my previous mail message.
>
>The space freed by a DROP COLUMN command can be reused by Oracle at a later
>date. My first test was not extensive enough.
>
>Sorry for any misleading,
>Andrew
there are three relevant options:
SET UNUSED
flag deletes a column. the column is logically deleted but space is not
reclaimed.
DROP
removes the column descriptor and
the data associated with the target column from each row in the table. ^^^^^^^^^^^^^^^^^^^^^^
When the column data is dropped, all indexes defined on any of the target columns are also dropped. All constraints that reference any target column are removed (even if they also reference a nontarget column). In addition, any association of those columns with a statistics type and any user-defined statistics collected using the statistics type are also removed. (For more information on statistics type associations, see "ASSOCIATE STATISTICS" on page 4-207.)
DROP UNUSED COLUMNS
removes from the table all columns currently marked as unused. Use
this command when you want to reclaim the extra disk space from
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
unused columns in the table. If the table contains no unused columns,
the statement returns with no errors.
So, setting unused does not reclaim space and happens very quickly. dropping the column does (but will therefore take much longer).
>
>gt_at_uno.com wrote:
>
>> This whole drop column functionality is misleading (especially
>> Microsoft's implementation in SQL Server 7).
>>
>> I still think the temp-table-shuffle is best ( except, of course, a
>> better design in the first place).
>>
>> -Dean
>>
>> On Wed, 21 Apr 1999 09:47:14 +0800, Andrew Babb <andrewb_at_mail.com>
>> wrote:
>>
>> >RC,
>> >
>> >You are not making sense but, with effect from Oracle8i (v8.1.5) you can
>> >logically drop the column from the table, but the space is not freed up
>> >for any existing data.
>> >
>> >Prior to Oracle8i, there was no way of doing this except for creating the
>> >table without the required column and then populating it using INSERT
>> >statements.
>> >
>> >Andrew
>> >
>> >RC wrote:
>> >
>> >> Can you drop a column from a table in Oracle 8 ? I know you can do it
>> >> in Oracle 8
>> >
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |