| Moving data using Materialsed Views - PLEASE HELP ASAP!!! [message #75032] |
Wed, 21 March 2001 06:12  |
Bryn Ellis
Messages: 2
Registered: March 2001
|
Junior Member |
|
|
I have a production database which has 3 yrs of data in it of which 2 yrs never get touched apart from reporting. I'm going to move all 3 yrs to a separate reporting system and remove 2 yrs worth from the production system. I still want to keep the reporting system up to date with production so I'm going to use materialised views to do this. The refresh will happen every night. I will also delete anything more than 1 yr old from the production system but don't want the deletion to be replicated by the MV's to the reporting system. Do I have to drop the MV's before performing the delete and then recreate them afterwards or is there some way of diabling the MV's before doing the delete and then enabling them again afterwards? Maybe there is a better way of doing this altogether? Your help would be appreciated ASAP. Thanks.
----------------------------------------------------------------------
|
|
|
|
| Re: Moving data using Materialsed Views - PLEASE HELP ASAP!!! [message #75037 is a reply to message #75032] |
Fri, 13 April 2001 08:41  |
anjan
Messages: 9
Registered: February 2001
|
Junior Member |
|
|
hi,
Oracle8i maintains the Materialized Views so that data changes to a basetable are automatically propagated into the Materialized View. The administrator can define the type and timing of refreshes to be performed. Again it is suggestable to create Materialized View as Diferred like:
>CREATE Materialized View MY_PROJECT
STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0)
TABLESPACE XXX
NOLOGGING
PARALLEL
BUILD DIFERRED
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS........ >
Diferred creates Materialized View defination in the data dictionary and creates the necessary tables but doesnot populate it with data until the first refresh. ( remember the diferred clause affects only the initial population and for the rest of it's lifetime this caluse has no effect.) But at this moment Materialized View will not only automate the refresh of the various tables of each dimension or fact tables but will also utilize the query rewrite feature of CBO(cost based optimizer). this feature automatically and transperently redirect any queries written against the fact or dimension tables to instead execute when needed.
use the dbms_mview.refresh_all_mviews to refresh all Materialized View that have 'stale' data following a data load or any deletion into one or more source tables.
NB: 1. check alert log and .trc files
2. your query_rewrite_enabled should be set to true.
at session lavel type: alter session set query_rewrite_enabled=true.
3.query_rewrite_integrity can be set to enforced (default).
4. check necessary priviledges/permissions like:
create materialized view, query rewrite, global query rewrite..
point: you dont have to drop the Materialized View before performing the delete. if your optimizer is (CBO) is working fine then it will automatically populate/refresh when necessary(i mean at the time you've defined.. in your case every night.)
let me know if it works.
regards,
anjan saikia.
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
