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
One obscure trick for indexing queries with a leading wildcard character (like '%SON') is to create a REVERSE index and them programmatically reverse the SQL like clause to read "like 'NOS%'", effectively indexing on the other side of the text, clumsy, yet effective.
Read more about indexing on Oracle text based searches here:
http://oracle-tips.c.topica.com/maalZ3vabItU3bLGJrib/
Oracle LIKE clause searches with text indexes
