4

I have problem with Oracle DB - domain index returns zero rows after search by CONTAINS() on materialized view. I see that materialized view is filled with data and I also used procedure ctx_ddl.sync_index() for domain index synchronization.

What works good:

  • CREATE TABLE
  • INSERT DATA
  • CREATE DOMAIN INDEX
  • SYNC DOMAIN INDEX
  • FIND ROWS BY CONTAINS - RETURN ROWS

What is not working:

  • CREATE TABLE
  • INSERT DATA
  • CREATE MATERIALIZED VIEW
  • REFRESH MATERIALIZED VIEW
  • CREATE DOMAIN INDEX IN MATERIALIZED VIEW
  • SYNC DOMAIN INDEX IN MATERIALIZED VIEW
  • FIND ROWS BY CONTAINS IN MATERIALIZED VIEW - RETURN ZERO ROWS (LIKE %TERM% WORKS)

Why everything works fine without materialized view? Here is my queries (you can copy-paste and try it in your oracle db):

--create table CREATE TABLE "PCOUNTERPARTY" ( "ID_COUNTERPARTY" NUMBER(10,0), "TXT_SEARCH_FULL_NAME" NVARCHAR2(260), CONSTRAINT "PCOUNTERPARTY_PK" PRIMARY KEY ("ID_COUNTERPARTY"));

--INSERT DATA. Insert into PCOUNTERPARTY (ID_COUNTERPARTY,TXT_SEARCH_FULL_NAME) values (1184,'MARTINKO3');

--create materialized view CREATE MATERIALIZED VIEW m_pcounterparty AS SELECT c.ID_COUNTERPARTY, CAST( c.TXT_SEARCH_FULL_NAME AS varchar2(260 CHAR) ) as txt_search_full_name_all FROM PCOUNTERPARTY c;

--create domain index create index IDXM_1_pcounterparty on m_pcounterparty(TXT_SEARCH_FULL_NAME_ALL) indextype is ctxsys.context PARAMETERS ('SYNC ( ON COMMIT)');

--refresh of materialized view EXECUTE DBMS_MVIEW.REFRESH('M_PCOUNTERPARTY'); --refresh of index exec ctx_ddl.sync_index('IDXM_1_pcounterparty');

--search in materialized view SELECT TXT_SEARCH_FULL_NAME_ALL from M_PCOUNTERPARTY c WHERE CONTAINS(c.TXT_SEARCH_FULL_NAME_ALL, 'martin', 1) > 0; --return ZERO and THIS IS PROBLEM --c.TXT_SEARCH_FULL_NAME_ALL LIKE '%MARTIN%'; -- return rows but we want search thru CONTAINS

Martin
  • 61
  • 3

1 Answers1

0

Oracle Text indexes normally search words, not strings.

A wildcard is not necessary to search for "martin" in "Martin Luther King Jr." But a wildcard is necessary to search for "martin" in "MARTINKO3".

Change the CONTAINS predicate to this:

CONTAINS(c.TXT_SEARCH_FULL_NAME_ALL, 'martin%', 1) > 0;

I ran tests on Oracle 12.2 and could not find any differences in behavior between using a table or a materialized view. Perhaps there was a test mistake or a very specific bug that caused the text indexes to act differently on your system.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132