Oracle DBMS_LOB.INSTR和CONTAINS性能

问题描述

dbms_lob.instr与包含之间是否存在性能差异?或者我做错了什么?

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

这是我的代码

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

最后两行更改为使用instr

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

我的问题是我想使用instr而不是contains，但是在我看来，instr会比contains慢很多.

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

推荐答案

CONTAINS将使用Oracle Text索引，因此您希望它比像INSTR这样必须读取整个CLOB的文件效率更高.在运行时.如果您为这两个语句生成查询计划，那么我希望您会发现差异与Oracle Text索引有关.

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.

为什么要使用INSTR而不是CONTAINS?

这篇关于Oracle DBMS_LOB.INSTR和CONTAINS性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！