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

CAUSE

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. 让绑定变量定义的数据类型与该列的数据类型一致。

参考资料：

https://hoopercharles.wordpress.com/2015/02/08/to-n-or-not-to-n-is-sys_op_c2c-the-question-oracle-nvarchar-slow/

SYS_OP_C2C Causing Full Table/Index Scans ( 文档 ID 732666.1)