Home » Server Options » Replication » Alternative for Complete Refresh / Delete records from MVIEWS (Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit )
Alternative for Complete Refresh / Delete records from MVIEWS [message #419018] Wed, 19 August 2009 10:47 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

TABLE NAME :ABC  
(PARTITION BASE TABLE - 84 Partition and each Partition has 500-800 Millions of Records) 


/* Step1 Create MATERIALIZED VIEW LOG */
CREATE MATERIALIZED VIEW LOG ON ABC;

/* Step2 Create MATERIALIZED VIEW Refresh after Every 20 Minutes */
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/ (24*60) AS
SELECT * FROM ABC
WHERE TMSTP > SYSDATE-1;



Scenario:Intially data extracted will be "Greater than SYSDATE-1 = 18/08/2009 11:27:39 " - 76 Millions of Records
After 20 Min of FAST Refresh, it will add 2500 Records

Please suggest on each Refresh,
i)Is query in the MVIEWS (MV_ABC) SYSDATE-1 will be changed on each FAST Refresh, as observation (as per definition) it is incrementing the records to intial extracted data?

ii)As COMPLETE Refresh will be expensive to execute on the partition base table on each refresh.
Is there any alternative way to achieve the Nature of COMPLETE REFRESH in Materialized View?

iii)Is Delete or Conditional Delete option is possible in Materialized View (i.e not a delete from the base table but the delete from MVIEWS)?

iv)What is the machnism for populating the logs for MVIEWS?
Is it overhead for the system and better than a Trigger based approach?
Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419035 is a reply to message #419018] Wed, 19 August 2009 11:19 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


>>iii)Is Delete or Conditional Delete option is possible in Materialized View (i.e not a delete from the base table but the delete from MVIEWS)?

No; You have created READ ONLY Materialized view not UPDATABLE. If you want updatable then go with Advanced Replication.

>>i)Is query in the MVIEWS (MV_ABC) SYSDATE-1 will be changed on each FAST Refresh, as observation (as per definition) it is incrementing the records to intial extracted data?

>>iv)What is the machnism for populating the logs for MVIEWS?

During refresh; you will get latest records details in your materialized view logs.

>>ii)As COMPLETE Refresh will be expensive to execute on the partition base table on each refresh

You have billions of records; Please do NOT gowith COMPLETE refresh.

Babu
Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419164 is a reply to message #419035] Thu, 20 August 2009 03:55 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Gentlebabu,

Quote:
>>iii)Is Delete or Conditional Delete option is possible in Materialized View (i.e not a delete from the base table but the delete from MVIEWS)?

No; You have created READ ONLY Materialized view not UPDATABLE. If you want updatable then go with Advanced Replication.


Pls. suggest, after using UPDATEBLE, Can one Delete the records from MVIEWS but not from the base table?

Thanks.

[Updated on: Thu, 20 August 2009 10:16] by Moderator

Report message to a moderator

Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419173 is a reply to message #419035] Thu, 20 August 2009 04:51 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Gentlebabu,

Able to Delete the MVIEWS as per your valuable suggestion.
Thanks.


Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419205 is a reply to message #419173] Thu, 20 August 2009 06:35 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Gentlebabu,

But the Delete in the MVIEWS is not happening permanent from the mviews.

Thanks.
Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419225 is a reply to message #419205] Thu, 20 August 2009 10:03 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
>>iii)Is Delete or Conditional Delete option is possible in Materialized View (i.e not a delete from the base table but the delete from MVIEWS)?

No; You have created READ ONLY Materialized view not UPDATABLE. If you want updatable then go with Advanced Replication.


Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419238 is a reply to message #419225] Thu, 20 August 2009 11:24 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Gentlebabu,

USED THE FOR UPDATE Clause:
Using the FOR UPDATE Clause in the MVIEW Definition, able to DELETE the records from MV_ABC but after REFRESH interval of 20 MINUTES it retains the DELETED value in the MV_ABC.

Please suggest:
i) How to permanently DELETE the records from MV_ABC, so after REFRESH interval of 20 MINUTES it should not retain the data but only the incremental data should be appended by its nature?

Used Query for Reference:

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; 

SELECT * FROM MV_ABC;

SELECT NAME, TABLE_NAME, UPDATABLE, REFRESH_METHOD, REFRESH_MODE FROM DBA_SNAPSHOTS;

/* Total Record Cound in BASE Table */
SELECT COUNT(1) FROM  ABC;  -- Record Count 11181

/* Total Record Cound in MVIEWS - MV_ABC */
SELECT COUNT(1) FROM MV_ABC;   -- Record Count 11181

/* There 33 Records for DID 'did10726'*/
SELECT * FROM MV_ABC WHERE DID='did10726';

DELETE FROM MV_ABC WHERE DID='did10726'; -- 33 Records are COMMIT;

/* After DELETE Operation Record Count in  MVIEWS - MV_ABC */
SELECT COUNT(1) FROM MV_ABC

-- Record Count 11148 

/* After DELETE Operation Total Record Cound in BASE Table */
SELECT COUNT(1) FROM  ABC;  

-- Record Count 11181
Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419337 is a reply to message #419238] Fri, 21 August 2009 06:07 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

