Any way to control materialized view staleness?

From: Tao Zuo <Tao_Zuo_at_npd.com>
Date: Mon, 7 Jun 2004 12:15:38 -0400
Message-ID: <E52FC22CD585744F968349C74D10E0A8B991@W2EMLVS1.npd.com>

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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Mon Jun 07 2004 - 11:12:50 CDT