Home » Server Options » Replication » Nested materialized view ORA-12053
Nested materialized view ORA-12053 [message #75486] Mon, 29 March 2004 05:51 Go to next message
Insectwarrior
Messages: 2
Registered: March 2004
Junior Member
Oracle 8i

I have created a main materialized view which is a join of two tables, it has query rewrite enabled and is fast refresh on demand

the nested materialized view is a summary of fields from the main materialized view, it too is query rewrite enabled and fast refresh on demand.

      CREATE MATERIALIZED VIEW nest1_mv
      TABLESPACE ABC_LARGE PARALLEL NOLOGGING
      BUILD IMMEDIATE USING INDEX
      REFRESH Fast on demand
      ENABLE QUERY REWRITE
      AS SELECT /*+ FIRST_ROWS */
            fiscal_month,facility_nbr,ship_type,count(*) as tot_count,  sum(rcv_qty),sum(strg_cost),
                sum(vend_cost),sum(act_cost),
                count(rcv_qty),count(strg_cost),
                count(vend_cost),count(act_cost)
          FROM main_mv
          WHERE fiscal_year='2004'
          GROUP by fiscal_month,facility_nbr,ship_type;

          GROUP by fiscal_month,facililty_nbr,ship_type
                                                          *
ERROR at line 14:
ORA-12053: this is not a valid nested materialized view

I receive an ORA-12053 nested materialized view is invalid when I try to create the 2nd view. I can not see what the problem is and can not find meaningful reference to ORA-12053.

Any help is appreicated Thank you

 
Re: Nested materialized view ORA-12053 [message #75487 is a reply to message #75486] Tue, 30 March 2004 10:32 Go to previous messageGo to next message
Insectwarrior
Messages: 2
Registered: March 2004
Junior Member
To overcome the problem I used a partition of the main mview instead of the where clause:

CREATE MATERIALIZED VIEW nest1_mv
TABLESPACE ABC_LARGE PARALLEL NOLOGGING
BUILD IMMEDIATE USING INDEX
REFRESH Fast on demand
ENABLE QUERY REWRITE
AS SELECT /*+ FIRST_ROWS */
fiscal_month,facility_nbr,ship_type,count(*) as tot_count,sum(rcv_qty),sum(strg_cost),
sum(vend_cost),sum(act_cost),
count(rcv_qty),count(strg_cost),
count(vend_cost),count(act_cost)
FROM main_mv partition (2004)
GROUP by fiscal_month,facility_nbr,ship_type

nested mviews really don't like the where clause.
Re: Nested materialized view ORA-12053 [message #75602 is a reply to message #75487] Thu, 06 January 2005 13:16 Go to previous message
turing complete
Messages: 1
Registered: January 2005
Junior Member
I ran into the same error. My work around was to use an intermediate plain view, and then create the mview based on select * from intermediate_view. Don't know why it was giving the error in the first place as the error messages w/r/t mviews are rarely that detailed.
Previous Topic: No changes when running DBMS_REFRESH
Next Topic: how to rollback a materialized view in oracle 8.1.7
Goto Forum:
  


Current Time: Thu Mar 28 18:23:29 CDT 2024