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