/* After DELETE Operation Record Count in  MVIEWS - MV_ABC */
SELECT COUNT(1) FROM MV_ABC

-- Record Count 11148 

/* After DELETE Operation Total Record Cound in BASE Table */
SELECT COUNT(1) FROM  ABC;  

-- Record Count 11181


Good Practice. Please do the following action.

1. Can you trasfer/refresh data using 

dbms_mview_refresh(MV_ABC);

2. SELECT COUNT(1) FROM  ABC;  

3. SELECT COUNT(1) FROM  MV_ABC;  


Let us know.
Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419571 is a reply to message #419018] Mon, 24 August 2009 05:13 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Gentlebabu,

Thanks.....

Steps followed for Permanent Delete from MVIEWS:


-- STEP 1
CREATE MATERIALIZED VIEW LOG ON ABC;

-- STEP 2
CREATE MATERIALIZED VIEW MV_ABC
BUILD IMMEDIATE
REFRESH FAST
START WITH SYSDATE
NEXT  SYSDATE+5/(24*60)
FOR UPDATE
ENABLE QUERY REWRITE
AS  SELECT * FROM ABC
WHERE TMSTP > TO_DATE('20/08/2009 10:52:28','dd/mm/yyyy hh24:mi:ss');

-- STEP 3
DELETE FROM MV_ABC WHERE ROWNUM < 6;
COMMIT;

-- STEP 4
--SELECT UPDATE_LOG FROM DBA_SNAPSHOTS WHERE NAME='MV_ABC' AND UPDATABLE='YES';

DELETE FROM USLOG$_MV_ABC;
COMMIT;

5 RECORDS DELETED FROM THE MV_ABC permanently.



As I have tried and tested, it is deleting the records from MVIEWS but not from base table.

Query1:
Please suggest, 
Is there any automated concept exists in MVIEWS for Permanent delete.


Query2:
Create trigger on USLOG$_MV_ABC, its not executing in order to make DELETION operation automated,
Pls. suggest
CREATE OR REPLACE TRIGGER TRG_LOGDATA 
AFTER INSERT 
ON USLOG$_MV_ABC 
DECLARE 
PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN 
     DELETE FROM USLOG$_MV_ABC; 
     COMMIT; 
END TRG_LOGDATA;


Query3:
1. Can you trasfer/refresh data using 

dbms_mview_refresh(MV_ABC);



Pls Suggest,
Refresh Clause which is defined in MVIEWS definition with an time interval, Is it different from dbms_mview_refresh(MV_ABC)? If no, than it's for general practice only.



Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419579 is a reply to message #419571] Mon, 24 August 2009 06:12 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
As I have tried and tested, it is deleting the records from MVIEWS but not from base table.

Query1:

Please suggest,
Is there any automated concept exists in MVIEWS for Permanent delete.


Hello Mr,

Please find difference betweek BASIC and Advanced Replication.

In Basic replication only for read only access. If you want do any DML Action in your materialized view those changes NEVER comes in Base Table. It's one way data transfer

If updatable materialized view. Then go with Advanced Replication (Materialized View Based Replication)

Thanks
http://download.oracle.com/docs/cd/B19306_01/server.102/b14226/repoverview.htm#i14155

Please find Updatable/Read Only Materialized View Replication architecture.
Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419711 is a reply to message #419579] Tue, 25 August 2009 05:12 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Gentlebabu,

Thanks for the informative URL.

After going through the URL which was informative but not able to track the PERMENANT DELETE operation from MVIEWS.
As Updatable materialized view are meant for DML operation on MVIEWS but once the refresh happens it flsuhs the update log and reflect the it into the BASE TABLE, if DBMS_REPLICAT is used to sync data.

Pls. Suggest.
Re: Alternative for Complete Refresh / Delete records from MVIEWS [message #419776 is a reply to message #419711] Tue, 25 August 2009 09:55 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Hello Again,

I think still your not understanding difference between basic/advanced replication.

Quote:
Oracle Server supports two different forms of replication: basic and advanced replication.

Basic replication is implemented using standard CREATE SNAPSHOT or CREATE MATERIALIZED VIEW statements. It can only replicate data (not procedures, indexes, etc), replication is always one-way, and snapshot copies are read only.

Advanced replication supports various configurations of updateble-snapshot, multi-master and update anywhere replication. It is more difficult to configure but allows data and other database objects like indexes and procedures to be replicated.


Ref: http://www.orafaq.com/wiki/Advanced_Replication_FAQ#What_is_the_difference_between_BASIC_and_ADVANCED_replication.3F

>>As Updatable materialized view are meant for DML operation on MVIEWS but once the refresh happens it flsuhs the update log and reflect the it into the BASE TABLE, if DBMS_REPLICAT is used to sync data

Ok I agree; from where your creating updatable materialized view? in Basic Replication or Advanced Replication.

ASAIK; Your playing with basic replication not advanced replication.

Thanks

Previous Topic: Complete Transaction Statistics of MVIEWS on Base Table
Next Topic: I need to setup database replication with updatable materialized view
Goto Forum:
  


Current Time: Thu Mar 28 18:56:39 CDT 2024