set unused column是对列进行逻辑删除,而drop column是对列进行物理删除,它们的共同点是对列进行删除,使用set unused column删除列更快一些,逻辑删除好处就是可以当资源紧张的时候,节约处理时间,当以后空余的时间再真正DROP掉。
SQL>
NICK
-------------------- ----------
zhaolin1346
zhaolin1347
zhaolin1348
zhaolin1349
zhaolin1350
zhaolin1351
zhaolin1352
zhaolin1353
zhaolin1354
9 rows selected.
SQL> create index idx_test_id on test(id);
Index created.
SQL> select index_name,table_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------ ------------------------------
IDX_TEST_NICK
IDX_TEST_ID
SQL> ALTER TABLE TEST SET UNUSED COLUMN ID;
Table altered.
SQL>
INDEX_NAME
------------------------------ ------------------------------
IDX_TEST_NICK
SQL> desc test;
Name
----------------------------------------- -------- ----------------------------
NICK
SQL> alter table test drop unused columns;
Table altered.
SQL> desc test;
Name
----------------------------------------- -------- ----------------------------
NICK
另外,set unused column column_name包含此列的索引也会被drop掉
--下面的附加实验是验证在set unused column 之后,在下次插入新的记录时,会不会再为此column分配存储空间
SQL> select rowid,t.* from test t;
ROWID
------------------ -------------------- ----------
AAAB6sAAFAAAAAuAAA zhaolin
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) as file#,
2
---------- ----------
5
SQL> alter system dump datafile 5 block 46;
System altered.
--以下是dump文件的信息
data_block_dump,data header at 0x5374464
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x05374464
bdba: 0x0140002e
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8a
avsp=0x1f76
tosp=0x1f76
0xe:pti[0]
0x12:pri[0]
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x1
col
col
end_of_block_dump
End dump data blocks tsn: 7 file#: 5 minblk 46 maxblk 46
--在set unused column id之后,对于新插入的记录,id列会不会不用再分配存储空间了
SQL> alter table test set unused column id;
Table altered.
SQL> insert into test(nick) values('nature');
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) as file#,
2
---------- ----------
5
5
SQL> alter system dump datafile 5 block 46;
System altered.
SQL> select * from test;
NICK
--------------------
zhaolin
nature
--以下是dump文件的信息
data_block_dump,data header at 0x5374464
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x05374464
bdba: 0x0140002e
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f7f
avsp=0x1f69
tosp=0x1f69
0xe:pti[0]
0x12:pri[0]
0x14:pri[1]
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x1
col
col
tab 0, row 1, @0x1f7f
tl: 11 fb: --H-FL-- lb: 0x2
col
col
end_of_block_dump
End dump data blocks tsn: 7 file#: 5 minblk 46 maxblk 46
我们新增加列是定长型的列address char(10)
SQL> alter table test add(address char(10));
Table altered.
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) as file#,
2
---------- ----------
5
5
SQL>
System altered.
SQL> desc test;
Name
----------------------------------------- -------- ----------------------------
NICK
ADDRESS
SQL> insert into test values('cat','china hang');
1 row created.
SQL> commit;
Commit complete.
SQL>
System altered.
SQL>
2
---------- ----------
5
5
5
--dump文件如下
data_block_dump,data header at 0x5374464
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x05374464
bdba: 0x0140002e
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f62
avsp=0x1f48
tosp=0x1f48
0xe:pti[0]
0x12:pri[0]
0x14:pri[1]
0x16:pri[2]
0x18:pri[3]
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x0
col
col
tab 0, row 1, @0x1f7f
tl: 11 fb: --H-FL-- lb: 0x0
col
col
tab 0, row 2, @0x1f6c
tl: 19 fb: --H-FL-- lb: 0x1
col
col
col
tab 0, row 3, @0x1f62
tl: 10 fb: --H-FL-- lb: 0x2
col
col
col
end_of_block_dump
End dump data blocks tsn: 7 file#: 5 minblk 46 maxblk 46
所以在set unused column之后,不会再为新插入的记录分配存储空间,要是在set unused column之前新增加的那列是定长型,并且有默认值,还有NOT NULL约束,情况又是怎么样呢?
经过实验,也不会再分配存储空间了!