Implicit datatype conversion in the parsing phase – something new I learned today!

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!

This entry was posted in Cool stuff, Oracle and tagged , . Bookmark the permalink.

6 Responses to Implicit datatype conversion in the parsing phase – something new I learned today!

  1. Tanel Poder says:

    A small addition to the beginning of the post -> This column A in table T is a NUMBER column (I added this info to the beginning)

  2. Thomas Kyte says:

    Yet another reason to dislike cursor sharing force/similar:

    ops$tkyte%ORA11GR2> alter session set cursor_sharing=’force’;

    Session altered.

    ops$tkyte%ORA11GR2> select * from t t2 where a = ’1′||rpad(’0′,5,’0′);

    no rows selected

    ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

    PLAN_TABLE_OUTPUT
    ——————————————————————————-
    SQL_ID bttpcuamf3n92, child number 0
    ————————————-
    select * from t t2 where a = :”SYS_B_0″||rpad(:”SYS_B_1″,:”SYS_B_2″,:”SY
    S_B_3″)

    Plan hash value: 1601196873

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | SELECT STATEMENT | | | | 2 (100)| |
    |* 1 | TABLE ACCESS FULL| T | 1 | 13 | 2 (0)| 00:00:01 |
    ————————————————————————–

    Predicate Information (identified by operation id):
    —————————————————

    1 – filter(“A”=TO_NUMBER(:SYS_B_0||RPAD(:SYS_B_1,:SYS_B_2,:SYS_B_3)))

  3. Gints Plivna says:

    Seems the same at least for 9.2, I don’t have anything more ancient :)

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.7.0 – Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.7.0 – Production

    SQL> select * from t where a = ’1′ || RPAD(’0′,5,’0′);

    no rows selected

    SQL> select sql_text, hash_value
    2 from v$sql where sql_text like ‘select * from t where a =%’;

    SQL_TEXT
    ——————————————————————-
    HASH_VALUE
    ———-
    select * from t where a = ’1′ || RPAD(’0′,5,’0′)
    1495464911

    SQL> select operation || ‘ ‘ || options, filter_predicates
    2 from v$sql_plan where hash_value = ’1495464911′;

    OPERATION||”||OPTIONS
    ——————————————————————-
    FILTER_PREDICATES
    ——————————————————————-
    SELECT STATEMENT

    TABLE ACCESS FULL
    “A”=100000

  4. Tanel Poder says:

    @Gints Plivna

    Cool, thanks!

    Interesting that it has gone unnoticed (by me at least) so far…

  5. I think the mechanism is very old – the optimizer has been doing “constant-folding” for years. The earliest version I can demonstrate is 8i, though. Here’s an example of the optimizer predicate output from 8i for a test similar to your 11g test with a supplied predicate of: A = ’1′ || rpad(’0′,4,’0′)

    Id Text
    — ——————————-
    1 Table:
    2 T1
    3 frofkks[i] (index start key)
    4 “T1″.”A”=10000
    5 frofkke[i] (index stop key)
    6 “T1″.”A”=10000

    6 rows selected.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>