| 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 dean.reynolds_at_home.net
(if that email address didn't require changing)
On Tue, 27 Apr 1999 00:41:39 GMT, you wrote:
>On Mon, 26 Apr 1999 12:32:01 GMT, tkyte_at_us.oracle.com (Thomas Kyte)
>wrote:
>>
>>
>>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.
>>
>
>
>To reclaim the space, doesn't something similar to the following
>happen (I'm on 7 so I'm just guessing):
>
>rename TABLE to TMP_TABLE
>
>drop or disable constraints
>
>create table TABLE as (select all undropped columns)
>
>drop TMP_TABLE
>
>enable or rebuild constraints
>
Yes, that is similar but would change rowids and rebuild the table and do lots more work then needed. The drop column will reclaim the space on a block but not move the data about into different blocks. So, the drop column will decrease the avg_row_len and increase the avg_space (free space) but will not move the rows (necessitating a rebuild of indexes, constraints, etc). Consider this example:
REM create a table of data, large enough to work with... SQL> create table t1 as select * from all_objects; Table created.
REM remember the rowids of the rows in this table, we'll
REM make sure they don't change
SQL> create table t2 as select rowid r from t1;
Table created.
SQL> create unique index t2_idx on t2(r);
Index created.
REM lets see how big the table is right now: SQL> analyze table t1 compute statistics; Table analyzed.
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len 2 from user_tables where table_name = 'T1' 3 /
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ------------ ---------- ---------- -----------
12083 161 30 874 0 94
REM we'll free up lots of space by unusing many of the columns
SQL> alter table t1 set unused column object_name; SQL> alter table t1 set unused column subobject_name; SQL> alter table t1 set unused column object_id; SQL> alter table t1 set unused column data_object_id; SQL> alter table t1 set unused column object_type; SQL> alter table t1 set unused column created; SQL> alter table t1 set unused column last_ddl_time;
REM we can see from this that the columns are just flag deleted as the stats REM do not change at all for the table:
SQL> analyze table t1 compute statistics; Table analyzed.
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len 2 from user_tables where table_name = 'T1' 3 /
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ------------ ---------- ---------- -----------
12083 161 30 874 0 94
REM Now, lets get the space back:
SQL> alter table t1 drop unused columns; Table altered.
SQL> analyze table t1 compute statistics; Table analyzed.
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len 2 from user_tables where table_name = 'T1' 3 /
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN ---------- ---------- ------------ ---------- ---------- -----------
12083 161 30 4918 0 40
REM the above shows that the avg_row_len was cut way down, from 94 to 40 REM and the avg_space on a block went from 874 bytes to 4918 bytes. We didn't REM compress the table -- we just cleaned up the blocks, removing the columns REM from them. Also note the chain count is still 0, no migrated rows
REM Now, lets make sure our rowids didn't change...
SQL> select count(*) from t1
2 where not exists ( select null from t2 where t2.r = t1.rowid )
3 /
COUNT(*)
0
>
>I like the ANSI compatability (read ease of use), I just like to know
>what it's really doing.
>
>So, you could mark a column to be dropped during normal business hours
>and end it's use, but wait to actually rebuild the table until a low
>usuagecycle, is that right?
>
correct. actually dropping the column takes a while as every block is re-written (drop column can in fact be interrupted and restarted) and you might choose to never reclaim the space if you so desired.
>-Dean
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |