Skip to Main Content
  • Questions
  • Oracle Text Search Index on Materialized View

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Saurabh.

Asked: July 15, 2022 - 4:17 am UTC

Last updated: August 04, 2022 - 1:56 pm UTC

Version: 19C

Viewed 10K+ times! This question is

You Asked

I have two tables viz. master and trans on which the text search has to be incorporated. The record needs to come from both master and trans and if record is present in both then it has to be picked only from trans. To achieve this I create a materialized view on the two tables using the below query

SELECT RECORD_ID, ROW_ID, 'MSTR' SOURCE FROM MASTER_TABLE M, TRANS_TABLE T WHERE M.RECORD_ID (+) = T.RECORD_ID AND T.RECORD_ID IS NULL
UNION ALL
SELECT RECORD_ID, ROW_ID, 'TRNS' SOURCE FROM TRANS_TABLE T

The domain index query
CREATE INDEX MY_INDEX ON MATERIALIZED_VIEW(RECORD_ID) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('ASYNCHRONOUS UPDATE DATASTORE MY_DATASTORE TRANSACTIONAL SYNC(ON COMMIT)')

When the materialized view is created with the option "REFRESH FAST ON COMMIT", any changes in underlying master and table is not updated in the index.
Therefore I had to create the materialized view the options "REFRESH COMPLETE ON COMMIT" and it works.

I still want to do use the option "REFRESH FAST ON COMMIT" and index to work.

and Connor said...

Take a look at this post

https://connor-mcdonald.com/2022/01/31/silent-data-corruption-constraints-spanning-multiple-rows/

which walks through how you might get silent errors in a fast refresh and how to diagnose/remedy them

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Saurabh Nainwal, July 18, 2022 - 12:38 pm UTC

Thanks Connor for a quick response. However, I would like to mention that any changes to underlying tables (both the Trans and Master) are reflected in the materialized view with FAST REFRESH ON COMMIT option. However, the same change is not coming up in the text search query.
Connor McDonald
July 26, 2022 - 7:15 am UTC

In that case, we'll need a test case that we can run here

sample script for replicating issue

Saurabh Nainwal, July 27, 2022 - 3:48 pm UTC

CREATE TABLE T (KEY NUMBER, VAL VARCHAR2(25), CONSTRAINT PK_T PRIMARY_KEY(KEY));
CREATE TABLE M (KEY NUMBER, VAL VARCHAR2(25), CONSTRAINT PK_M PRIMARY_KEY(KEY));

INSERT INTO M (KEY, VAL) VALUES (1,'A');
INSERT INTO M (KEY, VAL) VALUES (2,'B');
INSERT INTO M (KEY, VAL) VALUES (3,'C');
INSERT INTO M (KEY, VAL) VALUES (4,'D');
INSERT INTO T (KEY, VAL) VALUES (1,'E');
INSERT INTO T (KEY, VAL) VALUES (2,'F');
INSERT INTO T (KEY, VAL) VALUES (5,'G');
INSERT INTO T (KEY, VAL) VALUES (6,'H');
COMMIT;

CREATE MATERIALZED VIEW LOG ON T WITH ROWID;
CREATE MATERIALZED VIEW LOG ON M WITH ROWID;

CREATE MATERIALIZED VIEW MY_MV
REFRESH FAST ON COMMIT WITH ROWID
AS(
SELECT M.KEY, M.ROWID ROW_ID, 'M' SOURCE, T.ROWID DO_NOT_USE FROM M ,T WHERE T.KEY(+)=M.KEY
AND T.KEY IS NULL
UNION ALL
SELECT KEY, ROWID ROW_ID , 'T' SOURCE, NULL DO_NOT_USE FROM T);

CREATE OR REPLACE PROCEDURE DS_PROC(I_ROWID IN ROWID, IO_TEXT IN OUT NOCOPY CLOB)
AS
V_MSTR VARCHAR2(1000);
V_TRNS VARCHAR2(1000);
V_SQL VARCHAR2(100);
V_ID NUMBER;
V_SOURCE CHAR(1);
BEGIN

V_SQL := 'SELECT KEY, SOURCE FROM MY_MV WHERE ROWID = :1';
EXECUTE IMMEDIATE V_SQL INTO V_ID, V_SORUCE USING ROWID;

IF(V_SOURCE = 'M') THEN
EXECUTE IMMEDIATE 'SELECT XMLELEMENT("M",XMLFOREST(KEY,VAL)).GETCLOBVAL() FROM M WHERE KEY = :1' INTO IO_TEXT USING V_ID;
ELSE
EXECUTE IMMEDIATE 'SELECT XMLELEMENT("T",XMLFOREST(KEY,VAL)).GETCLOBVAL() FROM T WHERE KEY = :1' INTO IO_TEXT USING V_ID;
END IF;

