Oracle LIKE clause searches with text indexes

One serious SQL performance problem occurs when you use the SQL “LIKE clause” operator to find a string within a large Oracle table column (e.g. VARCHAR(2000), CLOB, BLOB):

Select stuff from bigtab where text_column like ‘%ipod%’;
Select stuff from bigtab where full_name like ‘%JONES’;

Because standard Oracle cannot index into a large column, there “like” queries cause full-table scans, and Oracle must examine every row in the table, even when the result set is very small. These unnecessary full-table scans are a problem:

1. Large-table full-table scans increase the load on the disk I/O sub-system

2. Small table full table scans (in the data buffer) cause high consistent gets and drive-up CPU consumption


Ver Articulo completo

A CONTEXT Query Application

Oracle SQL "contains" clause tips

Filtrar por cadenas parecidas en una lista en oracle

Oracle case insensitive searches(Implementación)

Búsquedas fuzzy, índices context y gestión documental con Oracle 9i