[20171113]修改表結構刪除列相關問題.txt--//維護表結構刪除欄位一般都是先ALTER TABLE <table_name> SET UNUSED (<column_name>);--//然後等空閑時候刪除列.ALTER TABLE <table_name> DROP UNUSED CO ...
[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 的產生,在一些維護操作前一定要謹慎.減少不必要的維護問題.
