SQL Oracle DBMS_LOB.INSTR 和 CONTAINS 性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9481573/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle DBMS_LOB.INSTR and CONTAINS performance
提问by Petr Mensik
Is there any performance difference between dbms_lob.instr and contains or am I doing something wrong?
dbms_lob.instr 和 contains 之间是否有任何性能差异，或者我做错了什么？
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
最后两行更改为使用 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.
我的问题是我想使用 instr 而不是 contains，但在我看来 instr 比 contains 慢很多。
回答by Justin Cave
CONTAINSwill use an Oracle Text index so you'd expect it to be much more efficient than something like INSTRthat 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.
CONTAINS将使用 Oracle Text 索引，因此您希望它比INSTR在运行时读取整个 CLOB 的方法更有效。如果您为这两个语句生成查询计划，我希望您会看到差异与 Oracle Text 索引有关。
Why do you want to use INSTRrather than CONTAINS?
为什么要使用INSTR而不是CONTAINS？
