... We use bind variables too....NO YOU DO NOT, YOU ARE USING CURSOR_SHARING=force or similar.I can tell by this:
streamId= :"SYS_B_0";
If you go back to the application, you'll find it has something like:
streamId = '0123456789ABCDEF';
in it, they are putting a string in the sql statement and we are 'auto binding it'
You should
a) rewrite the sql in the application to use a bind
b) bind a RAW
then it will work.
short of that, when you compare datatype1 to datatype2 (you are comparing a RAW to a STRING), one of the types must be converted. Here the rule is "convert raw to string", you can see that easily;
ops$tkyte%ORA11GR1> create table t ( x raw(10) );
Table created.
ops$tkyte%ORA11GR1> @at
ops$tkyte%ORA11GR1> select * from t where x = '01020304';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 2 (0)| 00:00:01
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(RAWTOHEX("X")='01020304')
Note
-----
- dynamic sampling used for this statement
the rule is - we KNOW we can convert the raw into hex without error, we are not sure the string can be converted into RAW (that it is in hex) without error - so we do the natural thing - convert the raw to hex and compare.
Now, you have an index on that raw field, not on rawtohex() of that field.
So, your two options if you don't fix the really bad bug in the code are
a) index rawtohex(your_column) instead of indexing your column.
b) use hextoraw('your string') in your code
but if you do (b) you might as well FIX THE CODE
But please, do not say you are using binds, you are NOT. You have cursor sharing set to some value other than EXACT
ops$tkyte%ORA11GR1> alter session set cursor_sharing = force;
Session altered.
<b>
ops$tkyte%ORA11GR1> select * from t where x = '010101';
</b>
no rows selected
ops$tkyte%ORA11GR1> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 7287p5bwnfakf, child number 0
-------------------------------------
<b>select * from t where x = :"SYS_B_0"</b>
Plan hash value: 1601196873
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 7 | 2 (0)| 00:00:01
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
<b>
1 - filter(RAWTOHEX("X")=:SYS_B_0)
</b>
Note
-----
- dynamic sampling used for this statement
22 rows selected.