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.
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?
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.
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 RelatedCREATE 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.
Any best way to gather statistics on Materialized Views.
View 1 Replies View RelatedI have a Problem regarding Data Replication, I am using Standard Version of Oracle 9i,I am having 5 remote servers , I am replicating the data from all of these servers using Materialized views on a Centralized server , I have created 5 schema for them, there is a application which is Consolidating the Data from all the locations.
When I create materialized views from these Materialized views I am facing problems to Fast refresh them as I am using "Union All" to combine data of all Users (Error is ora -12004) . I would like to know that is there any other way to do this, also I have tried all the ways to solve the problem of Ora-12004.
I have a Materialized view based on 2 views. I modified one column in both the views.Do i need to refresh the Materialized view.
E.g. :
In view1 and view2 i changed column like this
nvl(test.no,'n/a') testdata --changed
nvl(test.id,'n/a') testdata --original
CREATE MATERIALIZED VIEW MV_test
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
[code].........
while replicating the master site via materialized views, in mViewGroups i issued:
BEGIN
DBMS_REFRESH.MAKE (
name => 'mvadmin.hamza_refg',
list => '',
next_date => SYSDATE,
interval => 'SYSDATE + 1/1440', -- for test purpose i used such a small interval
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
in my previous attempt it worked all fine... i had a change in senerio, so i did my replication again from scractch using the same old spool in a new installation but even after 3 tries on replicating from scratch my objects are not getting refreshed automatically. even when i attempt to refresh the group it does not work. it works only when i refresh each of the participating object of the group manually.
I have a table in one database and I want to replicate it in another two databases with materialized views. The refresh may be fast. Is this possible?
View 1 Replies View RelatedWe have some materialized views in our environment which refreshes after every 1 hour. In the same environment, we have some big processes and we do not want the performance of these big processes to get affected because of refreshing these materialized views.
if there is a way to pause the refreshing of materialized views before running the big processes and later after the big processes are completed, then resume the refreshing of the materialized views?
One way I can think of is to drop the materialized views and create the materialized views back.
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.
I have one question on MVIEWS.....
Where the logs will be logged for all the materialized views transactions (I mean a particular table)?
Is there any Data Dictionary Table were i can find this .
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?
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
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?
I am an Oracle newbie.
We have 2 fact tables and one lookup table in this structure:
FACTTABLE1 (C1_ID, C2, SALE)
FACTTABLE2 (C1_ID, C3, SALE)
LOOKUPTABLE (C1_ID, C1_NAME)
The DBAs have built 2 Materialized Views, which aggregates data in the fact tables at column C1 level
MAT_VIEW1 :SELECT C1_ID, SUM(SALE) SALES from FACTTABLE1 join LOOKUPTABLE on C1_ID
MAT_VIEW2: SELECT C1_ID, SUM(SALE) SALE from FACTTABLE2 join LOOKUPTABLE on C1_ID
We are using an old BI tool that can ONLY generate Inline Views in these formats.
CASE1:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
CASE2:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(select C1_ID, null C2_ID, C3_ID, SALE from FACTTABLE2)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
CASE3:
select
INL_VIEW.C1_ID
,LOOKUPTABLE.C1_NAME
,sum(SALE) SALE
from
(
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE1
union all
select C1_ID, C2_ID, null C3_ID, SALE from FACTTABLE2
)INL_VIEW
join LOOKUPTABLE
on INL_VIEW.C1_ID = LOOKUPTABLE.C1_ID
group by INL_VIEW.C1_ID, LOOKUPTABLE.C1_NAME
Oracle 11g rewrites Case 1 and Case 2 to use the correct materialized views. But for case 3, it goes to the base fact tables 1 and 2. Is there a way to make oracle use the MVs even if there is a UNION ALL in the inline view? There is a 1:M Foreign Key relationship between LOOKUPTABLE.C1_ID and the 2 fact tables.
I want to create a materialized view for the last 10 days with the enable query rewrite option.
e.g. i want to create a view with the list of employees who joined the company in the last 10 days.
create materialized view M_Employee
refresh fast on commit
enable query rewrite as
select joining_date , name
from
employee
where joining_datde < TRUNC(sysdate) - 10
I seem to get the error SQL Error: ORA-30353: expression not supported for query rewrite
30353. 00000 - "expression not supported for query rewrite"
*Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE.
This error is self explanatory , but is there any work around to have a query liek this to list the employees based on sysdate.
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.
I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.
View 3 Replies View Relatedi 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 RelatedWhy do we need materialized views when the normal views also serve the same purpose?
View 2 Replies View RelatedI have a problem Mat view refresh...I have to take a snapshot of the table before nsert or update to the table....below is the code written to achieve this..
create materialized view my_view refresh complete as select * from t2;
create or replace trigger TRG_T2
before insert or update on T2
declare
X number;
[code]....
However this is not getting refreshed... Also i noticed that it works for the very first time (after first insert or update) and then for successive ones it doesnt.. ..
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.
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 RelatedCan we add partitions for materialized views like tables ? ALTER TABLE owner.tablename ADD PARTITION p1 VALUES LESS THAN (2012,12);
Like is there any syntax for mview ALTER TABLE mv.mviewname ADD PARTITION ..... ?
I need to know that what is best approach for replication. Materialized View or Streaming. We have to replicate a subset of database at different nodes.
View 3 Replies View RelatedI 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?
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
What are the advantages of writable materialized views.
View 6 Replies View Related