From oracle-l-bounce@freelists.org Mon Jun 7 11:12:50 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i57GCOc14334 for ; Mon, 7 Jun 2004 11:12:34 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i57GCE614301 for ; Mon, 7 Jun 2004 11:12:24 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0F57A72C2F6; Mon, 7 Jun 2004 10:58:10 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32428-41; Mon, 7 Jun 2004 10:58:09 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 20B7D72C693; Mon, 7 Jun 2004 10:58:09 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 07 Jun 2004 10:56:40 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 742C272C2A7 for ; Mon, 7 Jun 2004 10:56:40 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30753-96 for ; Mon, 7 Jun 2004 10:56:40 -0500 (EST) Received: from mail43.messagelabs.com (mail43.messagelabs.com [140.174.2.211]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 02B5272C06C for ; Mon, 7 Jun 2004 10:56:40 -0500 (EST) X-VirusChecked: Checked X-Env-Sender: Tao_Zuo@npd.com X-Msg-Ref: server-16.tower-43.messagelabs.com!1086624918!4018735 X-StarScan-Version: 5.2.10; banners=-,-,- X-Originating-IP: [192.81.69.172] Received: (qmail 10521 invoked from network); 7 Jun 2004 16:15:39 -0000 Received: from w2emlec2.npd.com (HELO W2EMLVS1.npd.com) (192.81.69.172) by server-16.tower-43.messagelabs.com with SMTP; 7 Jun 2004 16:15:39 -0000 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: Any way to control materialized view staleness? X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 Date: Mon, 7 Jun 2004 12:15:38 -0400 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Oracle 9.2 changes in INIT Thread-Index: AcRMqPoe1U2/g6toQH+ukgvI4ZkR7QAAA6lQ From: "Tao Zuo" To: X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2135 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Tao_Zuo@npd.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@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 -----------------------------------------------------------------