Oracle SQL

    Tag Archives: HEXTORAW

    REGEXP_LIKE: strange unspecified value in parameter “modifier”

    Posted on July 23, 2014 by Sayan Malakshinov Posted in bug, CBO, curious, oracle Leave a comment

    Today I noticed strange thing in predicate section of execution plan for simple query with regexp_like, where 3rd parameter “MODIFIER” was not specified:

    SQL> select * from dual where regexp_like(dummy,'.');
    
    D
    -
    X
    
    SQL> select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------
    SQL_ID  97xuqf9cmjsta, child number 0
    -------------------------------------
    select * from dual where regexp_like(dummy,'.')
    
    Plan hash value: 272002086
    
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
    |*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter( REGEXP_LIKE ("DUMMY",'.',HEXTORAW('F07FD85CFF0700006A1116
                  45010000000000000000000000FC12164501000000000000000000000000000000000000
                  0010000000000000001880D85CFF07000002000000000000000000000081000000') ))
    
    
    20 rows selected.
    
    

    It is particularly interesting that the values in HEXTORAW() are always different for different first parameters:

    SQL> select * from dual where regexp_like(dummy,'x');
    ...
       1 - filter( REGEXP_LIKE ("DUMMY",'x',HEXTORAW('3895D330FF0700006A1116
                  45010000000000000000000000FC12164501000000000000000000000000000000000000
                  0011000000000000006895D330FF07000002000000000000000000000081000000') ))
    
    SQL> select * from dual where regexp_like(dummy,'y');
    ...
       1 - filter( REGEXP_LIKE ("DUMMY",'y',HEXTORAW('00DA3C3FFF0700006A1116
                  45010000000000000000000000FC12164501000000000000000000000000000000000000
                  00110000000000000030DA3C3FFF07000002000000000000000000000081000000') ))
    
    SQL> select * from dual where regexp_like(dummy||'','x')
    ...
       1 - filter( REGEXP_LIKE ("DUMMY"||'','x',HEXTORAW('70964F2FFF0700006A
                  111645010000000000000000000000FC1216450100000000000000000000000000000000
                  0000001100000000000000A0964F2FFF07000002000000000000000000000081000000')
                   ))
    

    I don’t know, what does it mean, but it looks like garbage from memory.
    When I noticed this, I decided to check how regexp_like will work in function-based indexes:

    SQL> create table xtest as
      2    select dummy||level as str
      3    from dual
      4    connect by level<=30;
    
    Table created.
    
    SQL> select * from xtest where case when regexp_like(str,'1') then 1 end = 1;
    ...
    12 rows selected.
    
    SQL> select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------
    SQL_ID  7ztp0k8c1zn2h, child number 0
    -------------------------------------
    select * from xtest where case when regexp_like(str,'1') then 1 end = 1
    
    Plan hash value: 4207139086
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |       |       |     3 (100)|          |
    |*  1 |  TABLE ACCESS FULL| XTEST |    12 |   264 |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(CASE  WHEN  REGEXP_LIKE
                  ("STR",'1',HEXTORAW('68F9CB32FF0700006A111645010000000000000000000000FC1
                  216450100000000000000000000000000000000000000110000000000000098F9CB32FF0
                  7000002000000000000000000000081000000') ) THEN 1 END =1)
    
    SQL> create index xtest_fbi on xtest(case when regexp_like(str,'1') then 1 end);
    
    Index created.
    
    SQL> select * from xtest where case when regexp_like(str,'1') then 1 end = 1;
    ...
    12 rows selected.
    
    SQL> select * from table(dbms_xplan.display_cursor);
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------
    SQL_ID  7ztp0k8c1zn2h, child number 0
    -------------------------------------
    select * from xtest where case when regexp_like(str,'1') then 1 end = 1
    
    Plan hash value: 1479471124
    
    -----------------------------------------------------------------------------------------
    | Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |           |       |       |     2 (100)|          |
    |   1 |  TABLE ACCESS BY INDEX ROWID| XTEST     |    12 |   300 |     2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | XTEST_FBI |    12 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("XTEST"."SYS_NC00002$"=1)
    
    SQL> select column_expression from user_ind_expressions e where e.index_name='XTEST_FBI';
    
    COLUMN_EXPRESSION
    -----------------------------------------------------------------------------------------
    CASE  WHEN  REGEXP_LIKE ("STR",'1') THEN 1 END
    
    

    As you can see it works fine, although the predicate from first execution plan differs from the FBI expression.
    Then I dumped 10053 trace and noticed that the HEXTORAW(…) function appeared in “Explain Plan Dump” only, so it looks just like plan output bug.

    execution plan HEXTORAW regexp_line

    Simple Android Oracle client

    Get it on Google Play

    About us

    photo Sayan Malakshinov

    Oracle ACE Associate Oracle ACE Associate
    Oracle performance tuning expert
    Russia / Moscow / Transmedia Dynamics
    photo Bair Malakshinov

    Ph.d candidate
    Senior Oracle Developer
    Poland / Cracow / Luxoft

    Popular Posts

    • Differences between integer(int) in SQL and PL/SQL 0 comments
    • Deterministic function vs scalar subquery caching. Part 1 8 comments
    • Amazing optimization of getting distinct values from the index, and TopN for each of them 4 comments
    • SQL*Plus tips #6: Colorizing output 4 comments
    • SQL*Plus tips #5: sql_text/sql_fulltext formatting(sql beatifier) 13 comments
    • SQL*Plus tips. #1 5 comments
    • A couple of well-known but often forgotten things for PL/SQL developers 2 comments
    • SYS_OP_MAP_NONNULL is in the documentation now 0 comments
    • SQL*Plus tips #4: Branching execution 0 comments
    • Oracle 12c: Lateral, row_limiting_clause 3 comments

    Recent Posts

    • Top-N again: fetch first N rows only vs rownum
    • Docker with Oracle database: install patches automatically
    • Top N biggest tables (with lobs, indexes and nested table)
    • “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”

    Email Subscription

    Recent Comments

    • SQL*Plus 256 Colours in terminal | EDUARDO CLARO on SQL*Plus tips #6: Colorizing output
    • A simple SQL*Plus parameter parser | EDUARDO CLARO on SQL*Plus tips. #1
    • Sayan Malakshinov on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • Frits on “Collection iterator pickler fetch”: pipelined vs simple table functions
    • SQL*Plus tips #8: How to read the output of dbms_output without 'serveroutput on' - SSWUG.ORG on SQL*Plus tips #8: How to read the output of dbms_output without “serveroutput on”
    • Adaptive serial direct path read decision ignores object statistics since 12.1 - SSWUG.ORG on Adaptive serial direct path read decision ignores object statistics since 12.1
    • Oracle issues after upgrade to 12.2 - SSWUG.ORG on Oracle issues after upgrade to 12.2
    • Ampersand instead of colon for bind variables - SSWUG.ORG on Ampersand instead of colon for bind variables
    • Евгений Бабин on Oracle issues after upgrade to 12.2
    • Oracle SQL | How even empty trigger increases redo generation on Triggers and Redo: changes on 12.2

    Blogroll

    • Alex Fatkulin
    • Alexander Anokhin
    • Andrey Nikolaev
    • Charles Hooper
    • Christian Antognini
    • Coskan Gundogar
    • David Fitzjarrell
    • Igor Usoltsev
    • Jonathan Lewis
    • Karl Arao
    • Mark Bobak
    • Martin Bach
    • Martin Berger
    • Neil Chandler
    • Randolf Geist
    • Richard Foote
    • Riyaj Shamsudeen
    • Tanel Poder
    • Timur Akhmadeev
    • Valentin Nikotin

    Categories

    Aggregated by OraNA Aggregated by OraFAQ

    Meta

    • Log in
    • Entries RSS
    • Comments RSS
    • WordPress.org
    ©Sayan Malakshinov. Oracle SQL