title: Oracle DBMS_LOB.INSTR and CONTAINS performance
body: <p>Is there any performance difference between dbms_lob.instr and contains or am I doing something wrong?</p>
  
  <p>Here is my code</p>
  
  <pre><code>SELECT DISTINCT ha.HRE_A_ID, ha.HRE_A_FIRSTNAME, ha.HRE_A_SURNAME, ha.HRE_A_CITY,  
  ha.HRE_A_EMAIL, ha.HRE_A_PHONE_MOBIL
  FROM HRE_APPLICANT ha WHERE ha.HRE_A_STATUS_ID=1 AND ha.HRE_A_CURRENT_STATUS_ID &lt;= '7' 
  AND ((DBMS_LOB.INSTR(hre_a_for_search,'java') &gt; 0) 
  OR EXISTS 
  (SELECT 1 FROM gob_attachment, gob_table WHERE hre_a_id=gob_a_record_id 
  AND gob_a_table_id = gob_t_id AND gob_t_code = 'HRE_APPLICANT' 
  AND CONTAINS (gob_a_document, 'java') &gt; 0))
  ORDER BY HRE_A_SURNAME
  </code></pre>
  
  <p>and last two lines changed for using instr</p>
  
  <pre><code>AND dbms_lob.instr(gob_a_document,utl_raw.cast_to_raw('java')) &lt;&gt; 0))
  ORDER BY HRE_A_SURNAME
  </code></pre>
  
  <p>My problem is that I would like to use instr instead of contains, but instr seems to me a lot slower then contains.</p>