Problems with Materialized View

From: Peter Kroiss <pk_at_faw.uni-linz.ac.at>
Date: Wed, 6 Feb 2002 11:08:33 +0100
Message-ID: <3c60fe21@news.uni-linz.ac.at>

I'm using Materialized views to speed up my application, the data of the base tables are updated frequently,
the materialized view is refreshed every hour. The materialized view is on a prebuild table
When I refresh the materialized view fast updates are not refreshed in the materialized, when I makre a complete refresh the view is empty during fresh time.

Any Idea?

Peter

create table OBJECTSEARCHTABLE_CNT_WL (

TISOBJECTIDUP                  VARCHAR2(16) NOT NULL ,
FOLDERTYPEID                            VARCHAR2(16),
CLASSIFICATIONTYPEIDDOWN        VARCHAR2(16)  NOT NULL,
CATEGORYTYPEIDDOWN                      VARCHAR2(16),
REGISTEREDFLAG                 CHAR(1) NOT NULL ,
RELEVANTLETTER                          CHAR(1),
LANGUAGEID                     VARCHAR2(2) NOT NULL ,
LOCKEDFLAG                              CHAR(1),
SUPPLIERLOCKEDFLAG                      CHAR(1),
LETTERCOUNT                             NUMBER

) PCTFREE 0 PCTUSED 99
  STORAGE (INITIAL 10M NEXT 1M PCTINCREASE 0); CREATE MATERIALIZED VIEW LOG ON tisobjectnamelanguage with rowid
(TISOBJECTID,

languageid,
relevantletter) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON OBJECTSEARCHTABLE with rowid
(TISOBJECTIDUP,

FOLDERTYPEID,
TISOBJECTIDDOWN,
CLASSIFICATIONTYPEIDDOWN,
CATEGORYTYPEIDDOWN,
REGISTEREDFLAG,
DIRECTASSOCIATIONFLAG,
LOCKEDFLAG,
SUPPLIERLOCKEDFLAG) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW OBJECTSEARCHTABLE_CNT_WL  ON PREBUILT TABLE WITH REDUCED PRECISION   REFRESH FAST
  AS
  SELECT tisobjectidup, foldertypeid, classificationtypeiddown, categorytypeiddown, REGISTEREDFLAG, relevantletter, languageid, lockedflag, supplierlockedflag, COUNT(*) AS lettercount   from tisobjectnamelanguage n, objectsearchtable o WHERE o.tisobjectiddown = n.tisobjectid group by tisobjectidup, foldertypeid, classificationtypeiddown, categorytypeiddown, REGISTEREDFLAG, relevantletter, languageid, lockedflag, supplierlockedflag;

EXECUTE DBMS_MVIEW.REFRESH('OBJECTSEARCHTABLE_CNT_WL', 'F', '', TRUE, FALSE, 0,0,0, TRUE);
OR
EXECUTE DBMS_MVIEW.REFRESH('OBJECTSEARCHTABLE_CNT_WL', 'C', '', TRUE, FALSE, 0,0,0, TRUE); Received on Wed Feb 06 2002 - 04:08:33 CST