| Materialized View with Context Index [message #686406] |
Fri, 02 September 2022 14:29  |
Duane
Messages: 581
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  |
 |
Barbara Boehmer
Messages: 9104
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
