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

Source: Internet
Author: User
SYS_OP_ C2C full table scan (fts) full index scan reference: SYS_OP_C2CCausingFullTableIndexScans (DocID732666.1) applicable to: OracleDatabase

SYS_OP_ C2C Full Table scan (fts)/Full Index scan reference: 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 docu

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 On (SYS_OP_ C2C ( ));

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
True

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.