[Translated from mos] SYS_OP_ C2C full table scan (fts)/full index scan, sys_op_c2cfts

[Translated from mos] SYS_OP_ C2C full table scan (fts)/full index scan, sys_op_c2cfts

SYS_OP_ C2C full table scan (fts)/full index Scan

Reference Original:
SYS_OP_ C2C Causing Full Table/Index Scans (Doc ID 732666.1)

Applicable:
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) a query with Bound variables is being executed.
2) bind the variable through application (. net, j2ee, etc.) using the "string" Type Binding variable to bind.
3) The full table scan/index scan is performed for this query error, but the unique index scan or index range scan is not used.
4) use the advanced option to view the explain plan, sqltxlain or 10053 trace. You will notice that a "filter (SYS_OP_ C2C)" is displayed in the "Predicate Information" section )".

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:

"String" the bound variable and the column in the table use different data types.
This means that the data needs to be implicitly converted when the query is executed. SYS_OP_ C2C is an implicit function (implicit function) used to convert fields (columns) between nchar and char.

Solution:

1. Create a function-based index.
E. g create index <index_name> on <table_name> (SYS_OP_ C2C (<column> ));

Or:

2. Make the data type defined by the Bind Variable consistent with the data type of the column.
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 PES ypes to be converted to NVARCHAR2.
E.g.-Doracle. jdbc. defaultNChar = true
<Connection-property name = "defaultNChar"> true </connection-property>