Oracle текстовый поиск по нескольким таблицам и объединениям

Question

У меня есть следующий оператор 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

Я создал индекс на employee_details стол для emp_name столбец, однако я не могу соединиться с v_depts для поиска, потому что я изменяю свой оператор SQL как

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

хотя я создал индекс для столбца emp_name в employee_details Таблица. Как я могу решить эту проблему?

Индекс заявления для emp_name

CREATE INDEX IDX_EMP_DETAILS ON EMPLOYEE_DETAILS(EMP_NAME)INDEXTYPE IS CTXSYS.CTXCAT

Answer 1

Я обычно решаю полнотекстовый поиск по нескольким столбцам в разных таблицах, материализуя их структурированное представление XML, а затем создавая индекс по всему XML.

Это решение является общим и также дает вам свободу при поиске: весь вид или только подпуть. Недостатком является управление обновлением MV, который обычно не может быть обновлен быстро; но обновление полнотекстового индекса обычно происходит не в режиме реального времени, поэтому вы можете просто запланировать его.

-- 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;