Any way to control materialized view staleness?

From: "Tao Zuo" <Tao_Zuo@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 7 Jun 2004 12:15:38 -0400

Hi All,


I create a materialized view, and expect its status has no change if qualified 
data has no change. I made a test case, which shows to me that mview status 
turned to stale even the qualified data (actually data stored in mview) has no 
change. Now my question is: is there any way we can control the mview staleness 
to indicate us that we don't need to refresh such mview?

Here is my case:
mview:
CREATE MATERIALIZED VIEW SFTDIMC
BUILD IMMEDIATE 
USING INDEX 
REFRESH FORCE 
ON DEMAND 
AS 
SELECT s.ParentCode as INDUSTRY, 
t.ParentCode i, u.ParentCode CATEGORYGROUP, v.ParentCode 
CATEGORY,
v.ChildCode SUBCATEGORY, s.DisplayOrder i_ORDER, 
t.DisplayOrder CATEGORYGROUP_ORDER, u.DisplayOrder CATEGORY_ORDER, 
v.DisplayOrder SUBCATEGORY_ORDER 
FROM sub s, sub t, sub u, sub v 
WHERE s.SubCodeSet = 'sft_a_industry_i' AND 
t.SubCodeSet = 'sft_a_i_catgroup' AND 
t.ParentCode = s.ChildCode AND 
u.SubCodeSet = 'sft_a_cg_c' AND 
u.ParentCode = t.ChildCode AND 
v.SubCodeSet = 'sft_a_c_sc' AND
v.ParentCode = u.ChildCode;

no I made a change on sub:

update sub
set description='x'
where subcodeset='ftw';
commit;

now the mview: SFTDIMC staleness is stale. Per its definition, 
column "description" is not anticipating in the mview, and 
subcodeset='ftw' is not in the mview any way, we expect 
that we should not need to refresh the mview, but user_mview.staleness indicate 
so.
Is there any way we can make oracle tell us that such a mview is not need to be 
refreshed?
We will have a meeting on this tomorrow, so your help on this is very
appreciated.

Thanks a lot.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Any way to control materialized view staleness?

Other related posts: