oracle中unused column 和 drop column操作实质

unused column和drop column的操作从本质上讲是不一样的，unused是通过变更数据字典的信息让sql无法访问到column，而drop是直接在物理数据层做了变动。这里的操作后台跟踪可以用event 10046去验证,这里不做描述.

下面通过实验的方式验证unused和drop column的操作对比情况:

1.创建测试表

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 Block.........................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.设置object_name为unused

SQL> alter table luda_t1 set unused column object_name;

Table altered.

Elapsed: 00:00:00.02
SQL> exec showspace('LUDA_T1','LUDA')  ---- 对比操作前,可以发现luda_t1表存储信息未有变动
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................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# and b.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             92176

Elapsed: 00:00:00.00

---产生了少量redo日志

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

--产生了少量undo

3.执行drop unused column

SQL> alter table luda_t1 drop unused column;

Table altered.

Elapsed: 00:00:00.26
SQL> exec showspace('LUDA_T1','LUDA') --对比操作前可以发现在freespace层面25%-50%多出了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 Block.........................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  --总占用block降低为53个,总块数不变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

--产生大量的redo日志相对比上一次操作的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
--产生大量的undo信息相对比上一次操作的undo量
Elapsed: 00:00:00.00
SQL>

4.执行drop column的测试

SQL> alter table luda_t1 drop column object_type;

Table altered.

Elapsed: 00:00:00.25
SQL> exec showspace('LUDA_T1','LUDA') -- drop 操作效果与drop unused一致,释放空间,降低高水位
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................53
Unused Bytes............................434176
Last Used Ext FileId....................4
Last Used Ext BlockId...................2953
Last Used Block.........................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
--产生大量的redo日志相对比上一次操作的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';
--产生大量的undo信息相对比上一次操作的undo量
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                             9439452

Elapsed: 00:00:00.00
SQL>

这里验几个情况:

1.unused column只产生少量的redo和undo,真实在表存储部分并未做变动,高水位线没有变动.真实数据部分并未被oracle处理,而根据trace信息可以发现unused column是在数据字典层面做的变动,对被unused操作的字段打上对于的flag.

2.drop unused column 操作会对被标记为unused flag的数据进行rewrite(trace可以发现)并释放空间,降低高水位,同时产生大量的redo和undo.

3.drop column操作会对整个字段物理数据部分直接进行删除(bbed可以发现),并更新table entries.同时降低高水位产生大量的redo和undo.相当于一次数据重组.