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)