title: ORACLE: force like to use index?
body: <p>I have a table that has among others, two columns</p>
  
  <pre><code>SPONSOR_ID     number
  NR_CERTIFICATE char(11)
  </code></pre>
  
  <p>And there's a index for these two columns</p>
  
  <pre><code>NR_CERTIFICATE + SPONSOR_ID
  </code></pre>
  
  <p>This table has hundreds of thousands of rows, and the problem is, when we filter by a specific certificate, the select takes nanoseconds as expected:</p>
  
  <pre><code>SELECT * FROM TBL_XXX WHERE NR_CERTIFICATE = '33300123456'
  </code></pre>
  
  <p>the problem is when we use LIKE to filter:</p>
  
  <pre><code>SELECT * FROM TBL_XXX WHERE NR_CERTIFICATE LIKE '%123456'
  </code></pre>
  
  <p>Then the select takes around 15 seconds.</p>
  
  <p>I know that in this case, the index is discarded and a table scan takes place. Is there a way to force ORACLE to use the index in this case? Or is there a way to speed up this select?</p>