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

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://www.dba-oracle.com/oracle_tips_like_sql_index.htm

*****************************************

Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf