materialized view rewrite

From: Volker Hetzer <firstname.lastname_at_ieee.org>
Date: Mon, 28 Aug 2006 16:27:18 +0200
Message-ID: <ecuug6$9f1$1@nntp.fujitsu-siemens.com>

Hi!
I got a simple view, an inner join on four tables with only the equality condition but oracle (10.2.0.1, linux) gives me an ORA-30353. I've checked the doc and and uid, user, rownum,sysdate,... don't apply, except that the doc says etc and I don't know what falls under this. Here's what I'm trying to do:

drop table dxdb_cmsattrib;
CREATE TABLE dxdb_cmsattrib (

     fsc_snr VARCHAR2(128) CONSTRAINT NN_dxdb_cmsattrib_fsc_snr NOT NULL,
     vorzug VARCHAR2(1) CONSTRAINT NN_dxdb_cmsattrib_vorzug NOT NULL,
     zulassung VARCHAR2(128) CONSTRAINT NN_dxdb_cmsattrib_zulassung NOT NULL,
     rohs VARCHAR2(128) CONSTRAINT NN_dxdb_cmsattrib_rohs NOT NULL,
     CONSTRAINT PK_dxdb_cmsattrib PRIMARY KEY (fsc_snr)

);

drop table dxdb_cms_vorzug_enum;
CREATE TABLE dxdb_cms_vorzug_enum (

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

);

drop table dxdb_cms_zulassung_enum;
CREATE TABLE dxdb_cms_zulassung_enum (

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

);

drop table dxdb_cms_rohs_enum;
CREATE TABLE dxdb_cms_rohs_enum (

     Textstring VARCHAR2(128) CONSTRAINT NN_cmsrohs_Textstring NOT NULL,
     Value NUMBER(2),
     CONSTRAINT PK_dxdb_cms_rohs_enum PRIMARY KEY (Textstring)

);

drop materialized VIEW dxdb_cms_attvalues_MV; CREATE materialized VIEW dxdb_cms_attvalues_MV

	build immediate refresh complete next sysdate + 1/144
	enable query rewrite
	AS
	select
			fsc_snr,
			dxdb_cms_vorzug_enum.value vorzugvalue,
			dxdb_cms_zulassung_enum.value zulassungvalue,
			dxdb_cms_rohs_enum.value rohsvalue
		from
			dxdb_cmsattrib,
			dxdb_cms_vorzug_enum,
			dxdb_cms_zulassung_enum,
			dxdb_cms_rohs_enum
		where
				dxdb_cmsattrib.vorzug=dxdb_cms_vorzug_enum.textstring
			and dxdb_cmsattrib.zulassung=dxdb_cms_zulassung_enum.textstring
			and dxdb_cmsattrib.rohs=dxdb_cms_rohs_enum.textstring
	;

Does anyone know what's wrong?

Lots of Greetings and thanks!
Volker

-- 
For email replies, please substitute the obvious.

Received on Mon Aug 28 2006 - 09:27:18 CDT