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

今天在调整一个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

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/447173/viewspace-1021508/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/447173/viewspace-1021508/