Home » SQL & PL/SQL » SQL & PL/SQL » nested materialized view (10.2, Window 2003)
nested materialized view [message #354676] Mon, 20 October 2008 14:30
Messages: 49
Registered: October 2007
Location: New York
I've created a nested materialized view that depends on 4 other materialized views and I'm having trouble getting it to automatically refresh.

One of the underlying materialized views is refreshed on commit - so is always fresh.
The other views are too complex for a refresh on commit. I've put them into a refresh group and tried to synch them all to refresh hourly at the same time.

The underlying views do refresh automatically at the time I've specified. But the nested view's refresh is somewhat erratic.
I've read that there is a property 'nested = true' that can be set on the nested view to help synch it with the views it depends on. But I've not been able to discover how to set it.

Please help by providing me with the SQLPlus/PL/SQL script you would use to set the required properties.

The views were created on one of my streamss database
The nested view is glprod.c_mv_contrib206070.
The underlying views that refresh hourly are name glprod.c_mv_wosodl207060, glprod.empcost, and glprod.0_cost21

Here is what I am attempting to do---

EXEC dbms_mview.refresh('c_mv_contrib20607', nested=>TRUE);
EXEC dbms_mview.refresh_dependent('dl207060', nested=>TRUE);
EXEC dbms_mview.refresh_dependent('empcos', nested=>TRUE);
EXEC dbms_mview.refresh_dependent('0_cost21', nested=>TRUE);
Previous Topic: SQl insert
Next Topic: Date conversion using a table
Goto Forum:

Current Time: Wed Jul 26 10:05:13 CDT 2017

Total time taken to generate the page: 0.06576 seconds