0

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.

Petr Mensik
  • 26,874
  • 17
  • 90
  • 115

1 Answers1

3

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?

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Because I have a index on my table which is supposed to be working(I mean it's running in a live environment and query from app with CONTAINS is executed correctly), but now I need to make some change in search string and the query(which is the same) you can see above is not working. So I tried to fix it with dbms_lob.instr, but now I see that is not a good solution. – Petr Mensik Feb 28 '12 at 15:59
  • @PetrMensik - I'm not sure what "not working" means in this context. Are you getting an error? If so, what error? Are you getting incorrect results? If so, describe the incorrect results. Or is it just a performance problem? If it is a performance problem, why don't you just change the `CONTAINS` query? – Justin Cave Feb 28 '12 at 16:03
  • I am sorry, I took a vacation until Friday, so I will tell you the precise text of exception later(so yes, I am getting an error). Thanks for help – Petr Mensik Feb 29 '12 at 20:12
  • I posted a question regarding that error here http://stackoverflow.com/questions/9531145/contains-doesnt-work-with-oracle-text – Petr Mensik Mar 02 '12 at 09:53