Home » Server Options » Text & interMedia » Materialized View with Context Index (Oracle, 12.2, Unix)
Materialized View with Context Index [message #686406] Fri, 02 September 2022 14:29 Go to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
If I have a Materialized View with a Context Index, is there anyway to refresh/sync that index when the Materialized View is refreshed without running a Package/Procedure or an Oracle Scheduled job? Is there anyway to do this without something else running?

Maybe a trigger?



CREATE MATERIALIZED VIEW DIRECTORY_MV 
    (ID,
     FIRST_NAME,
     LAST_NAME,
     SEARCH_FIELD)
as
(select ..... from ....)

CREATE INDEX SEARCH_FIELD_MV_INDX ON DIRECTORY_MV
(SEARCH_FIELD, LAST_NAME, FIRST_NAME)
INDEXTYPE IS CTXSYS.CONTEXT;


Somehow run these two statements when the Materialized View is refreshed without a Package or an Oracle Scheduled Job.


ctx_ddl.sync_index('search_field_mv_indx');
ctx_ddl.optimize_index('search_field_mv_indx', 'FULL');

Re: Materialized View with Context Index [message #686831 is a reply to message #686406] Mon, 16 January 2023 13:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8955
Registered: November 2002
Location: California, USA
Senior Member
You can use refresh on commit in your create or alter of your materialized view and you can use sync(on commit) in your create or alter of your context index. You can optimize from a trigger if you use pragama_autonomous transaction. However, this may not be the best method. I have provided a demonstration below that shows no results before commit and results after commit using these methods.

SCOTT@orcl_12.1.0.2.0> CREATE MATERIALIZED VIEW LOG ON emp
  2  /

Materialized view log created.

SCOTT@orcl_12.1.0.2.0> CREATE MATERIALIZED VIEW DIRECTORY_MV
  2  	  (ID,
  3  	  FIRST_NAME,
  4  	  LAST_NAME,
  5  	  SEARCH_FIELD)
  6    REFRESH ON COMMIT
  7  as
  8  (select empno, deptno, ename, job from emp)
  9  /

Materialized view created.

SCOTT@orcl_12.1.0.2.0> CREATE INDEX SEARCH_FIELD_MV_INDX ON DIRECTORY_MV (SEARCH_FIELD) INDEXTYPE IS CTXSYS.CONTEXT
  2    PARAMETERS ('SYNC (ON COMMIT)')
  3  /

Index created.

SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE TRIGGER directory_mv_trig
  2    AFTER INSERT OR UPDATE OR DELETE OF search_field ON directory_mv
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    ctx_ddl.optimize_index('search_field_mv_indx', 'FULL');
  7  END directory_mv_trig;
  8  /

Trigger created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> INSERT INTO emp (empno, deptno, ename, job)
  2  VALUES (1, 2, 'BOEHMER', 'DEVELOPER')
  3  /

1 row created.

SCOTT@orcl_12.1.0.2.0> -- no results before commit:
SCOTT@orcl_12.1.0.2.0> SELECT first_name, last_name FROM directory_mv WHERE CONTAINS (search_field, 'DEVELOPER') > 0
  2  /

no rows selected

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> -- results after commit that triggers synchronization and optimization
SCOTT@orcl_12.1.0.2.0> SELECT * FROM directory_mv WHERE CONTAINS (search_field, 'DEVELOPER') > 0
  2  /

        ID FIRST_NAME LAST_NAME  SEARCH_FI
---------- ---------- ---------- ---------
         1          2 BOEHMER    DEVELOPER

1 row selected.

Re: Materialized View with Context Index [message #686832 is a reply to message #686831] Mon, 16 January 2023 13:46 Go to previous message
Duane
Messages: 508
Registered: December 2002
Senior Member
Perfect. Thank you. I wasn't aware of the sync(on commit) parameter. This solves my problem.
Previous Topic: Oracle Text Search - Handling special characters and blank search term
Next Topic: Oracle Text Search - How to drop DR$ tables and indexes
Goto Forum:
  


Current Time: Thu Feb 09 04:15:22 CST 2023