Volker Hetzer schrieb:
> 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
>
Volker, either i don't understand you or you me.
Try to change your ddl for materialized view omitting the "next sysdate
+ 1/144" ( like it is done in my example) part and see if you can create
a view enabled for query rewrite. The "next" part is not necessary in
the ddl, because it can be easily added afterwards by defining the
database job. This is of course a workaround if you are looking for
working solution. If you in opposite are asking, what is wrong in *your*
ddl, then , probably - nothing, my suggestion is - this is a bug. I like
you didn't seen the restriction in the documentation of this kind, but ,
obviously , one can't create a materialized view with enabled query
rewrite and defined scheduling for refresh in one step.
Best regards
Maxim
Received on Tue Aug 29 2006 - 08:03:10 CDT
