[20180613]縮短欄位長度.txt

[20180613]縮短欄位長度.txt

--//最近遇到的一個問題,就是修改欄位長度.理論講增加欄位長度沒有什麼問題,而縮短我記憶裡好像不行的,
--//即使當前記錄滿足縮短欄位長度需求.
--//透過例子說明:

1.環境:
SCOTT@book> @ 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

create table tx( id number,vc varchar2(100));
insert into tx select rownum,dbms_random.string('U',20) from dual connect by level<=10;
commit;

2.測試:
SCOTT@book> alter table tx modify vc varchar2(19);
alter table tx modify vc varchar2(19)
                      *
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big
--//太短不行.

SCOTT@book> alter table tx modify vc varchar2(20);
Table altered.

SCOTT@book> @ &r/desc tx
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                                       NUMBER
    2      VC                                       VARCHAR2(20)

--//OK沒有任何問題,看來我以前的記憶存在問題.還是以前遇到的版本有這個問題.
--//但是實際上這樣很慢的,它必須加TM鎖,進入資料塊檢查,才能完成修改操作.

3.做一個跟蹤看看:
SCOTT@book> @ &r/10046on 12
Session altered.

SCOTT@book> alter table tx modify vc varchar2(120);
Table altered.

SCOTT@book> alter table tx modify vc varchar2(20);
Table altered.

SCOTT@book> @ &r/10046off
Session altered.

--//執行tkprof後檢查:
SQL ID: 9raphb4ba5mc7 Plan Hash: 0

LOCK TABLE "TX" IN EXCLUSIVE MODE  NOWAIT

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          1          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          1          0           0

--//上鎖2次.

SQL ID: 0nuddc4bgbuvs Plan Hash: 0
alter table tx modify vc
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          2          0           0
Execute      2      0.01       0.01          0         80          4           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0         82          4           0

--//sql語句後面的看不到.

SQL ID: fkjk2s4bvknkk Plan Hash: 40191160
select /*+ first_rows */ 1
from
"SCOTT"."TX" where LENGTHB("VC") > 20

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          7          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          8          0           0

--//很明顯在執行alter table tx modify vc varchar2(20);時要掃描表TX一次.
--//而alter table tx modify vc varchar2(120);沒有類似的操作.