删除column需要注意的一个问题

删除表的一个或多个列有两种方式：

1.直接删除

ALTER TABLE <table_name> DROP COLUMN column_name;
--//删除多列可以使用:
ALTER TABLE <table_name> DROP (<column_name1>,<column_name2>);
--注意，删除多个列的时候，多个列加上括号，并不需要column关键字

2.考虑到负载的问题，先将列设为UNUSED，等负载空闲的时候再删除

ALTER TABLE <table_name> SET UNUSED (<column_name>);

--//然后等空闲时候删除列.
ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>;

--CHECKPOINT关键字可以一定程度减少undo空间的消耗,后面接删除记录的条数，指定多少笔数据删除后强制一次checkpoint。

1.用第1种方法做一个测试：

SQL> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> create table t (id number,v1 varchar2(10),v2 varchar2(20));

Table created.

SQL>  insert into t select rownum,lpad('a',10,'a'),lpad('b',20,'b') from dual connect by rownum<10000000;

9999999 rows created.

SQL> commit;

Commit complete.

SQL> set timing on;
SQL> alter table t drop ( v2);

Table altered.

Elapsed: 00:07:08.30
SQL>  @viewredo

NAME                                VALUE
------------------------------ ----------
user commits                            1
redo size                      2376261196
redo wastage                            0
data blocks consistent reads -          1
 undo records applied

删除这一列用了7分钟，生成了2376M的redo数据，太可怕了。

2.接着测试第2种方法：

重建表T

SQL> drop table t purge;

Table dropped.

SQL> create table t (id number,v1 varchar2(10),v2 varchar2(20));

Table created.

SQL> insert into t select rownum,lpad('a',10,'a'),lpad('b',20,'b') from dual connect by rownum<10000000;

9999999 rows created.

SQL> commit;

Commit complete.

先将columns设置为unused，产生的redo非常少。

SQL> alter table t  SET UNUSED (v2);

Table altered.

SQL> @viewredo

NAME                                VALUE
------------------------------ ----------
user commits                            1
redo size                            3768
redo wastage                            0
data blocks consistent reads -          1
 undo records applied

真正删除的时候才会产生大量的redo，这与预期相符。

SQL> alter table t drop unused columns;

Table altered.

SQL>  @viewredo

NAME                                VALUE
------------------------------ ----------
user commits                            2
redo size                      2379126024
redo wastage                            0
data blocks consistent reads -          2
 undo records applied

3.重点来了，这两种方法一起使用时需要注意的一个问题

先重建表T后：

将v2设置为unused待空闲时再真正删除，接着新建一个column flag,不过发现flag建立错了，需要删除。由于考虑到flag是新建的表删除这个flag不会产生多少redo，所以就直接删除了。

SQL> alter table t  SET UNUSED (v2);

Table altered.

SQL> alter table t add ( flag number(1,0));

Table altered.

SQL>  @viewredo

NAME                                VALUE
------------------------------ ----------
user commits                            1
redo size                            6920
redo wastage                            0
data blocks consistent reads -          2
 undo records applied


SQL> alter table t drop (flag);

不过发现drop flag这个column时等的时候太久了，感觉情况不对。

等删除结束后，发现产生了大量的redo.

SQL> @viewredo

NAME                                VALUE
------------------------------ ----------
user commits                            2
redo size                      2379294812
redo wastage                            0
data blocks consistent reads -          3
 undo records applied

原来在drop flag的时候，我以为只是针对flag做删除，不会影响其它的column，数据库在drop flag时将unused的column一起给删除了。这就导致产生了大量的redo，这种操作以后要注意一下。

SQL> SELECT col#, segcol#, name, intcol#, type# FROM sys.col$ WHERE obj# IN (SELECT object_id FROM
  2  dba_objects WHERE object_name = 'T' AND owner = user);

      COL#    SEGCOL# NAME                              INTCOL#      TYPE#
---------- ---------- ------------------------------ ---------- ----------
         1          1 ID                                      1          2
         2          2 V1                                      2          1

从redo的切换频率也可以看出产生了大量的redo。

SQL>  @log_history

Date      Day Total   h0   h1   h2   h3   h4   h5   h6   h7   h8   h9  h10  h11  h12  h13  h14  h15  h16  h17  h18  h19  h20  h21  h22  h23
--------- --- ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
09-APR-20 Thu    17    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    6   10    0    0    0    0    0    0    0
08-APR-20 Wed     1    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
01-APR-20 Wed     1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0
26-MAR-20 Thu    18    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    6    8    4    0    0    0    0    0    0
23-MAR-20 Mon     1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0
24-FEB-20 Mon     1    0    0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0
18-FEB-20 Tue     1    0    0    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0    0    0    0
12-FEB-20 Wed     1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0
06-FEB-20 Thu     1    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    0    1    0
31-JAN-20 Fri     1    0    0    0    0    0    0    0    0    0    0    1    0    0    0    0    0    0    0    0    0    0    0    0    0

10 rows selected.