title: PL/SQL Performance Tuning for LIKE '%...%' Wildcard Queries
body: <p>We're using Oracle 11g database.<br>
  As you may or may not know, if you use wildcard query with "%" in front of the string, the column <strong>index is not being used</strong> and a <strong>full table scan</strong> is happening. </p>
  
  <p>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:</p>
  
  <pre><code>SELECT * 
    FROM myTable 
   WHERE UPPER(CustomerName) like '%ABC%' 
      OR UPPER(IndemnifierOneName) like '%ABC%' 
      OR UPPER(IndemnifierTwoName) like '%ABC%';
  </code></pre>
  
  <p>...where all 3 columns are of type <strong>varchar2(100)</strong> and <strong>ABC</strong> is a value of variable input parameter.</p>
  
  <p>@All suggesting <em>CONTEX</em> index, please note my data gets updated any time of the day every day and this index requires re-syncing, hence it's <strong>not</strong> a good option for a table of <strong>1.5 million rows</strong>, sorry.</p>
  
  <p>P.S. I'll upvote every answer, so please do keep them coming.</p>