Oracle在多个表和联接上进行文本搜索 [英] Oracle text search on multiple tables and joins
问题描述
我有以下SQL语句.
select emp_no,dob,dept_no from v_depts
where catsearch (emp_no,'abc',NULL) > 0
or
catsearch (dept_no,'abc',NULL) > 0
其中v_depts
是视图.
现在,我想添加一个或多个表作为联接,以便可以对列进行文本搜索
例如employee_details
包含员工信息,我可以加入emp_no
Now I would like to add one or more tables as join so that I can do text search on columns
e.g. employee_details
contains employee information and I can join with emp_no
我已经在employee_details
表上为emp_name
列创建了索引,但是由于我将sql语句修改为
I have created index on employee_details
table for emp_name
column, however I am not able to join with v_depts to search because I modify my sql statement as
select a.emp_no,a.dob,a.dept_no from v_depts a left outer join employee_details b
on (a.emp_no = b.emp_no)
where catsearch (a.emp_no,'abc',NULL) > 0
or
catsearch (a.dept_no,'abc',NULL) > 0
or
catsearch (b.emp_name,'abc',NULL) > 0
它给我错误
ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation
DRG-10599: column is not indexed
即使我已经为employee_details
表中的emp_name列创建了索引.我怎么解决这个问题?
even though I have created index for emp_name column in employee_details
table. How can I solve this problem?
emp_name的索引语句
Index statement for emp_name
CREATE INDEX IDX_EMP_DETAILS ON EMPLOYEE_DETAILS(EMP_NAME)INDEXTYPE IS CTXSYS.CTXCAT
推荐答案
我通常通过实例化它们的结构化XML视图,然后在整个XML上创建索引,来解决对不同表上多个列的全文搜索.
I usually solve fulltext searches on multiple columns on different tables by materializing a structured XML view of them and then creating the index on the whole XML.
此解决方案是通用的,还使您可以自由搜索:整个视图或仅一个子路径.缺点是管理通常无法快速刷新的MV的刷新.但是全文索引的更新通常也不是实时的,因此您可以对其进行调度.
This solution is generic and also give you freedom on search: the whole view or only a subpath. The drawback is managing the refresh of a MV who usually cannot be refreshed fast; but update of fulltext index usually isn't in real-time, too, so you can just schedulate it.
-- Crating the view
CREATE MATERIALIZED VIEW fulltext_helper
NOLOGGING
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
a.dob, -- we don't need to fulltext on him
XMLELEMENT(helper,
XMLFOREST(a.emp_no AS emp_no,
a.dept_no AS dept_no,
b.emp_name AS emp_name)
) AS indexme
FROM v_depts a
LEFT OUTER JOIN employee_details b
ON (a.emp_no = b.emp_no);
-- Creating the index
BEGIN
ctx_ddl.create_preference('fulltext_helper_lexer', 'BASIC_LEXER');
ctx_ddl.create_preference('fulltext_helper_filter', 'NULL_FILTER');
END;
/
CREATE INDEX fulltext_helper_index ON fulltext_helper (indexme)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS (
'DATASTORE CTXSYS.DIRECT_DATASTORE
LEXER fulltext_helper_lexer
FILTER fulltext_helper_filter');
-- Searching the whole data
SELECT * FROM fulltext_helper
WHERE contains(indexme, '{abc} INPATH (/helper)') > 0;
-- Searching only on "empno"
SELECT * FROM fulltext_helper
WHERE contains(indexme, '{abc} INPATH (/helper/emp_no)') > 0;
这篇关于Oracle在多个表和联接上进行文本搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!