Re: materialized view rewrite

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

Relevant Pages

  • Re: materialized view rewrite
    ... "query rewrite" I beleive is only available in the Enterprise Edition. ... We've got the enterprise edition on linux. ... SQL> col banner format a100 ... vorzug VARCHAR2CONSTRAINT NN_dxdb_cmsattrib_vorzug NOT NULL, ...
    (comp.databases.oracle.server)
  • Re: Oracle 10G and Trigger Problem
    ... SQL> insert into blah values; ... Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod ... PL/SQL Release 10.2.0.1.0 - Production ...
    (comp.databases.oracle.misc)
  • Re: Error when importing
    ... SQL> select * from v$version; ... Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod ... PL/SQL Release 10.2.0.1.0 - Production ...
    (comp.databases.oracle.server)
  • Re: MSDN SQL 2008 Enterprise non-trial version
    ... It appears to me that there is no sql 2008 Enterprise available yet except for the trial version. ... Has anyone installed a non-trial version of sql 2008 Enterprise in a production environment yet? ... use Enterprise Edition for production, YOU WILL NEED TO BUY IT. ...
    (microsoft.public.sqlserver.setup)
  • Re: Benchmark for different edition SQL 2000
    ... > My concern is how to prevent the SQL server that increase the memory usage continuously? ... > Enterprise edition has some distinct features that SE doesn't. ... the memory arrangement is the same between standard and enterprise? ...
    (microsoft.public.sqlserver.server)