Re: materialized view rewrite

From: Volker Hetzer <firstname.lastname_at_ieee.org>
Date: Tue, 29 Aug 2006 14:32:17 +0200
Message-ID: <ed1c4h$oiq$1@nntp.fujitsu-siemens.com>

G Quesnel schrieb:
> As Maxim showed, it may be related to the Oracle software version you
> are using.
> "query rewrite" I beleive is only available in the Enterprise Edition.
> Can you repeat the query Maxim wrote to show us what version you are
> using (Select * from v$version;)

We've got the enterprise edition on linux. AFAIK the current version is still 10.2.0.1. Here's the spool of the testcase:

SQL> set lines 100
SQL> col banner format a100
SQL> select * from v$version;

BANNER

---

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

SQL>
SQL> drop table dxdb_cmsattrib;

Table dropped.

SQL> 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.

SQL>
SQL> drop table dxdb_cms_vorzug_enum;

Table dropped.

SQL> 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.

SQL>
SQL> drop table dxdb_cms_zulassung_enum;

Table dropped.

SQL> 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.

SQL>
SQL> drop table dxdb_cms_rohs_enum;

Table dropped.

SQL> 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.

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

SQL> CREATE materialized VIEW dxdb_cms_attvalues_MV

   2  	 build immediate refresh complete next sysdate + 1/144
   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  	 ;
             dxdb_cmsattrib,
             *

ERROR at line 11:
ORA-30353: expression not supported for query rewrite

SQL> exit;

Does this help?
Lots of Greetings!
Volker

-- 
For email replies, please substitute the obvious.

Received on Tue Aug 29 2006 - 07:32:17 CDT