| Nested materialized view ORA-12053 [message #75486] |
Mon, 29 March 2004 05:51  |
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  |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
