Oracle DBMS_LOB.INSTR and CONTAINS performance

Question

Is there any performance difference between dbms_lob.instr and contains or am I doing something wrong?

Here is my 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 <= '7' 
AND ((DBMS_LOB.INSTR(hre_a_for_search,'java') > 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') > 0))
ORDER BY HRE_A_SURNAME

and last two lines changed for using instr

AND dbms_lob.instr(gob_a_document,utl_raw.cast_to_raw('java')) <> 0))
ORDER BY HRE_A_SURNAME

My problem is that I would like to use instr instead of contains, but instr seems to me a lot slower then contains.

Answer 1

CONTAINS will use an Oracle Text index so you'd expect it to be much more efficient than something like INSTR that has to read the entire CLOB at runtime. If you generate the query plans for the two statements, I expect that you'll see that the difference is related to the Oracle Text index.

Why do you want to use INSTR rather than CONTAINS?