PL/SQL Performance Tuning for LIKE '%...%' Wildcard Queries

As already mentioned you could add a ctx context index to the name columns.

assuming a small number of records get updated, 1 option is to refresh your index daily. (and record when it happened)

then add a lastupdate date column & index to your table being searched.

It should be possible to scan your ctx index for the majority of the old unchanged dataand select from the small percentage of updated data using the traditonal LIKE e.g:

WHERE (lastupdated<lastrefresh AND contains(name,'%ABC%'))    OR (lastupdated>lastrefresh AND name like '%ABC%')

NOTE: you may find your query plan goes a little mental (lots of bitmap conversions to row ids) in that case split the 2 parts of the OR into a UNION ALL query.e.g

SELECT id FROM mytable       WHERE     (lastupdate>lastrefresh and name LIKE '%ABC%')    UNION ALL    SELECT id FROM mytable       WHERE lastupdate<lastrefresh and CONTAINS(name, '%ABC%', 1) > 0

The only optimization is to not use that type of query and instead use the native capabilities of the database platform:

See Oracle Text: http://www.oracle.com/technetwork/database/enterprise-edition/index-098492.html

The common answer for SQL Server related questions would be Full Text Search.. nice to see Oracle has something as good or better.

The UPPER() is killing your indexes before anything, consider using a regexp. The initial % may avoid a normal index scan, but not always results in a Full Table Scan but into a Full Index Scan, which is faster than the FTS.

I suppose that 'ABC' is variable. If not, a function index is the way to go.