Large Table如何快速的drop column

开发经常有时候会有一些需求,就是删除一张大表的某个列。这个事情如果是在高峰期来做的话,会引起很大的堵塞。因为Oracle是按照行来存储的,将一行一行的数据存储在数据块当中。当我们删除列,我们就需要遍历所有数据块,然后把这个数据块上的列的值给清除掉。所以在业务高峰期,我们很少做这样的事情。但是需求总是有的。业务人员才不管你那么多,他现在要删你就得给他删掉。所以Oracle推出了逻辑删除列的方式。我们来看看以下几种方式。
1.物理删除
ALTER TABLE <table_name> DROP COLUMN <col_name>
这种删除方式将会把列从表中删除,就像我们前面描述的一样,它会逐个块去扫描,将相应的数据删除。并产生大量的redo和undo数据。一般情况下,我们可以对小表进行这类的操作,对于大表,这个扫描,删除的时间可能非常的长,产生redo和undo的数据会非常大。

2.逻辑删除
ALTER TABLE <table_name> SET UNUSED COLUMN <col_name>
这种只是从数据字典中将该列删除,空间不会回收,数据也不能重新写进去。它不会产生redo和undo。如果你想让数据块重新可写。就需要运行下列命令。
ALTER TABLE <table_name> DROP UNUSED COLUMNS;
这条命令和物理删除一样,期间会产生很大的redo还有undo。而且这里删除列的值是无法回滚的。
ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <number>;
因为在删除列的过程中,可能需要占用很多的undo空间,所以处理完一部分之后,就执行一次checkpoint释放Undo空间。

 SQL> select * from v$sysstat where name='redo size';

STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
       178 redo size                                                                 2 9704227768 1236385760

SQL> alter table test SET UNUSED COLUMN object_name;

Table altered.

SQL> select * from v$sysstat where name='redo size';

STATISTIC# NAME                                                                  CLASS      VALUE    STAT_ID
---------- ---------------------------------------------------------------- ---------- ---------- ----------
       178 redo size                                                                 2 9705985588 1236385760

SQL> alter table test drop UNUSED COLUMNS Checkpoint 300;

Table altered.

SQL> select * from v$sysstat where name='redo size';

STATISTIC# NAME                                                                  CLASS               VALUE    STAT_ID
---------- ---------------------------------------------------------------- ---------- ------------------- ----------
       178 redo size                                                                 2         12038548300 1236385760

下面是我删除的时候对undo的监控,我们可以看到设置checkpoint之后,Oracle Active的undo信息所占空间非常少,这就是设置checkpoint的好处,达到设置的阈值后,oracle会立马把active的转换成为UNEXPIRED。

 SQL> SELECT seg.tablespace_name "Tablespace Name", ts.bytes/1024/1024 "TS Size(MB)",
  2    ue.status "UNDO Status", count(*) "Used Extents",
  3    round(sum(ue.bytes)/1024/1024, 2) "Used Size(MB)",
  4    round(sum(ue.bytes)/ts.bytes*100, 2) "Used Rate(%)"
  5  FROM dba_segments seg, DBA_UNDO_EXTENTS ue,
  6    (SELECT tablespace_name, sum(bytes) bytes
  7      FROM dba_data_files GROUP BY tablespace_name) ts
  8  WHERE ue.segment_NAME=seg.segment_NAME and seg.tablespace_name=ts.tablespace_name
  9  GROUP BY seg.tablespace_name, ts.bytes, ue.status
 10  ORDER BY seg.tablespace_name;

Tablespace Name                TS Size(MB) UNDO Stat Used Extents Used Size(MB) Used Rate(%)
------------------------------ ----------- --------- ------------ ------------- ------------
UNDOTBS1                              1985 ACTIVE               1           .06            0
UNDOTBS1                              1985 EXPIRED              5          3.13          .16
UNDOTBS1                              1985 UNEXPIRED          291       1011.19        50.94

SQL> SELECT seg.tablespace_name "Tablespace Name", ts.bytes/1024/1024 "TS Size(MB)",
  2    ue.status "UNDO Status", count(*) "Used Extents",
  3    round(sum(ue.bytes)/1024/1024, 2) "Used Size(MB)",
  4    round(sum(ue.bytes)/ts.bytes*100, 2) "Used Rate(%)"
  5  FROM dba_segments seg, DBA_UNDO_EXTENTS ue,
  6    (SELECT tablespace_name, sum(bytes) bytes
  7      FROM dba_data_files GROUP BY tablespace_name) ts
  8  WHERE ue.segment_NAME=seg.segment_NAME and seg.tablespace_name=ts.tablespace_name
  9  GROUP BY seg.tablespace_name, ts.bytes, ue.status
 10  ORDER BY seg.tablespace_name;

Tablespace Name                TS Size(MB) UNDO Stat Used Extents Used Size(MB) Used Rate(%)
------------------------------ ----------- --------- ------------ ------------- ------------
UNDOTBS1                              1985 EXPIRED              3          2.06           .1
UNDOTBS1                              1985 UNEXPIRED          307       1018.75        51.32

所以我们针对一张大表删除一个列,我们可以先逻辑的将列的定义从数据字典当中删除,然后再找一个业务闲的时候慢慢的释放空间。这里最好使用checkpoint,能帮助我们有效的控制一下undo。这个功能往往很关键。

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *