Replication :: ORA-12013 / Updatable Materialized VIEWS Must Be Simple Enough TO DO Fast Refresh

Aug 20, 2009

CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;

It is working fine. Appended the Query with WHERE clause in MV_ABC MVIEWS.

DROP MATERIALIZED VIEW LOG ON ABC;
DROP MATERIALIZED VIEW MV_ABC;
CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;

ORA-12013: updatable materialized VIEWS must be simple enough TO DO fast refresh

View 6 Replies


ADVERTISEMENT

Replication :: Fast Refresh Of Materialized Views

Jun 19, 2008

CREATE Materialized View log on t1 with rowid;
CREATE Materialized View log on t2 with rowid;
CREATE Materialized View log on t3 with rowid;
CREATE Materialized View MV_test
BUILD IMMEDIATE
FAST REFRESH ON COMMIT
[code].......

I have created a Materialized View by joining the above 3 tables. The Materialized View logs with ROWID has been created on each of the 3 base tables. One of the base tables has around 160 million rows and i expect the Materialized View to have the same number of rows.

The Materialized View has been built for FAST REFRESH on COMMIT. The SELECT list of the CREATE Materialized View statement includes all the ROWIDS from each base tables for doing the fast refresh.

The DBMS_MVIEW explain view shows that the view is capable of doing fast refresh on any DML. The Materialized View took 3 hrs to get built. I have also created indexes on all the three rowid columns (t1rowid,t2rowid,t3rowid) on the Materialized View.

Now, When i do any update on one of the base table and do a commit,

UPDATE t1 set value=500 where tx_id='1234';

The commit is taking around around 15 minutes. I have generated the trace and had a look at it, and it shows that, to refresh the Materialized View, ORACLE is first deleting that record from the Materialized View and then inserting the new record from the base table to the Materialized View.
This delete done by oracle is being done on the basis of the rowid of the updated record stored on the Materialized View log,

However its not using the index created on the t1rowid of the Materialized View and doing a full scan of the MV and hence the delete is taking a lot of time. The delete statement is using a hint NO_MERGE and HASH_SJ which is causing it to do the full table scan of the MV. why is ORACLE not using the index on the t1rowid column of the MV to do this delete?

Also to do the insert in the MV, its selecting the new changed value from the base tables t1,t2 and t2. Here again its doing a full table scan of the base table t1, rather then using the rowid directly or the index coulmn.which is taking a lot of time.

View 4 Replies View Related

Convert Some Existing Materialized Views (fast Refresh) To Partition Materialized Views

Jul 7, 2010

I have to convert some existing materialized views (fast refresh) to partition materialized views.

Database version is oracle 10.1.0.4. I have decided to use on prebuilt table option to do the partitioning as it minimizes the time to transfer from the master site.

1) stop replication
1) create interim tables with similar structure as the materialized views
2) transfer all data from the materialized views to the interim tables
4) script out the materialized views structure and add in on prebuilt table option in the scripts
5) drop the materialized views
6) rename the interim tables with the same name as the materialized views
7) run the scripts to create the materialized views with on prebuilt table option
8) refresh the newly created materialized views -> it should take a short time since I am using on prebuilt table option

But I am facing one major issue. That is if I drop the materialized views, the materialized view logs of the master tables are purged. When the materialized views are refreshed fast, there are some data missing. the data that are purged out when the materialized view are dropped.

Do you happen to know other ways that existing materialized views can be converted to partitioned materialized views? Do you have any workaround to prevent the materialized view logs from being purged?

View 3 Replies View Related

SQL & PL/SQL :: Materialized View On Views - Fast Refresh?

Jun 17, 2010

We are planning to create a materialized view on 5 tables and 2 views. After first complete refresh we want to do Fast refresh going forward and planning to create view log for base tables. Since the SQL uses two views, we can create view-log for its underlying tables. But will that work ? In case of fast refresh ? or it will still go for complete refresh ?

View 9 Replies View Related

Replication :: Error Performing Fast Refresh Of Materialized View?

Sep 4, 2008

We are facing serious problem while refreshing materialized views using fast refresh option in ORACLE..For the very first time we are performing Complete refresh of data from DB1 to DB2 for few tables.Ongoing we are performing Fast Refresh.Sometimes the fast refresh works fine without any error and sometimes it fails with the below error.

ERROR at line 1:
ORA-32320: REFRESH FAST of "CIR"."C_BO_COMM" unsupported after
cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

let me know when will this error occur during Fast Refresh.

View 4 Replies View Related

Replication :: Materialized Views Refresh In Snapshot-group?

Mar 24, 2010

How to refresh the dependent materialized views in a snapshot group. It refreshes in the order we add the materialized views to snapshot group?

Here below is the scenario.

MATVW_A
CREATE MATERIALIZED VIEW MATVW_A BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM EMP;

MATVW_B
CREATE MATERIALIZED VIEW MATVW_B BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_A;

MATVW_C
CREATE MATERIALIZED VIEW MATVW_C BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_B;

I created a snapshotgroup SNGRP with all the above materialized views.

DBMS_REFRESH.MAKE(name => 'SNGRP',
tab => 'MATVW_A,MATVW_B,MATVW_C',
next_date => TRUNC(sysdate) + 1,
interval => 'SYSDATE + 1');

The order of refreshing the materialized views would be like first materialized view MATVW_A should get refresh then after MATVW_B,MATVW_C.

As they are dependent views.Is there anyway where we can mention the order of refreshing materialized views in snapshot group.

View 2 Replies View Related

Replication :: Setup A Refresh Group For Nested Materialized Views

Mar 25, 2009

Suppose I have materialized view A,B,C. Now I have a new materialized view D which is created from A,B,C. Can I put D into same refresh group?

I am not sure whether D will be refreshed first before A,B,C, resulting that D do not get any update If that is the case, the result will be wrong

If we cannot use refresh group to update nested materialized view, what should we do?

View 13 Replies View Related

Replication :: Setup Database Replication With Updatable Materialized View?

Sep 4, 2009

i need to set up a central server with all the master tables and two other local database which will hold the updatable materialized view of the master table...the databases must be synchronized with central server..and user will work on the materialized view database...

View 10 Replies View Related

Replication :: Cannot Fast Refresh Truncated MV Even After Complete Refresh

Aug 14, 2010

I am getting ORA -32320 when I tried to fast fresh MVs that were truncated.I am able to complete refresh all MVs. However, some MVs are still giving me ORA-32320 when I try to do fast refresh even after complete refresh.

DBMS_MVIEW.EXPLAIN_PLAN says the MVIEW can be fast refreshed.

ERROR at line 1:
ORA-32320: REFRESH FAST of "dev"."Claim_line" unsupported after cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

Quote:ORA-32320:
REFRESH FAST of "string"."string" unsupported after container table PMOPs Cause: A Partition Maintenance Operation (PMOP) has been performed on the materialized view, and no materialized view supports fast refersh after container table PMOPs. Action: Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.

how to again make the MVs fast refreshable?

View 4 Replies View Related

Refresh Materialized Views Based On Remote Views?

Aug 4, 2011

I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.

Materialized view is created by

CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';

I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.

View 1 Replies View Related

Materialized View Is Not Doing Fast Refresh

Jun 20, 2011

i have created a fast refresh materialized view but it is not performing fast refresh or even complete refresh.Then i have checked my materialized view capabilities then in mv_capabilities_table i got a message that materialized view log is newer than old .

View 2 Replies View Related

SQL & PL/SQL :: Materialized View With Aggregate And FAST REFRESH

Feb 8, 2012

I'm trying to build up a materialized view with aggregate and FAST REFRESH for INSERT and UPDATE, DELETE with no success. But the web doesn't deny it ?

--build up needed schema objects

CREATE TABLE empt
( ename VARCHAR2(20),
empno INTEGER,

[Code]....

View 9 Replies View Related

SQL & PL/SQL :: To Fire Triggers While Materialized FAST Refresh

Dec 8, 2011

I have the source tables having 10 columns. I want to track the delta changes of only 6 columns. In case of insertion/updation, that new record should be inserted into another DELTA table of different schema.While in case of deletion, A new row will be inserted into that DELTA table , with the value of the primary key and other columns populated with NULL values.

I have applied the concept of 'after trigger' specifying the columns names but it logic is not working properly. Its tracking the changes for other 4 columns also.

View 4 Replies View Related

SQL & PL/SQL :: Performance With Materialized View Fast Refresh

Oct 10, 2013

I've 2 tables in schema A.

1) TXN_HD_FCT (This table has 6 billion records. Has fields TXN_DT, CUST_ID, TXN_AMT, TXN_ID and the table is partitioned on TXN_DT. We load data approximately 600K+ records to this table every day
2) RELINK_CUST (This table has 2 fieds CUST_ID and NEW_CUST_ID). We load data approximatley 100-200K+ records every day into this table. This table has total 10MM+ records now.

Now i need to create a table (TXN_UPD_HDR_FCT) in Schema B by joining above 2 tables using below query every day

CREATE TABLE TXN_UPD_HDR_FCT
select a.TXN_DT,
nvl(b.new_cust_id, a.cust_id) as cust_id,
a.txn_amt,
a.txn_id,
from schema_A.TXN_HD_FCT a,
schema_A.RELINK_CUST b
where a.cust_id = b.cust_id(+);

We update CUST_ID to NEW_CUST_ID based on some rules (and keep it in the table RELINK_CUST) and need to take this new_cust_id while building the table TXN_UPD_HDR_FCT.

Building this table is taking approximately 4+ hours every day. just refresh only records from TXN_HDR_FCT whose CUST_ID is changed or any records inserted into TXN_HDR_FCT? I read about Materialized views but not sure how can i create MV logs on 2 tables? If i use materialized views do i need to create indexes every day?

View 15 Replies View Related

Materialized View - Refresh In 10g FAST / 11g SLOW

Nov 1, 2012

The customer moved their database from 10g to a 11gR2 database.They created the MV in the new system and now it takes 26 hours to refresh as opposed to 15 min in the old 10g database.Just looking for a game plan for troubleshooting.

View 8 Replies View Related

SQL & PL/SQL :: Materialized View - Fast Refresh Whenever Commit Placed On Table?

Mar 6, 2013

I have a view that includes sub queries in its select.

I am creating a MV over that view. and i want to fast refresh it when ever a commit is placed on the table (that is used in the from clause for the simple view)

View 9 Replies View Related

PL/SQL :: Refresh Fast Materialized View On Complex Query

Sep 26, 2013

create materialized view ino_crm.MV_IDENTIFICATION_DETAILS REFRESH FAST as SELECT billing_account_id, i.s1 KEYS_ , COALESCE(i.s2, i.b, i.d) VALUES_ FROM ba_personal_details pd ,  xmltable ( '//entry[string = "identification Details"]//entry' passing xmltype(pd.details)  columns s1 VARCHAR2 (255) path 'string[1]'  ,s2 VARCHAR2(255) path 'string[2]'  ,b VARCHAR2(255) path 'boolean'  ,d VARCHAR2(255) path 'date'  ) i WHERE pd.mandator_key = 'VSO01'  AND pd.valid_to = to_date('01.01.4000', 'DD.MM.YYYY'); and

I get an exception like: SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query 12015. 00000 -  "cannot create a fast refresh materialized view from a complex query"

*Cause:  Neither ROWIDs and nor primary key constraints are supported for  complex queries.
*Action:   Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view. 

I have found this URL....

View 2 Replies View Related

