关于VARCHAR2(2 CHAR) VS VARCHAR2(2)

PUB里看到一个网友修改数据类型导致很慢。

 

原文如下：

 

我在生产环境执行

change column value_yy  from varchar2(2 char)  to varchar2(2);

执行下面命令
alter table t_1 modify value_yy varchar2(2);

t_1 800万记录。

非常慢。

我想知道，怎么知道该命令消耗的是哪些资源。 cpu 内存 io

哪里是瓶颈

谢谢

原帖地址：http://www.itpub.net/thread-1637794-1-1.html

 

对于这个问题需要区分 varchar2(2 char) 和 varchar2(2 byte)

varchar2(2 char)  表示2个字符，这个类型是可以存储2个多字节字符的，譬如2个汉字。
varchar2(2 byte)  表示2个字节，这个类型是不能存储2个多字节字符的。

如下所示：

SQL> CREATE TABLE T(C1 VARCHAR2(2 CHAR),C2 VARCHAR2(2 BYTE));
表已创建。
SQL> DESC T
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
C1 VARCHAR2(2 CHAR)
C2 VARCHAR2(2)
SQL> INSERT INTO T VALUES('华腾','华腾');
INSERT INTO T VALUES('华腾','华腾')
*
第 1 行出现错误:
ORA-12899: value too large for column "ZYLIFE"."T"."C2" (actual: 6, maximum: 2)

SQL> INSERT INTO T VALUES('华腾','华');
INSERT INTO T VALUES('华腾','华')
*
第 1 行出现错误:
ORA-12899: value too large for column "ZYLIFE"."T"."C2" (actual: 3, maximum: 2)

SQL> INSERT INTO T VALUES('华腾','AB');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> SELECT * FROM T;
C1 C2
---- ----
华腾 AB

注意：在UTF8下一个汉字代表3个字节。

如果只写了varchar2(2)， 那么这个是代表 varchar2(2 char)  还是 varchar2(2 byte)

这取决于初始化参数nls_length_semantics的设置，默认这个参数被设置为byte。

nls_length_semantics=byte  varchar2(2)=varchar2(2 byte)
nls_length_semantics=char  varchar2(2)=varchar2(2 char)

网友的例子从 varchar2(2 char) 改为 varchar2(2) ，反应很慢。
基本是因为字符长度变小导致Oracle去验证列的数据是否违反修改后的长度，由于表中的数据有800W，因此表现很慢。

看如下的例子：

SQL> show parameter nls_length
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
nls_length_semantics string BYTE
SQL> create table test as select * from dba_objects;
Table created.
SQL> insert /*+append*/ into test select * from test;
369314 rows created.

SQL> commit;
Commit complete.
SQL> insert /*+append*/ into test select * from test;
738628 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+append*/ into test select * from test;
1477256 rows created.
SQL> commit;
Commit complete.
SQL> set linesize 80
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> set timing on
SQL> alter table test modify OBJECT_NAME VARCHAR2(128 char);
Table altered.
Elapsed: 00:00:00.06
SQL> alter table test modify OBJECT_NAME VARCHAR2(128);
Table altered.
Elapsed: 00:00:04.22
SQL>

我用了大约150W的记录，可以看到从VARCHAR2(128)改为VARCHAR2(128 CHAR)是非常快的，不到1秒。
而从VARCHAR2(128 CHAR)改为 VARCHAR2(128)用了4秒多，当然我的测试环境的硬件能力比较好，效果不是很明显。

[oracle@testdb1 ~]$ lsdev -Cc processor
proc0  Available 00-00 Processor
proc2  Available 00-02 Processor
proc4  Available 00-04 Processor
proc6  Available 00-06 Processor
proc8  Available 00-08 Processor
proc10 Available 00-10 Processor
proc12 Available 00-12 Processor
proc14 Available 00-14 Processor
proc16 Available 00-16 Processor
proc18 Available 00-18 Processor
proc20 Available 00-20 Processor
proc22 Available 00-22 Processor
proc24 Available 00-24 Processor
proc26 Available 00-26 Processor
proc28 Available 00-28 Processor
proc30 Available 00-30 Processor
[oracle@testdb1 ~]$ lsattr -El mem0
goodsize 63232 Amount of usable physical memory in Mbytes False
size     63232 Total amount of physical memory in Mbytes  False

[oracle@testdb1 ~]$ lsdev -Cc disk | more
hdisk0    Available 07-08-00-3,0 16 Bit LVD SCSI Disk Drive
hdisk1    Available 07-08-00-4,0 16 Bit LVD SCSI Disk Drive
hdisk2    Available 06-08-02     XP Disk Array (Fibre)
hdisk3    Available 06-08-02     XP Disk Array (Fibre)
hdisk4    Available 06-08-02     XP Disk Array (Fibre)
hdisk5    Available 06-08-02     XP Disk Array (Fibre)
hdisk6    Available 06-08-02     XP Disk Array (Fibre)
hdisk7    Available 06-08-02     XP Disk Array (Fibre)
hdisk8    Available 06-08-02     XP Disk Array (Fibre)
hdisk9    Available 06-08-02     XP Disk Array (Fibre)
hdisk10   Available 06-08-02     XP Disk Array (Fibre)
hdisk11   Available 06-08-02     XP Disk Array (Fibre)
hdisk12   Available 06-08-02     XP Disk Array (Fibre)
hdisk13   Available 06-08-02     XP Disk Array (Fibre)
hdisk14   Available 06-08-02     XP Disk Array (Fibre)
hdisk15   Available 06-08-02     XP Disk Array (Fibre)
hdisk16   Available 06-08-02     XP Disk Array (Fibre)
hdisk17   Available 06-08-02     XP Disk Array (Fibre)
hdisk18   Available 06-08-02     XP Disk Array (Fibre)
hdisk19   Available 06-08-02     XP Disk Array (Fibre)
hdisk20   Available 06-08-02     XP Disk Array (Fibre)
hdisk21   Available 06-08-02     XP Disk Array (Fibre)
hdisk22   Available 06-08-02     XP Disk Array (Fibre)
hdisk23   Available 06-08-02     XP Disk Array (Fibre)
hdisk24   Available 06-08-02     XP Disk Array (Fibre)
hdisk25   Available 06-08-02     XP Disk Array (Fibre)
hdisk26   Available 06-08-02     XP Disk Array (Fibre)
hdisk27   Available 06-08-02     XP Disk Array (Fibre)
hdisk28   Available 06-08-02     XP Disk Array (Fibre)
hdisk29   Available 06-08-02     XP Disk Array (Fibre)
hdisk30   Available 06-08-02     XP Disk Array (Fibre)