Unused column and drop column operation essence in Oracle

The operations of the unused column and drop column are essentially different, and the unused makes the SQL inaccessible to column by changing the data dictionary's information, and the drop is changed directly at the physical data layer. The operation background tracking here can be validated with event 10046, which is not described here.

The following is an experimental way to verify the operation comparison of unused and drop column:

1. Create a test table

Sql> Grant DBA to Luda;

Grant succeeded.

Sql> Conn Luda/luda
Connected.

Sql> CREATE TABLE Luda_t1 as SELECT * from Dba_objects;

Table created.

Sql> Set Timing on
Sql> set Serverout on
sql> exec showspace (' luda_t1 ', ' Luda ')
Total Blocks ................... 768
Total Bytes ... ................... 6291456
Unused Blocks ......... .......... ... 53
Unused Bytes .......... ......... .... 434176
Last Used Ext Fileid ............ 4
Last Used Ext blockid ............ 2953
Last Used blocks ................ ... 75
*************************************************
The segment is analyzed
0%--25% free space blocks ...... 0
0%--25% free space bytes ....... 0
25%--50% free space blocks ...... 0
25%--50% free space bytes ...... 0
50%--75% free space blocks ...... 0
50%--75% free space bytes ...... 0
75%--100% free space blocks ..... 0
75%--100% free space bytes ...... 0
Unused Blocks ......... .......... ... 0
Unused Bytes .......... ......... .... 0
Total Blocks ................... 695
Total bytes ... ................... 5693440

Pl/sql procedure successfully completed.

elapsed:00:00:00.00
Sql> Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# and b.name= ' redo size ';

name                                                                     VALUE
----------------------------- ---------------------------------------------
Redo size                                                                80916

elapsed:00:00:00.01
Sql> Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# and b.name= ' Undo change vector Size ';

NAME VALUE
---------------------------------------------------------------- ----------
Undo Change Vector Size 21012

elapsed:00:00:00.00

2. Set object_name as unused

Sql> ALTER TABLE LUDA_T1 set unused column object_name;

Table altered.

elapsed:00:00:00.02
sql> exec showspace (' luda_t1 ', ' luda ')----contrast operation, you can find that the LUDA_T1 table storage information has not changed
Total Blocks ................... 768
Total Bytes ... ................... 6291456
Unused Blocks ......... .......... ... 53
Unused Bytes .......... ......... .... 434176
Last Used Ext Fileid ............ 4
Last Used Ext blockid ............ 2953
Last Used blocks ................ ... 75
*************************************************
The segment is analyzed
0%--25% free space blocks ...... 0
0%--25% free space bytes ....... 0
25%--50% free space blocks ...... 0
25%--50% free space bytes ...... 0
50%--75% free space blocks ...... 0
50%--75% free space bytes ...... 0
75%--100% free space blocks ..... 0
75%--100% free space bytes ...... 0
Unused Blocks ......... .......... ... 0
Unused Bytes .......... ......... .... 0
Total Blocks ................... 695
Total bytes ... ................... 5693440

Pl/sql procedure successfully completed.

elapsed:00:00:00.01
sql> Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# D b.name= ' redo size ';

NAME VALUE
---------------------------------------------------------------- ----------
Redo Size 92176

elapsed:00:00:00.00

---produces a small amount of redo logs

Sql> Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# and b.name= ' Undo change vector Size ';

NAME VALUE
---------------------------------------------------------------- ----------
Undo Change Vector Size 25212

elapsed:00:00:00.00

--produced a small amount of undo

3. Execute Drop unused column

sql> ALTER TABLE LUDA_T1 drop unused column;

Table altered.

elapsed:00:00:00.26
sql> exec showspace (' luda_t1 ', ' Luda ')--the comparison can be found in the FreeSpace level 25%-50% more than 642 block
Total Blocks ................... 768
Total Bytes ... ................... 6291456
Unused Blocks ......... .......... ... 53
Unused Bytes .......... ......... .... 434176
Last Used Ext Fileid ............ 4
Last Used Ext blockid ............ 2953
Last Used blocks ................ ... 75
*************************************************
The segment is analyzed
0%--25% free space blocks ...... 0
0%--25% free space bytes ....... 0
25%--50% free space blocks ...... 642
25%--50% free space bytes ...... 5259264
50%--75% free space blocks ...... 0
50%--75% free space bytes ...... 0
75%--100% free space blocks ..... 0
75%--100% free space bytes ...... 0
Unused Blocks ......... .......... ... 0
Unused Bytes .......... ......... .... 0
Total Blocks ................... 53--Total occupancy block reduced to 53, total number of blocks unchanged 642+53=695
Total bytes ... ................... 434176

Pl/sql procedure successfully completed.

elapsed:00:00:00.00
Sql> Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# and b.name= ' redo size ';

name                                                                     VALUE
----------------------------- ---------------------------------------------
Redo size                                                             12393932

--produces a large number of redo logs relative to the last operation Redo
elapsed:00:00:00.01
sql> Select B.name,a.value from V$mystat a,v$statname b where A.statistic#=b.statistic# and b.name= ' undo change vector size ';

NAME VALUE
---------------------------------------------------------------- ----------
Undo Change Vector Size 5128064
--Produces a large amount of undo information relative to the undo amount of the last operation
elapsed:00:00:00.00
Sql>

4. Perform test for drop column

sql> ALTER TABLE luda_t1 drop column object_type;

Table altered.

elapsed:00:00:00.25
sql> exec showspace (' luda_t1 ', ' Luda ')--the drop operation effect is consistent with the drop unused, releasing space and lowering the high water level
Total Blocks ................... 768
Total Bytes ... ................... 6291456
Unused Blocks ......... .......... ... 53
Unused Bytes .......... ......... .... 434176
Last Used Ext Fileid ............ 4
Last Used Ext blockid ............ 2953
Last Used blocks ................ ... 75
*************************************************
The segment is analyzed
0%--25% free space blocks ...... 0
0%--25% free space bytes ....... 0
25%--50% free space blocks ...... 664
25%--50% free space bytes ...... 5439488
50%--75% free space blocks ...... 1
50%--75% free space bytes ...... 8192
75%--100% free space blocks ..... 0
75%--100% free space bytes ...... 0
Unused Blocks ......... .......... ... 0
Unused Bytes .......... ......... .... 0
Total Blocks ................... 30
Total bytes ... ................... 245760

Pl/sql procedure successfully completed.

elapsed:00:00:00.01
Sql>
Sql> Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# and b.name= ' redo size ';

NAME VALUE
---------------------------------------------------------------- ----------
Redo Size 23902388
--produces a large number of redo logs relative to the previous operation's redo volume
elapsed:00:00:00.01
Sql> Select B.name,a.value from V$mystat a,v$statname b where a.statistic#=b.statistic# and b.name= ' Undo change vector Size ';
--Produces a large amount of undo information relative to the undo amount of the last operation
NAME VALUE
---------------------------------------------------------------- ----------
Undo Change Vector Size 9439452

elapsed:00:00:00.00
Sql>

Here are a few things to test:

The 1.unused column produces only a small amount of redo and undo, the real part of the table is not changed, and the high watermark is unchanged. The real data part is not handled by Oracle, and the unused column is changed at the data dictionary level according to trace information. The flag for the field being unused action.

The 2.drop unused column operation rewrite the data marked as unused flag (trace can be found) and frees up space to reduce the high water level while generating a large number of redo and undo.

The 3.drop column deletes the entire field's physical data portion directly (bbed can be found) and updates the table entries. Simultaneously lowering the high water level produces a lot of redo and undo. The equivalent of a data reorganization.