Home » Server Options » Replication » Truncating Materialized Views
Truncating Materialized Views [message #75607] Wed, 26 January 2005 14:10 Go to next message
Shridhar Garge
Messages: 1
Registered: January 2005
Junior Member
Hi,

I want to use Materialized Views to create summary of multiple tables. I will be using the data in the Materialized View only for a specific time period. After that I wish to delete all the rows from the view and refresh it again when the process starts next day.

Can I use the usual TRUNCATE statement to delete all the rows from the Materialized View ??

Please let me know.

Thanks in Advance,
Shridhar
Re: Truncating Materialized Views [message #75610 is a reply to message #75607] Tue, 01 February 2005 21:07 Go to previous message
Sreedhar Reddy
Messages: 55
Registered: January 2002
Member
Materialized views contains view statment and data...

if you want to referesh the materiailized view at a particular time on each day.

while creating materialized view itself you can state in with in the create materialized view statement.

You have to set parameters
query_rewrite_enabled string TRUE
query_rewrite_integrity string STALE_TOLERATED

you need to create snapshots.

For example

CREATE MATERIALIZED VIEW all_emps
PCTFREE 5 PCTUSED 60
TABLESPACE users
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH START WITH ROUND(SYSDATE + 1) + 11/24
NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')) + 15/24
AS SELECT * FROM fran.emp@dallas
UNION
SELECT * FROM marco.emp@balt;

Oracle automatically refreshes this materialized view tomorrow at 11:00 a.m. and subsequently every Monday at 3:00 p.m.. The default refresh method is FORCE. all_emps contains a UNION operator, which is not supported for fast refresh, so Oracle will automatically perform a complete refresh.
Previous Topic: Replication between MS SQL Server and Oracle
Next Topic: oracle 9i replication
Goto Forum:
  


Current Time: Thu Mar 28 04:40:38 CDT 2024