如何drop大表的中不用的字段[转]
我们要删除表中不用的字段,如果直接drop column,对于大表,进行DDL操作的时间会比较长,会严重阻塞DML语句,导致应用服务器crash,通常的做法是先set unused column column_name 或者 set unused column_name,column_name...,然后再利用停机时间做alter table table_name drop unused columns
SQL> select * from test where rownum<10;
NICK ID
-------------------- ----------
zhaolin1346 3
zhaolin1347 3
zhaolin1348 3
zhaolin1349 3
zhaolin1350 3
zhaolin1351 3
zhaolin1352 3
zhaolin1353 3
zhaolin1354 3
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 TABLE_NAME
------------------------------ ------------------------------
IDX_TEST_NICK TEST
IDX_TEST_ID TEST
SQL> ALTER TABLE TEST SET UNUSED COLUMN ID;
Table altered.
SQL> select index_name,table_name from user_indexes where table_name='TEST';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
IDX_TEST_NICK TEST
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
NICK VARCHAR2(20)
SQL> alter table test drop unused columns;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
NICK VARCHAR2(20)
另外,set unused column column_name包含此列的索引也会被drop掉
--下面的附加实验是验证在set unused column 之后,在下次插入新的记录时,会不会再为此column分配存储空间
SQL> select rowid,t.* from test t;
ROWID NICK ID
------------------ -------------------- ----------
AAAB6sAAFAAAAAuAAA zhaolin 10
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) as file#,
2 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) as block# from test;
FILE# BLOCK#
---------- ----------
5 46
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] nrow=1 offs=0
0x12:pri[0] offs=0x1f8a
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 7] 7a 68 61 6f 6c 69 6e
col 1: [ 2] c1 0b
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 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) as block# from test;
FILE# BLOCK#
---------- ----------
5 46
5 46
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] nrow=2 offs=0
0x12:pri[0] offs=0x1f8a
0x14:pri[1] offs=0x1f7f
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 7] 7a 68 61 6f 6c 69 6e
col 1: [ 2] c1 0b --原来的空间并不会释放
tab 0, row 1, @0x1f7f
tl: 11 fb: --H-FL-- lb: 0x2 cc: 2
col 0: [ 6] 6e 61 74 75 72 65
col 1: [0] --从这里可以看出，不会再分配存储空间了,但是保留着列的标识符
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 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) as block# from test;
FILE# BLOCK#
---------- ----------
5 46
5 46
SQL> alter system dump datafile 5 block 46;
System altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
NICK VARCHAR2(20)
ADDRESS CHAR(10)
SQL> insert into test values('cat','china hang');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system dump datafile 5 block 46;
System altered.
SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) as file#,
2 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) as block# from test;
FILE# BLOCK#
---------- ----------
5 46
5 46
5 46
--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] nrow=4 offs=0
0x12:pri[0] offs=0x1f8a
0x14:pri[1] offs=0x1f7f
0x16:pri[2] offs=0x1f6c
0x18:pri[3] offs=0x1f62
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 7] 7a 68 61 6f 6c 69 6e
col 1: [ 2] c1 0b
tab 0, row 1, @0x1f7f
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 6] 6e 61 74 75 72 65
col 1: [0]
tab 0, row 2, @0x1f6c
tl: 19 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 3] 63 61 74
col 1: [0]
col 2: [10] 63 68 69 6e 61 20 68 61 6e 67
tab 0, row 3, @0x1f62
tl: 10 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 4] 6c 61 73 74
col 1: [0]
col 2: [0] --从这里可以看出，不会再分配存储空间了
end_of_block_dump
End dump data blocks tsn: 7 file#: 5 minblk 46 maxblk 46
所以在set unused column之后,不会再为新插入的记录分配存储空间,要是在set unused column之前新增加的那列是定长型,并且有默认值,还有NOT NULL约束,情况又是怎么样呢?
经过实验,也不会再分配存储空间了!
