Tuning na Instrução SQL LIKE no Oracle

Tuning na Instrução SQL LIKE no Oracle

Fala pessoal, vou postar aqui um dica simples, que é quando se precisa de um tuning 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