| materialized view type is not supported by master site !! [message #160070] |
Thu, 23 February 2006 01:08  |
ankurgodambe
Messages: 45
Registered: March 2005
|
Member |
|
|
Hi ,
My database version is 10.2.0.1. and I am trying to create a MV as below but its errors out:
CREATE MATERIALIZED VIEW LOG ON SAE_SPEND_FACT with rowid;
CREATE MATERIALIZED VIEW SAE_MV_1
REFRESH FAST with rowid
AS
select a11.CLIENT_KEY CLIENT_KEY, a11.MASTER_SUPPLIER_CODE MASTER_SUPPLIER_CODE, sum(a11.SPEND) SPEND, sum(a11.TRANSACTION_COUNT) TRANSACTION_COUNT, count(*) CO
from SAE_SPEND_FACT a11
group by a11.CLIENT_KEY, a11.MASTER_SUPPLIER_CODE;
ERROR at line 5:
ORA-12028: materialized view type is not supported by master site
But when i create a one with COMPLETE refresh, it is created.
SQL> CREATE MATERIALIZED VIEW SAE_MV_1
2 REFRESH COMPLETE
3 AS
select a11.CLIENT_KEY CLIENT_KEY, a11.MASTER_SUPPLIER_CODE MASTER_SUPPLIER_CODE, sum(a11.SPEND) SPEND, sum(a11.TRANSACTION_COUNT) TRANSACTION_COUNT, count(*) CO
from SAE_SPEND_FACT a11
group by a11.CLIENT_KEY, a11.MASTER_SUPPLIER_CODE;
Materialized view created.
What is going wrong in FAST refresh? Also MV is on the same database( No DB links), the table has only varchar2 and number datatypes.
I recently upgraded the database from 10.2.0.3 to 10.2.0.1. But view wan not existing prior to upgrade, its only now that we want it. I have gone through metalink but could not find anything relevant.
Thanks.
[Updated on: Thu, 23 February 2006 01:13] Report message to a moderator |
|
|
|
| Re: materialized view type is not supported by master site !! [message #161640 is a reply to message #160070] |
Mon, 06 March 2006 07:13  |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
This might help:
[Source: http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96567/repmview.htm]
To be fast refreshed, the defining query for a materialized view must observe certain restrictions. If you require a materialized view whose defining query is more general and cannot observe the restrictions, then the materialized view is complex and cannot be fast refreshed.
Specifically, a materialized view is considered complex when the defining query of the materialized view contains:
* A CONNECT BY clause
For example, the following statement creates a complex materialized view:
CREATE MATERIALIZED VIEW hr.emp_hierarchy AS
SELECT LPAD(' ', 4*(LEVEL-1))||email USERNAME
FROM hr.employees@orc1.world START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
* An INTERSECT, MINUS, or UNION ALL set operation
For example, the following statement creates a complex materialized view because it has a UNION ALL set operation:
CREATE MATERIALIZED VIEW hr.mview_employees AS
SELECT employees.employee_id, employees.email
FROM hr.employees@orc1.world
UNION ALL
SELECT new_employees.employee_id, new_employees.email
FROM hr.new_employees@orc1.world;
* In some cases, the DISTINCT or UNIQUE keyword, although it is possible to have the DISTINCT or UNIQUE keyword in the defining query and still have a simple materialized view
For example, the following statement creates a complex materialized view:
CREATE MATERIALIZED VIEW hr.employee_depts AS
SELECT DISTINCT department_id FROM hr.employees@orc1.world
ORDER BY department_id;
* An aggregate function
For example, the following statement creates a complex materialized view:
CREATE MATERIALIZED VIEW hr.average_sal AS
SELECT AVG(salary) "Average" FROM hr.employees@orc1.world;
* Joins other than those in a subquery
For example, the following statement creates a complex materialized view:
CREATE MATERIALIZED VIEW hr.emp_join_dep AS
SELECT last_name
FROM hr.employees@orc1.world e, hr.departments@orc1.world d
WHERE e.department_id = d.department_id;
* In some cases, a UNION operation. Specifically, a materialized view with a UNION operation is complex if any one of these conditions is true:
o Any query within the UNION is complex. The previous bullet items specify when a query makes a materialized view complex.
o The outermost SELECT list columns do not match for the queries in the UNION. In the following example, the first query only has order_total in the outermost SELECT list while the second query has customer_id in the outermost SELECT list. Therefore, the materialized view is complex.
CREATE MATERIALIZED VIEW oe.orders AS
SELECT order_total
FROM oe.orders@orc1.world o
WHERE EXISTS
(SELECT cust_first_name, cust_last_name
FROM oe.customers@orc1.world c
WHERE o.customer_id = c.customer_id
AND c.credit_limit > 50)
UNION
SELECT customer_id
FROM oe.orders@orc1.world o
WHERE EXISTS
(SELECT cust_first_name, cust_last_name
FROM oe.customers@orc1.world c
WHERE o.customer_id = c.customer_id
AND c.account_mgr_id = 30);
The innermost SELECT list has no bearing on whether a materialized view is complex. In the previous example, the innermost SELECT list is cust_first_name and cust_last_name for both queries in the UNION.
|
|
|
|
| Re: materialized view type is not supported by master site !! [message #162405 is a reply to message #161640] |
Fri, 10 March 2006 01:29 |
ankurgodambe
Messages: 45
Registered: March 2005
|
Member |
|
|
Hi Girish,
This was not a case of complex select query creating issue with fast refresh mviews. The actual cause was the conflict between user objects and dictionary. The schema in which I was trying to create fast refresh mview had objects starting with DBMS% and DBA%. When those objects were droppped fast refresh mview could be created.
Wierd!!! I came to know about this only after raising TAR with Oracle. There is no documentation on what the conflict is and what all things it affects; on metalink or web.
Take a note and prevent object names like DBA% and DBMS% in application schemas.
Cheers,
Ankur
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
