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.