We're using Oracle 11g database.
As you may or may not know, if you use wildcard query with "%" in front of the string, the column index is not being used and a full table scan is happening.
It looks like there isn't a definitive suggestion on how to improve this kind of query, but perhaps you could share some valuable information from your experience on how to optimize the following query:
WHERE UPPER(CustomerName) like '%ABC%'
OR UPPER(IndemnifierOneName) like '%ABC%'
OR UPPER(IndemnifierTwoName) like '%ABC%';
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 data and 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