Home » Server Options » Replication » Fast Refresh of Materalized Views
Fast Refresh of Materalized Views [message #328329] Thu, 19 June 2008 15:18 Go to next message
ajayselhi
Messages: 4
Registered: June 2008
Junior Member
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
PARALLEL
AS
SELECT t1.rowid t1rowid,
t2.rowid t2rowid,
t3.rowid t3rowid,
t1.tx_id,
t2.tx_apmt_id,
t3.dlv_itm_id
FROM t1,
t2,
t3
WHERE t1.tx_id = t2.tx_id
AND t2.dlv_itm_id = t3.dlv_itm_id(+);

create index indx_t1rowid on MV_TEST(t1rowid);
create index indx_t2rowid on MV_TEST(t2rowid);
create index indx_t3rowid on MV_TEST(t3rowid);

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 coulmns (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


Can anyone please suggest 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.



Thanks for your help.
Fast Rfresh of Materialized View [message #328335 is a reply to message #328329] Thu, 19 June 2008 15:34 Go to previous messageGo to next message
ajayselhi
Messages: 4
Registered: June 2008
Junior Member
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
PARALLEL
AS
SELECT t1.rowid t1rowid,
t2.rowid t2rowid,
t3.rowid t3rowid,
t1.tx_id,
t2.tx_apmt_id,
t3.dlv_itm_id
FROM t1,
t2,
t3
WHERE t1.tx_id = t2.tx_id
AND t2.dlv_itm_id = t3.dlv_itm_id(+);

create index indx_t1rowid on MV_TEST(t1rowid);
create index indx_t2rowid on MV_TEST(t2rowid);
create index indx_t3rowid on MV_TEST(t3rowid);

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 coulmns (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


Can anyone please suggest 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.



Thanks for your help.
Re: Fast Refresh of Materalized Views [message #328336 is a reply to message #328329] Thu, 19 June 2008 15:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

select count(*) from t1;
select count(*) from t1 where tx_id='1234';

what are the results from the SQL above?

Are statistics current for all objects involved?

[Updated on: Thu, 19 June 2008 15:37] by Moderator

Report message to a moderator

Re: Fast Refresh of Materalized Views [message #328339 is a reply to message #328336] Thu, 19 June 2008 15:51 Go to previous messageGo to next message
ajayselhi
Messages: 4
Registered: June 2008
Junior Member
select count(*) from t1;
This has around 160 million records

select count(*) from t1 where tx_id='1234';
This has one record. The tx_id has a unique index.

All the base tables, MV and all indexes on the MV has been analysed.

Re: Fast Refresh of Materalized Views [message #328340 is a reply to message #328329] Thu, 19 June 2008 16:00 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

do NOT cross/multi-post
Previous Topic: Materialized View
Next Topic: wrong number or types of arguments in call to 'VERIFY_LOG'
Goto Forum:
  


Current Time: Sat Dec 10 07:12:11 CST 2016

Total time taken to generate the page: 0.10920 seconds