一步一脚印

欢迎来到一步一脚印>>　　 | 首页 资源中心 | BUG | 心情驿站 | Linux/Unix技术 | Oracle技术 | ITPUB论坛

联机重定义修改字段类型(NVARCHAR2->VARCHAR2)

发表人:space6212 | 发表时间: 2008年十一月13日, 22:33

今天在调整一个SQL的时候发现有一个表使用了NVARCHAR2字段类型，在于VARCHAR2类型的字段连接时发生隐式转换(VARCHAR2 -> NVARCAHR2)，导致SQL走了全表扫描。
SQL类似如下：
SELECT * FROM T1,T2 WHERE T1.NAME=T2.NAME AND T1.NAME='XXX';
其中T1的NAME类型为：NVARCHAR2
T2的NAME类型为：VARCHAR2

这个SQL会发生隐式转换(VARCHAR2 -> NVARCHAR2)，所以，实际执行的SQL是：
SELECT * FROM T1,T2 WHERE T1.NAME=SYS_OP_C2C(T2.NAME) AND T1.NAME=U'XXX';
T2.NAME上多了一个函数，所以不能用到这列上的索引。

这个SQL，应该让T1和T2都走索引，其实解决方法有两种：
1、修改SQL
修改方法1：SELECT * FROM T1,T2 WHERE T1.NAME='XXX' AND T2.NAME='XXX';
修改方法2：SELECT * FROM T1,T2 WHERE T1.NAME='XXX' AND TO_CHAR(T1.NAME)=T2.NAME;

由于代码修改量大，且很难确保所有SQL都被修改，并且NVARCHAR2占用的空间多，所以最好的办法是修改表结构。

2、修改表结构
修改表结构需要联机修改，否则会影响业务。
在联机转换NVARCHAR2到VARCHAR2时，有几点需要注意：
1)新表的结构VARCHAR2的长度要足够长，因为NVARCAHR2的长度定义的是字符个数，VARCHAR2定义的是字节个数。在中文环境下把VARCHAR2的长度定义为NVARCHAR长度的2倍即可。
2)如果主键列也需要转换，则重定义的时候options_flag要用ROWID，不能用主键
3)在匹配源表和新表字段的对应关系时，对源表的列要使用函数转换

举一个例子如下：
drop table k1 purge;
drop table k2 purge;
create table k1(a nvarchar2(10) primary key,b nvarchar2(10),c nvarchar2(10));
create table k2(a varchar2(30) primary key,b varchar2(10),c varchar2(10));
insert into k1(a) values('abcsdfsdsa');
insert into k1(a) values('1234567890');
insert into k1(a) values('我是中国人收到测一下');
commit;
exec dbms_redefinition.can_redef_table('suk','k1');
exec DBMS_REDEFINITION.START_REDEF_TABLE('SUK','K1','K2','Translate(a USING CHAR_CS) a,Translate(b USING CHAR_CS) b,Translate(c USING CHAR_CS) c',dbms_redefinition.cons_use_rowid)
exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SUK','K1','K2')
exec dbms_redefinition.finish_redef_table('SUK','K1','K2')

--其中TRANSLATE ... USING 函数用于数据库字符集和国家字符集之间转换
--dbms_redefinition.cons_use_rowid用于指定转换主键时的 option_flag为rowid

分类: ( Oracle技术 ) :: 评论 (11) :: 静态链接网址 :: 引用 (0)

赅 [回复]

溧 铐 驽 觐祆箜桉

bowFeebra | 13/09/2009, 00:13

[回复]

腓腓 镱腩骅豚 痼觇 屐

bowFeebra | 13/09/2009, 00:23

礤 铗镨疣轳 [回复]

bowFeebra | 13/09/2009, 00:33

祛泱 [回复]

赅

bowFeebra | 13/09/2009, 00:44

[回复]

耱疣

bowFeebra | 13/09/2009, 00:53

怦 [回复]

bowFeebra | 13/09/2009, 01:32

[回复]

磬 赅觐

bowFeebra | 13/09/2009, 01:43

礤 [回复]

耱囵 颃驽

bowFeebra | 13/09/2009, 01:55

眍 怦 [回复]

bowFeebra | 13/09/2009, 02:09

囫 [回复]

bowFeebra | 13/09/2009, 02:23

忑蝌 [回复]

磬

bowFeebra | 13/09/2009, 02:44

发表评论

标题

在此添加评论

称呼

邮箱地址（可选）

个人主页（可选）




Valid XHTML 1.0 Strict and CSS. Powered by pLog
Design by Blog.lvwo.com