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）
如果进行比较的两个值的数据类型不同，则 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. 让绑定变量定义的数据类型与该列的数据类型一致。

最后更新于 2022-12-05 09:51:23 并被添加「」标签，已有 1682 位童鞋阅读过。

本站使用「署名 4.0 国际」创作共享协议，可自由转载、引用，但需署名作者且注明文章出处

vps 之家

ORACLE中内部函数SYS_OP_C2C和隐式类型转换

此处评论已关闭