[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);没有类似的操作.
