I have a problem with Oracle DB - the domain index returns zero rows after searching for CONTAINS () on a materialized view. I see that the materialized view is filled with data, and I also used the ctx_ddl.sync_index () procedure to synchronize the domain index.
What works:
- CREATE TABLE
- INSERT DATA
- CREATE DOMAIN INDEX
- SYNC SEQUENCE INDEX
- FIND CONTENT NETWORKS - ROWS RETURN
What does not work:
- CREATE TABLE
- INSERT DATA
- CREATE A MATERIALIZED VIEW
- VIEWED MATERIALS
- CREATE A DOMAIN INDEX IN THE MATERATED VIEW
- SYNCHRONOUS DOMAIN INDEX IN MATERIAL VIEWING
- FIND NETWORKS CONTAINING IN MATERIALIZED TYPE - ROWS RETURN (HOW% TERM% WORKS)
?
( - 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