Tuning na Instruçao SQL LIKE no Oracle

Fala pessoal, vou postar aqui um dica simples, que é quando se precisa de um tunning rápido nas declarações "LIKE"  que o CBO naturalmente ignora os índices existentes nas colunas utilizadas pelo LIKE para uso de um FTS. Quando ocorre isso temos o recurso de utilizar um hint forçando a utilização do índice.

No Blog do Laurent Schneider tem um demo bem legal.
Abaixo a demonstração.

CREATE TABLE lsc_t AS
SELECT
  ROWNUM ID,
  SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
 || ' '
 || DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
 ,1,17) NAME,
  TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
  LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5;

ALTER TABLE lsc_t ADD PRIMARY KEY(ID);

CREATE INDEX lsc_i ON lsc_t(NAME);

EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true);

SQL> SELECT ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';  2
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |  5044 |   123K| 22123   (1)| 00:04:26 |
|*  1 |  TABLE ACCESS FULL| LSC_T |  5044 |   123K| 22123   (1)| 00:04:26 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
  379  recursive calls
    0  db block gets
  100090  consistent gets
  100016  physical reads
    0  redo size
    2066  bytes sent via SQL*Net to client
  491  bytes received via SQL*Net from client
    4  SQL*Net roundtrips to/from client
    5  sorts (memory)
    0  sorts (disk)
   35  rows processed

SWLQL> SELECT /*+INDEX(LSC_T,LSC_I)*/  ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';  2

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  5044 |   123K|  3574   (1)| 00:00:43 |
|   1 |  TABLE ACCESS BY INDEX ROWID| LSC_T |  5044 |   123K|  3574   (1)| 00:00:43 |
|*  2 |   INDEX FULL SCAN           | LSC_I |  5000 |       |   220   (2)| 00:00:03 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
    1  recursive calls
    0  db block gets
  360  consistent gets
  322  physical reads
    0  redo size
  142849  bytes sent via SQL*Net to client
  491  bytes received via SQL*Net from client
    4  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
   35  rows processed