Re: materialized view rewrite

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Mon, 28 Aug 2006 18:30:25 +0200
Message-ID: <44f31a30$0$10153$9b4e6d93@newsspool1.arcor-online.net>

Volker Hetzer schrieb:
> Maxim Demenko schrieb:

>> If you omit "next sysdate + 1/144" part of refresh clause, view will 
>> be created. You can then manually setup job to refresh it automatically.

> Unfortunately, the problem is not the refresh, the problem is that oracle
> will not create the view if I set the "enable query rewrite" clause.
> It complains about dxdb_cmsattrib but I don't see anything to complain
> about.
>
> Lots of Greetings!
> Volker

sqlplus cms/cms

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 28 18:27:43 2006

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

cms_at_ORA102> ed a

cms_at_ORA102> set echo on
cms_at_ORA102> select * from v$version

2 /

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Linux: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production

cms_at_ORA102> @a
cms_at_ORA102> drop table dxdb_cmsattrib;
drop table dxdb_cmsattrib

*
ERROR at line 1:
ORA-00942: table or view does not exist

cms_at_ORA102> CREATE TABLE dxdb_cmsattrib (

2 fsc_snr VARCHAR2(128) CONSTRAINT NN_dxdb_cmsattrib_fsc_snr NOT NULL,

   3      vorzug VARCHAR2(1) CONSTRAINT NN_dxdb_cmsattrib_vorzug NOT NULL,
   4      zulassung VARCHAR2(128) CONSTRAINT NN_dxdb_cmsattrib_zulassung 
NOT NULL,
   5      rohs VARCHAR2(128) CONSTRAINT NN_dxdb_cmsattrib_rohs NOT NULL,
   6      CONSTRAINT PK_dxdb_cmsattrib PRIMARY KEY (fsc_snr)

7 );

Table created.

cms_at_ORA102>
cms_at_ORA102> drop table dxdb_cms_vorzug_enum; drop table dxdb_cms_vorzug_enum

*
ERROR at line 1:
ORA-00942: table or view does not exist

cms_at_ORA102> CREATE TABLE dxdb_cms_vorzug_enum (

   2      Textstring VARCHAR2(1) CONSTRAINT NN_cmsvorz_Textstring NOT NULL,
   3      Value NUMBER(2),
   4      CONSTRAINT PK_dxdb_cms_vorzug_enum PRIMARY KEY (Textstring)

5 );

Table created.

cms_at_ORA102>
cms_at_ORA102> drop table dxdb_cms_zulassung_enum; drop table dxdb_cms_zulassung_enum

*
ERROR at line 1:
ORA-00942: table or view does not exist

cms_at_ORA102> CREATE TABLE dxdb_cms_zulassung_enum (

   2      Textstring VARCHAR2(128) CONSTRAINT NN_cmszul_Textstring NOT NULL,
   3      Value NUMBER(2),
   4      CONSTRAINT PK_dxdb_cms_zulassung_enum PRIMARY KEY (Textstring)

5 );

Table created.

cms_at_ORA102>
cms_at_ORA102> drop table dxdb_cms_rohs_enum; drop table dxdb_cms_rohs_enum

*
ERROR at line 1:
ORA-00942: table or view does not exist

cms_at_ORA102> CREATE TABLE dxdb_cms_rohs_enum (

2 Textstring VARCHAR2(128) CONSTRAINT NN_cmsrohs_Textstring NOT NULL,

   3      Value NUMBER(2),
   4      CONSTRAINT PK_dxdb_cms_rohs_enum PRIMARY KEY (Textstring)

5 );

Table created.

cms_at_ORA102>
cms_at_ORA102> drop materialized VIEW dxdb_cms_attvalues_MV; drop materialized VIEW dxdb_cms_attvalues_MV *
ERROR at line 1:
ORA-12003: materialized view "CMS"."DXDB_CMS_ATTVALUES_MV" does not exist

cms_at_ORA102> CREATE materialized VIEW dxdb_cms_attvalues_MV

   2      build immediate refresh complete
   3      enable query rewrite
   4      AS
   5      select
   6              fsc_snr,
   7              dxdb_cms_vorzug_enum.value vorzugvalue,
   8              dxdb_cms_zulassung_enum.value zulassungvalue,
   9              dxdb_cms_rohs_enum.value rohsvalue
  10          from
  11              dxdb_cmsattrib,
  12              dxdb_cms_vorzug_enum,
  13              dxdb_cms_zulassung_enum,
  14              dxdb_cms_rohs_enum
  15          where
  16                  dxdb_cmsattrib.vorzug=dxdb_cms_vorzug_enum.textstring
  17              and 
dxdb_cmsattrib.zulassung=dxdb_cms_zulassung_enum.textstring
  18              and dxdb_cmsattrib.rohs=dxdb_cms_rohs_enum.textstring
  19      ;

Materialized view created.

cms_at_ORA102>

Best regards

Maxim Received on Mon Aug 28 2006 - 11:30:25 CDT