Symptom: SYS_OP_C2C Causing FULL Table Scan Instead of Index Range Scan
SYS_OP_C2C is the implicit function that is used to convert the column between NCHAR and CHAR;
Cause:
When bind variable "string" is using a different datatype (e.g. NVARCHAR2) to the column (e.g. VARCHAR2) that is being queried,
index range scan is ignored.
Solution:
Create a function base index on the column using the following syntax:
create index <index_name> on <table_name> (SYS_OP_C2C(<column>));
Here's the test:
alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
create table test_char
(c1 varchar2(10));
begin
for i in 1 .. 100000 loop
insert /*+APPEND*/ into test_char(c1) values('中'||i);
end loop;
commit;
end;
/
create index idx_test_char on test_char(c1) ;
var search_text nvarchar2(10)
exec :search_text:='中100';
select * from test_char where c1 = '中100';
C1
--------------------
中100
select * from test_char where c1 = :search_text;
C1
--------------------
中100
col sql_id format a20
col sql_text format a50
select sql_id,sql_text from v$sql where sql_text like '%test_char%';
>>
SQL_ID SQL_TEXT
-------------------- --------------------------------------------------
42tbgppd765j2 select * from test_char where c1 = :"SYS_B_0"
2tbafah8m78x0 select * from test_char where c1 = :search_text
select * from table(dbms_xplan.display_cursor('42tbgppd765j2',null,'ADVANCED'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 42tbgppd765j2, child number 0
-------------------------------------
select * from test_char where c1 = :"SYS_B_0"
Plan hash value: 4273954991
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_TEST_CHAR | 1 | 7 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST_CHAR@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "TEST_CHAR"@"SEL$1" ("TEST_CHAR"."C1"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :SYS_B_0 (VARCHAR2(30), CSID=873): '中100'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=:SYS_B_0)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "C1"[VARCHAR2,10]
Note
-----
- dynamic sampling used for this statement (level=2)
已選取 51 個資料列.
select * from table(dbms_xplan.display_cursor('2tbafah8m78x0',null,'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID 2tbafah8m78x0, child number 0
-------------------------------------
select * from test_char where c1 = :search_text
Plan hash value: 718057891
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 70 (100)| |
|* 1 | TABLE ACCESS FULL| TEST_CHAR | 3 | 21 | 70 (3)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / TEST_CHAR@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TEST_CHAR"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :SEARCH_TEXT (NVARCHAR2(30), CSID=2000): '中100'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYS_OP_C2C("C1")=:SEARCH_TEXT)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "C1"[VARCHAR2,10]
Note
-----
- dynamic sampling used for this statement (level=2)
已選取 51 個資料列.
create index idx2_test_char on test_char(SYS_OP_C2C("C1")) ;
var search_text nvarchar2(10)
exec :search_text:='中100';
select * from test_char where c1 = '中100';
C1
--------------------
中100
select * from test_char where c1 = :search_text;
C1
--------------------
中100
col sql_id format a20
col sql_text format a50
select sql_id,sql_text from v$sql where sql_text like '%test_char%';
SQL_ID SQL_TEXT
-------------------- --------------------------------------------------
42tbgppd765j2 select * from test_char where c1 = :"SYS_B_0"
2tbafah8m78x0 select * from test_char where c1 = :search_text
select * from table(dbms_xplan.display_cursor('42tbgppd765j2'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID 42tbgppd765j2, child number 0
-------------------------------------
select * from test_char where c1 = :"SYS_B_0"
Plan hash value: 4273954991
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_TEST_CHAR | 1 | 7 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=:SYS_B_0)
Note
-----
- dynamic sampling used for this statement (level=2)
已選取 22 個資料列.
select * from table(dbms_xplan.display_cursor('2tbafah8m78x0'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 2tbafah8m78x0, child number 0
-------------------------------------
select * from test_char where c1 = :search_text
Plan hash value: 3111331547
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 67 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_CHAR | 905 | 17195 | 67 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX2_TEST_CHAR | 362 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TEST_CHAR"."SYS_NC00002$"=:SEARCH_TEXT)
Note
-----
- dynamic sampling used for this statement (level=2)
已選取 23 個資料列.
[reference]
My Oracle Support: SYS_OP_C2C Causing Full Table/Index Scans [Doc ID 732666.1]
留言列表
{{ article.title }}