Wow, I wasn’t aware that Oracle can also do an implicit datatype conversion for literal strings during parsing phase!
SQL> @desc t
Name Null? Type
------------------------------- -------- ----------------------------
1 A NUMBER(38)
SQL> select * from t where a = '1' || RPAD('0',5,'0');
no rows selected
SQL> @x
Display execution plan for last statement for this session from library cache...
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID d7r6md8wfu74d, child number 0
-------------------------------------
select * from t where a = '1' || RPAD('0',5,'0')
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 2 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=100000)
You see what happened? The expression ‘1’ || RPAD(‘0′,5,’0’) has been evaluated, which returns a string. And this string ‘100000’ has been converted to a NUMBER 100000 during parsing phase .. otherwise you would see quotes around the number above with a TO_NUMBER() function around it (so that Oracle could compare the NUMBER column “A” to the same datatype)…
I add a TO_CHAR() around the column A just for demoing that a varchar datatype (as the original “literal” in my query is) will be shown with quotes like every normal string:
SQL> select * from t where to_char(a) = '1'||rpad('0',5,'0');
no rows selected
SQL> @x
Display execution plan for last statement for this session from library cache...
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 7yf6j8fdyrvk7, child number 0
-------------------------------------
select * from t where to_char(a) = '1'||rpad('0',5,'0')
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 2 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR("A")='100000')
Let’s see whether this trick is somehow done also for bind variables:
SQL> var x varchar2(10)
SQL> exec :x:= '1' || RPAD('0',5,'0');
PL/SQL procedure successfully completed.
SQL> print x
X
--------------------------------
100000
SQL> select * from t where a = :x;
no rows selected
SQL> @x
Display execution plan for last statement for this session from library cache...
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 45f39y7580bdp, child number 2
-------------------------------------
select * from t where a = :x
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 2 (0)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (VARCHAR2(30), CSID=873): '100000'
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=TO_NUMBER(:X))
Apparently not! And this kind of makes sense – as if this string to number conversion is done during parse phase – Oracle doesn’t know what the actual value is yet (in the bind variable memory) so it can’t convert it to number in advance either :-)
This is a little interesting detail… I didn’t know that in addition to the implicit datatype conversion during query execution (using TO_CHAR, TO_NUMBER functions etc) Oracle can sometimes convert a string literal to number datatype under the hood during the parse time!
P.S. I tested this on Oracle 11.2.0.2 with optimizer_features_enable set from 11.2.0.2 to all the way back to 8.0.0 and the behavior was the same. I didn’t find any mention of this conversion in the CBO tracefile although after a filter pushdown transformation (FPD) the string literal was already shown as a number datatype. If anyone still has access to ancient Oracle database versions (like 9.2 and 10.1 ;-) then let me know whether you see the same results!
