| Truncating Materialized Views [message #75607] |
Wed, 26 January 2005 14:10  |
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  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
