[20171113]修改表結構刪除列相關問題.txt

[20171113]修改表結構刪除列相關問題.txt

--//維護表結構刪除欄位一般都是先
ALTER TABLE <table_name> SET UNUSED (<column_name>);

--//然後等空閒時候刪除列.
ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>;

--//參考文件:

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 的產生,在一些維護操作前一定要謹慎.減少不必要的維護問題.