ORACLE中内部函数SYS_OP_C2C和隐式类型转换
什么是sys_op_c2c呢？官方的介绍如下：
sys_op_c2c is an internal function which does an implicit conversion of varchar2 to national character set using to_nchar function. thus, the filter completely changes as compared to the filter using normal comparison.
简单的来说，sys_op_c2c 是一个内部函数，功能是将varchar2的数据类型转换成国家字符集的nvarchar2类型，内部通过to_nchar函数实现。
其实为什么要介绍一下这个内部函数，是因为最近一个同事在优化一个sql语句时，发现即使创建了相关字段的索引，但是sql语句的执行计划仍然不走索引，而走全表扫描，个人在分析了后，发现即使这个索引涉及的三个字段的选择率不高，但是也不是不走索引的原因，而是因为隐式转换问题（全局临时表的跟物理表关联的字段数据类型不一致），如果automatic sql tuning - sql profiles tuning advisor建议创建基于sys_op_c2c的函数索引，或者执行计划中有类似“ filter(sys_op_c2c（column).....”这样的信息，那么你应该检查是否出现了隐式类型转换（implicit type conversion）
什么是隐式类型转换（implicit type conversion）？
如果进行比较的两个值的数据类型不同，则 oracle 必须将其中一个值进行类型转换使其能够比较。这就是所谓的隐式类型转换。通常当开发人员将数字存储在字符列时会导致这种问题的产生。oracle 在运行时会强制转化其中一个值，（由于固定的规则）在索引字符列使用 to_number。由于添加函数到索引列所以导致索引不被使用。实际上，oracle 也只能这么做，类型转换是一个应用程序设计因素。由于转换是在每行都进行的，这会导致性能问题。详见:
document 232243.1 ora-01722 ora-01847 ora-01839 or ora-01858 from queries with dependent predicates
官方文档sys_op_c2c causing full table/index scans (文档 id 732666.1)中有介绍：
applies to:
oracle database - enterprise edition - version 10.1.0.2 to 12.1.0.1 [release 10.1 to 12.1]
information in this document applies to any platform.
this problem can occur on any platform.
symptoms
1) you are executing a query using bind variables.
2) the binding occurs via an application (eg. .net, j2ee ) using a "string" variable to bind.
3) the query is incorrectly performing a full table/index scan instead of an unique/range index scan.
4) when looking at advanced explain plan, sqltxplain or 10053 trace, you notice that the "predicate information" shows is doing a "filter(sys_op_c2c)".
e.g select * from table(dbms_xplan.display_cursor(&sql_id,null,'advanced'));
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(sys_op_c2c("col1")=:b1) <=== filter operation occurring
the bind variable "string" is using a different datatype to the column that is being queried.
this means that an implicit conversion of the data is required to execute the query. sys_op_c2c is the implicit function which is used to convert the column between nchar and char.
solution
1. create a function based index on the column.
e.g create index <index_name> on <table_name> (sys_op_c2c(<column>));
or
2. ensure that your bind "string" datatype and column datatype are the same.
a java example where this can occurs is when defaultnchar=true. this will cause strings to bind as nvarchar2 causing the predicate that are subset datatypes to be converted to nvarchar2.
e.g. -doracle.jdbc.defaultnchar=true
<connection-property name="defaultnchar">true</connection-property>
关于sys_op_c2c内部函数出现的可能原因,概况起来就是
1）正在执行一个带有绑定变量的查询
关于这个，以前也曾经遇到过这样的案例，参考这篇博文oracle绑定变量隐式转换导致性能问题
2）绑定变量经由application（.net, j2ee等）使用 "string" 类型的绑定变量来绑定。
3）该查询错误的执行了全表扫描/索引扫描，而没有使用索引唯一扫描或者索引范围扫描
4）使用advanced 选项查看explain plan, sqltxlain or 10053 trace,你会注意到在"predicate information"部分 会显示一个 "filter(sys_op_c2c)".
解决方案：
1. 建立一个基于函数的索引。
e.g create index <index_name> on <table_name> (sys_op_c2c(<column>));
2. 让绑定变量定义的数据类型与该列的数据类型一致。
参考资料：
sys_op_c2c causing full table/index scans (文档 id 732666.1)