[20171113]修改表结构删除列相关问题.txt
--//维护表结构删除字段一般都是先
ALTER TABLE <table_name> SET UNUSED (<column_name>);
--//然后等空闲时候删除列.
ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>;
--//参考文档:
https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11662
Removing Unused Columns
The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes
unused columns from the table and reclaims disk space.
In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint
to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of
undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;
--//从文档上可以看出加入CHECKPOINT关键字可以一定程度减少undo空间的消耗.当然也可以使用:
ALTER TABLE <table_name> DROP COLUMN column_name;
--//删除多列可以使用:
ALTER TABLE <table_name> DROP (<column_name1>,<column_name2>);
--//注:很奇怪这里有没有column关键字.简单的例子:
SCOTT@book> create table t (a number, b number, c number, d number);
Table created.
SCOTT@book> alter table t drop column b ;
Table altered.
SCOTT@book> alter table t drop ( c) ;
Table altered.
SCOTT@book> alter table t drop column ( d,e) ;
alter table t drop column ( d,e)
*
ERROR at line 1:
ORA-00904: : invalid identifier
SCOTT@book> alter table t drop column ( d) ;
alter table t drop column ( d)
*
ERROR at line 1:
ORA-00904: : invalid identifier
SCOTT@book> alter table t drop (d , e) ;
Table altered.
--//一般不建议直接删除列,这样很慢,但是有一种情况例外就是如果增加后数据类型错误,或者定义长度太小,选择drop column应该问题
--//不大,以为这个时候没有任何DML或者很少,马上修改回来应该还是很快的,当然做全表扫描是不可避免的.我想通过一个例子说明这样
--//操作的一些细节问题.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//drop table t purge;
create table t (id number,v1 varchar2(10),v2 varchar2(20));
SCOTT@book> insert into t select rownum,lpad('a',10,'a'),lpad('b',20,'b') from xmltable('1 to 10000000');
10000000 rows created.
SCOTT@book> commit ;
Commit complete.
2.增加一列然后删除看看产生redo的大小:
SCOTT@book> alter table t add ( flag number(1,0));
Table altered.
SCOTT@book> column name format a54
SCOTT@book> @ &r/viewredo
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 0
redo size 194 724
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
SCOTT@book> alter table t drop ( flag );
Table altered.
SCOTT@book> @ &r/viewredo
NAME STATISTIC# VALUE
------------------------------------------------------ ---------- ----------
user commits 6 1
redo size 194 4272
redo wastage 199 0
data blocks consistent reads - undo records applied 326 0
--//虽然需要2,3秒,但是产生的redo很小仅仅4K上下.
SCOTT@book> select SEGMENT_NAME,BYTES from dba_segments where owner=user and segment_name='T';
SEGMENT_NAME BYTES
-------------------- ----------
T 494927872
--//表大小472MB,主要花费在扫描数据块的时间.
3.如果先前执行了ALTER TABLE <table_name> SET UNUSED (<column_name>);,然后增加一列然后删除看看产生redo的大小:
SCOTT@book> alter table t SET UNUSED (v1);
Table altered.
SCOTT@book> alter table t add ( flag number(1,0));
Table altered.
SCOTT@book> SELECT col#, segcol#, name, intcol#, type# FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T' AND owner = user);
COL# SEGCOL# NAME INTCOL# TYPE#
---------- ---------- ------------------------------ ---------- ----------
1 1 ID 1 2
0 2 SYS_C00002_17111309:43:04$ 2 1
2 3 V2 3 1
3 4 FLAG 4 2
--// 可以发现字段v1变成了SYS_C00002_17111309:43:04$(后面一串是时间)
SCOTT@book> @ &r/viewredo
NAME STATISTIC# VALUE
----------------------------------------------------- ---------- ----------
user commits 6 0
redo size 194 724
redo wastage 199 0
data blocks consiste nt reads - undo reco rds applied 326 0
SCOTT@book> alter table t drop ( flag );
Table altered.
SCOTT@book> column name format a53
SCOTT@book> @ &r/viewredo
NAME STATISTIC# VALUE
----------------------------------------------------- ---------- ----------
user commits 6 1
redo size 194 2290947720
redo wastage 199 0
data blocks consistent reads - undo records applied 326 3
--//噢,忘记打开计时设置,不过从redo size=2290947720,也可以看出需要的时间不会太短.
--//2290947720/1024/1024/1024=2.1336113288998603828,差不多2G.
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2017-11-13 09:55:06
SCOTT@book> @ &r/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 Avg
------------------- ------ ---------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -------
2017-11-13 00:00:00 Mon 59 0 0 0 0 0 0 0 0 0 59 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2.46
2017-11-10 00:00:00 Fri 2 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 0 0 0 .08
2017-11-09 00:00:00 Thu 7 0 0 0 0 0 0 0 0 0 5 1 0 0 0 0 0 1 0 0 0 0 0 0 0 .29
2017-11-08 00:00:00 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 .04
--//从9点产生的redo的切换次数知道产生redo有多大(我测试环境一个redo 50m).
SCOTT@book> SELECT col#, segcol#, name, intcol#, type# FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T' AND owner = user);
COL# SEGCOL# NAME INTCOL# TYPE#
---------- ---------- ---- ------- ----------
1 1 ID 1 2
2 2 V2 2 1
--//从这里也可以看出,虽然执行 alter table t drop ( flag );,针对的是一个flag字段,实际上还顺便干了一件事情就是把就是把设置为SET UNUSED的列,一起给drop掉,这样就导致大量
--//大量redo 的产生,在一些维护操作前一定要谨慎.减少不必要的维护问题.