Performance Tuning: How to make "like '%xxxx' also using oracle index
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
