ITPub博客

首页 > 应用开发 > IT综合 > 联机重定义修改字段类型(NVARCHAR2->VARCHAR2)

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

原创 IT综合 作者:space6212 时间:2019-06-16 14:06:05 0 删除 编辑
今天在调整一个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
[@more@]

这个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

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

下一篇: blog将恢复更新
请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    188
  • 访问量
    142076