Performance Tuning :: Best Practices To Optimize A Fast Refresh Materialized View

Jan 16, 2012

I have a base table with ~20 mio. records with two FAST REFRESH Materialized Views based on that table using various aggregate functions in their view definition.

The problem is, when e.g. one record changes in the base table, I see two records in the MV log table MLOG$, but invoking the fast refresh mechanism by using using:

dbms_snapshot.refresh('mv1', 'f');
dbms_snapshot.refresh('mv2', 'f');

Is still running after ~20 minutes now.

View 2 Replies View Related

SQL & PL/SQL :: ORA-12015 / Cannot Create Fast Refresh Materialized View From Complex Query

Apr 27, 2011

I'm trying to create materialized view, successfully create logs for all the tables involved.

CREATE MATERIALIZED VIEW LOG ON tbJournal
WITH ROWID, SEQUENCE (tabid, companyid, storelocid, tabstatus, linetype)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON TBTABS
WITH ROWID, SEQUENCE (TSTAMP, COMPANYID, NUMGUESTS, POSITIONID, STORELOCID, TABSSTATUS)
INCLUDING NEW VALUES;

[code]....

The result is ora-12015: cannot create a fast refresh materialized view from a complex query

It does work fine if I remove

AND EXISTS (SELECT 1 FROM tbJournal J
WHERE j.tabstatus NOT BETWEEN -6 AND -4
AND j.linetype = 1
AND T.tabid = J.tabid
AND T.companyid = J.companyid
AND T.storelocid = J.storelocid)
from the where clause

View 10 Replies View Related

How To Refresh Materialized Views

Aug 4, 2011

I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.

Materialized view is created by

CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';

how the refersh happens or how do I specify the refresh clause.

REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.

View 1 Replies View Related

SQL & PL/SQL :: Refresh All Materialized Views?

Nov 28, 2011

I have n number of materialized views in my schema ..I want to refresh all M_views at once by using cursor

View 3 Replies View Related

Replication :: Materialized View Fast Refreshes Are Slow?

Jun 28, 2009

I created a Materialized view using joining of two tables with FAST REFRESH ON COMMIT option. These tables are the main tables of the DB and contains alot of data(about 15 millions of records). two master tables have index but mview doesn't have any indexes. I've thought the entire concept of the fast refresh is that it should be a relatively quick operation but the result showed that DB performance reduced, for example I could insert 900 records per minute thru application into master tables before but now I can just insert about 400 records per hour!.Also, It slows down the select operation, went from 0.11 seconds(with joining two tables) to 33 seconds (in mview)!.

First of all, I considered following possibilities and test them:

1) The most likely solution was that a complete refresh was happening. However, the materialized view refresh was confirmed to be a fast refresh by querying USER_MVIEWS.

2) I checked the query of the materialized view and confirmed that it was a simple select from the master tables without any aggregation or sorting.

3) DBMS_MVIEW.explain_mview was run on the materialized view. MV_CAPABILITIES_TABLE did not reveal any problems.

4) A query of dba_registered_snapshots showed that no other snapshots appeared to be using these logs. So, After each refresh, the records in mview logs were deleting.

As you see, it seems everything is ok, but what was causing this fast refresh to go so slow?

I use oracle 10.2 on Suse Linux enterprise server.

View 3 Replies View Related

Replication :: Alter Materialized View From Complete To Fast?

Jan 4, 2008

I am having two server A and B.B is the replica of A.I have to alter the materialized view of one of the table in server B,of which Master table is in both the servers .I do not have any Materialized view log on both the servers and want to convert the Refresh method from Complete to Fast refresh.One way of this altration is to create materialized view log on master table.

View 6 Replies View Related

One Materialized Views In Two Refresh Groups

Sep 11, 2013

I have 5 MViews that I want to refresh in two occasions, every Sundays and at the 1st of the month. I created a Refresh Group for the weekly and that work fine. But when I tried to created the second Refresh Group for the monthly I get a "materialized view is already in a refresh group".

