Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> conn dluo/dluo
Connected.
SQL> create table t as select object_name, object_id, status from dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats('dluo','t',cascade=>true);
PL/SQL procedure successfully completed.
SQL> create index t_f1 on t(reverse(object_name));
Index created.
SQL> exec dbms_stats.gather_table_stats('dluo','t',cascade=>true);
PL/SQL procedure successfully completed.
SQL> explain plan for select * from t
2 where reverse(object_name) like reverse('%TABLE');
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 271412068
------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------
0 SELECT STATEMENT 2 72 3 (0) 00:00:01
1 TABLE ACCESS BY INDEX ROWID T 2 72 3 (0) 00:00:01
* 2 INDEX RANGE SCAN T_F1 2 2 (0) 00:00:01
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access(REVERSE("OBJECT_NAME") LIKE 'ELBAT%')
filter(REVERSE("OBJECT_NAME") LIKE 'ELBAT%')
15 rows selected.
SQL>
Word of caution: Reverse is an undocumented function in 10g so better not use this in a production system.
The article was posted here.
