Alternative for Complete Refresh / Delete records from MVIEWS [message #419018] |
Wed, 19 August 2009 10:47 |
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 #419164 is a reply to message #419035] |
Thu, 20 August 2009 03:55 |
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 #419238 is a reply to message #419225] |
Thu, 20 August 2009 11:24 |
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 #419571 is a reply to message #419018] |
Mon, 24 August 2009 05:13 |
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 #419711 is a reply to message #419579] |
Tue, 25 August 2009 05:12 |
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.
|
|
|
|