END DS_PROC;
CTX_DDL.CREATE_SECTION_GROUP('MY_SECTION_GRP','AUTO_SECTION_GROUP');
CREATE INDEX MY_INDEX ON MY_MV(KEY) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('ASYNCHRONOUS UPDATE DATASTORE DS_PROC SECTION GROUP MY_SECTION_GRP
TRANSACTIONAL SYNC(ON COMMIT)');

UPDATE T SET VAL='TEST' WHERE KEY = 1;

SELECT * FROM MY_MV WHERE CONTAINS(KEY,'%TEST%') > 0;

Chris Saxon
July 28, 2022 - 2:08 pm UTC

There are many errors in that script e.g. misspelling MATERIALIZED. Please provide a working test case.

Saurabh Nainwal, July 30, 2022 - 5:02 am UTC

Apologies for the typos. Corrected the script

CREATE TABLE T (KEY NUMBER,
VAL VARCHAR2(25),
PRIMARY KEY (KEY));

CREATE TABLE M (KEY NUMBER,
VAL VARCHAR2(25),
PRIMARY KEY (KEY));

INSERT INTO M (KEY, VAL) VALUES (1,'A');
INSERT INTO M (KEY, VAL) VALUES (2,'B');
INSERT INTO M (KEY, VAL) VALUES (3,'C');
INSERT INTO M (KEY, VAL) VALUES (4,'D');
INSERT INTO T (KEY, VAL) VALUES (1,'E');
INSERT INTO T (KEY, VAL) VALUES (2,'F');
INSERT INTO T (KEY, VAL) VALUES (5,'G');
INSERT INTO T (KEY, VAL) VALUES (6,'H');
COMMIT;

CREATE MATERIALIZED VIEW LOG ON T WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON M WITH ROWID;

CREATE MATERIALIZED VIEW MY_MV
REFRESH FAST ON COMMIT WITH ROWID
AS(
SELECT M.KEY, M.ROWID ROW_ID, 'M' SOURCE, T.ROWID DO_NOT_USE FROM M ,T WHERE T.KEY(+)=M.KEY
AND T.KEY IS NULL
UNION ALL
SELECT KEY, ROWID ROW_ID , 'T' SOURCE, NULL DO_NOT_USE FROM T);

CREATE OR REPLACE PROCEDURE DS_PROC(I_ROWID IN ROWID, IO_TEXT IN OUT NOCOPY CLOB)
AS
V_MSTR VARCHAR2(1000);
V_TRNS VARCHAR2(1000);
V_SQL VARCHAR2(100);
V_ID NUMBER;
V_SOURCE CHAR(1);
BEGIN

V_SQL := 'SELECT KEY, SOURCE FROM MY_MV WHERE ROWID = :1';
EXECUTE IMMEDIATE V_SQL INTO V_ID, V_SOURCE USING I_ROWID;

IF(V_SOURCE = 'M') THEN
EXECUTE IMMEDIATE 'SELECT XMLELEMENT("M",XMLFOREST(KEY,VAL)).GETCLOBVAL() FROM M WHERE KEY = :1' INTO IO_TEXT USING V_ID;
ELSE
EXECUTE IMMEDIATE 'SELECT XMLELEMENT("T",XMLFOREST(KEY,VAL)).GETCLOBVAL() FROM T WHERE KEY = :1' INTO IO_TEXT USING V_ID;
END IF;
END DS_PROC;
/

EXEC CTX_DDL.CREATE_SECTION_GROUP('MY_SECTION_GRP','AUTO_SECTION_GROUP');
CREATE INDEX MY_INDEX ON MY_MV(KEY) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE DS_PROC SECTION GROUP MY_SECTION_GRP TRANSACTIONAL SYNC(ON COMMIT)');

--Test Scenario
UPDATE T SET VALUE = 'TEST' WHERE KEY = 1;
COMMIT;

SELECT * FROM MY_MV WHERE CONTAINS (KEY, '%test%') > 0;

Chris Saxon
August 01, 2022 - 4:30 pm UTC

Why are you trying to create a text index on KEY? That stores numeric values. Surely you want the index on VAL (and to add this to the MV)?

A reader, August 01, 2022 - 5:00 pm UTC

yes that is right. Number type of column can not have domain index. It should be value column in this sample example. In my actual code it is a varchar(2) type column.

A reader, August 03, 2022 - 6:37 am UTC

I have realized my mistake. If you see the materialized view it has columns like ID, Source (which indicates whether the record is from Master(M) or Trans (T) table, and rowid). If I am doing updates to a record in Trans table there is nothing which is actually changing in MV. For example a record with id = 1 and source as 'T' in MV and if I update this record 100 times in the TRANS table nothing is changing in MV and it is still as id = 1 and source = 'T' and that is the reason index is not refreshing. I have included another column in MV from both the underlying table which is changing with every DML operation on underlying table and created an index on it and it works like a charm.

However I have another question, If I use "ASYNCHRONOUS_UPDATE" in index params, the index is not refreshing. I am not sure how this ASYNCHRONOUS_UPDATE works. My assumption is that like a SYNCHRONOUS_UPDATE the ASYNCHRONOUS_UPDATE starts the index refresh on a change but but it does it using a different thread. Correct me if I am wrong.

Chris Saxon
August 03, 2022 - 12:52 pm UTC

Glad you figured it out.

I'm not too familiar with Oracle Text, here's what the docs have to say about async update:

In releases prior to Oracle Database 12c Release 2 (12.2), when there is an update to the column on which an Oracle Text index is based, the document is unavailable for search operations until the index is synchronized. User queries cannot perform a search of this document. Starting with Oracle Database 12c Release 2 (12.2), you can specify that documents must be searchable after updates, without immediately performing index synchronization. Before the index is synchronized, queries use the old index entries to fetch the contents of the old document. After index synchronization, user queries fetch the contents of the updated document.

The ASYNCHRONOUS_UPDATE option for indexes enables you to retain the old contents of a document after an update and then use this index to answer user queries.

A reader, August 03, 2022 - 1:38 pm UTC

what is the default duration for oracle text search to synchronize the index if ASYNCHRONOUS_UPDATE is used? Can we override this default value?
Chris Saxon
August 04, 2022 - 1:56 pm UTC

What do you mean by default duration? How long the sync takes? This will depend on how much data you're changing.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.