You can only have a materialized view in one refresh group? What options to I have to refresh it in different intervals?

View 1 Replies View Related

Materialized Views Vs Refresh Groups?

Sep 29, 2010

We recently migrated a database from 9i to 10g (overdue we know!!) and discovered that dbms_mview.refresh default behavior was turned upside down - meaning that 10g didn't first truncate the MV to refresh it. We're trying to unwind a lot of legacy issues, but it also turns out that we also have 100 REFRESH GROUPs and 100 MATERIALIZED VIEWs. That means a 1 to 1 relationship between RGs vs MVs. There is one MV defined to each RG.

These are my questions:

1) Does a 1 to 1 relationship between RGs and MVs make sense to anybody? The original implementors are gone and we can't fathom the reason for this.

2) Is there any reason why I shouldn't convert these 100 groups to plain and simple 100 MVs? I don't want the delete/insert refresh behavior of dbms_refresh.refresh and I do want the truncate behavior of dbms_mview.refresh ATOMIC=FALSE for refreshing a standard MVIEW

View 1 Replies View Related

Oracle Materialized Views - Refresh Combinations

Dec 10, 2012

I'd like to set up a new Oracle Materialzed View to automatically default to refresh FAST every hour. I have no trouble setting this up in the CREATE MATERIALIZED VIEW statement. However, we will occasionally be performing table maintenance on the table referenced by the MV. At that time I would like to use dbms_mview.REFRESH to execute a refresh ON COMMAND (either F or C depending on the operation weperformed on the table). I have read that this will override the automatic refresh every hour that was set up for the MV when it was created. However - wil the override be permanent? Or will the MV continue to automaticaly FAST refresh every hour after the ON COMMAND refresh is completed?

View 1 Replies View Related

Replication :: Materialized Views?

Oct 25, 2011

for following two questions.

1. I have created a materialized view with following syntax in 11g R1 database.

CREATE MATERIALIZED VIEW fr_emp_bonus_record_vw
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE+1/48
ENABLE QUERY REWRITE AS
SELECT person_id, <columns>

This MV will have around 300K records and this MV will be used in other queries based on person_id. So I have created an index on mv.person_id with following syntax. I have chosen REFRESH COMPLETE, I am assuming that, Oracle truncates this table and recreates it. When it recreates this MV, does it analyze automatically or do I need to do it manually.

CREATE UNIQUE INDEX mv_u1 ON mv(person_id);

2. It takes around 2 minutes to populate 300K. While it is populating the data, if somebody accessing this view or query where this view is being used, are they going to get any error?

View 1 Replies View Related

Replication :: Materialized Views On 3 Database?

Apr 8, 2012

There is a database db1 which has user U1 in in it contains T1 as table.

Likely,

There is also another database db2 which also has a user named U2 containing table T2 in it.

Now

I want to use the concept of JOINS and Join Table T1 of database named DB1 and Table T2 of database named DB2 and access from database named DB3 using Materialized View Concept.

what shall i do to access tables of DB1 and DB2 from database DB3 using Materialized View.

View 7 Replies View Related

Replication :: Sub-query In Materialized Views

Apr 1, 2009

I have a view with complex query that have grouping and aggregations in sub-queries.

Now we would like to change it to a materialized view. However, materialized view does not support GROUP BY clause in sub-query. The solution is to create several view for the sub-queries and then create a materialized view based on it. It seems it can work, but I guest every time complete refresh is required.

View 1 Replies View Related

Replication :: Materialized Views Not Getting Refreshed

Oct 3, 2009

I have a database link between 2 databases and that works fine. Now the tables of one database are having thier materialized views in the other. The materialized views are not getting refreshed. They hang when i try refreshing them. I have tried everything said on the forums. I changed the job_queue_processes. Also changed the query_reqrite_enabled to true. Still it won't work.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved