如何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约束,情况又是怎么样呢?

经过实验,也不会再分配存储空间了!

转载于:https://www.cnblogs.com/rootq/archive/2008/09/11/1289455.html