I ran across an app problem last week where a simple SQL that should have used a unique index didn't. Eventually the dim bulb in my head flickered on for a micro-sec or 2, and I finally noticed the "SYS_OP_C2C" function call being added to the filter, thus negating the use of the index. Tracing the session showed that the bind variable being passed in was in UTF-16 encoding (NVARCHAR2 to my database), while the database (and all the columns in the table) use the AL32UTF8 characterset. I showed this to the developer, the code got changed, the bind became a "VARCHAR2", and things worked as expected.
I understand that Oracle was using SYS_OP_C2C to implicitly convert the VARCHAR2 column to NVARCHAR2 to match the bind variable datatype for a comparison. Does anyone know why Oracle converts the column instead of the bind variable?
(I'm just curious about why Oracle does what it does in certain cases. I know Oracle isn't likely to change this any time soon.)
11.2.0.3 EE
define vNCHAR NVARCHAR2(30)
...
select * from blah where object_name = :vNCHAR; /* "BLAH" has a unique index on OBJECT_NAME */
...--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
|* 1 | TABLE ACCESS FULL| BLAH | 10 | 850 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYS_OP_C2C("OBJECT_NAME")=:VNCHAR) <---------- !!!!!
And if I explicitly convert that bind variable, the execution uses the index as expected:
select * from blah where object_name = sys_op_c2c(:vNCHAR);
...
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| BLAH | 1 | 85 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | BLAHUI | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"=SYS_OP_C2C(:VNCHAR))