Karl Reitschuster's Tech Space

Hi reader,

usually queries using LIKE and leading '%' cannot be indexed. So an idea would be to mirror the string content with the string reverse function to be able to put the '%' operator at the end :

SQL>
SET ECHO ON

SELECT REVERSE('gniK sI suseJ')
  FROM Dual;

REVERSE('GNIKSISUSEJ')
----------------------
Jesus Is King

SQL>

Now to a practical example a demonstration with large amount of data and the effect using a function based index for our reverse scenario :

SQL> set autotrace on
SQL> set line 120
SQL> set timing on

SQL> SELECT COUNT(*)
  2    FROM Company
  3   WHERE City LIKE '%stadt';
  COUNT(*)
----------
     93581

Abgelaufen: 00:00:55.76

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 1412938433
-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |    16 | 55298   (1)| 00:11:04 |       |       |
|   1 |  SORT AGGREGATE     |         |     1 |    16 |            |          |       |       |
|   2 |   PARTITION LIST ALL|         |   339K|  5309K| 55298   (1)| 00:11:04 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL| COMPANY |   339K|  5309K| 55298   (1)| 00:11:04 |     1 |     4 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CITY" LIKE '%stadt')

Statistiken
----------------------------------------------------------
         50  recursive calls
          0  db block gets
     251404  consistent gets
     249967  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

SQL> CREATE INDEX COMP_FX01 ON COMPANY ( REVERSE (CITY));
Index wurde erstellt.
Abgelaufen: 00:01:24.01

SQL> SELECT COUNT(*)
  2    FROM Company
  3   WHERE reverse(City) LIKE 'tdats%';

  COUNT(*)
----------
     93581

Abgelaufen: 00:00:00.12

Ausf³hrungsplan
----------------------------------------------------------
Plan hash value: 1998044629
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    16 |   136   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE   |           |     1 |    16 |            |          |
|*  2 |   INDEX RANGE SCAN| COMP_FX01 |   347K|  5426K|   136   (0)| 00:00:02 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(REVERSE("CITY") LIKE 'tdats%')
       filter(REVERSE("CITY") LIKE 'tdats%')

Statistiken
----------------------------------------------------------
         93  recursive calls
          0  db block gets
        442  consistent gets
        422  physical reads
          0  redo size
        337  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

The differences  in timing and resource consumption are dramatically.

think about it

Karl Reitschuster