you are a new oracle dba. you want to know about materialized views and how you can create them. you also would like to know how materialized views are important for database performance tuning.you would like to know some examples on how to create materialized views.
Below article contains a complete reference on materialized views for the new oracle dba.
View is stored sql statement which would execute when ever you query on it, and it will query the base tables directly, On the other hand Materialized View, actually its a whole big topic, the key features are, it create a physical table with its own storage, its an advance form of snapshot, it could be refreshed when ever user query it, when ever the data gets commit in base tables or manually, if the user have QUERY REWRITE privilage and the materialized view has been made for getting aggrigate result in the same schema with the query rewrite option, and if the user’s select statement could get the same kind of result that you have in the materialized view then even though user is selecting base tables, but Oracle will get the result from the materialzed view.
For more information consult the following link.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#42135
CREATE MATERIALIZED VIEW mv_simple_sri
TABLESPACE ts_users
BUILD IMMEDIATE
REFRESH force ON demand AS
SELECT * FROM sri_test;
select * from dba_users where username=user
select * from dba_objects where owner=’KADIYAL’
create table sri_test(a number , b number);
alter table sri_test add primary key(a )
insert into sri_test
select rownum,rownum+100 from dba_objects where rownum < 10000
commit;
BEGIN
Exec dbms_mview.refresh(‘minddba.MV_RAS_HIERARCHY’);
END;
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV');
select * from dba_mviews
select query from dba_mviews where mview_name = ‘ECLAIM_REF_SAP’ and owner = ‘EOI’
SELECT table_name,master,last_refresh FROM ALL_SNAPSHOTS WHERE OWNER=’EOI’ ORDER BY LAST_REFRESH;
select OWNER,MVIEW_NAME,UPDATABLE,STALENESS,COMPILE_STATE from dba_mviews
where compile_state=’NEEDS_COMPILE’
Please find below
1)to find out all snap shot sites (other databases that refer to our c_history table )
select * from DBA_REGISTERED_snapshots where name like ‘%HISTORY%’
2) Next in the output from above query please verify in the column snapshot_site the snap shot sites and populate the value
in below query.
To unregister please run each of these blocks individually in toad
begin
dbms_mview.unregister_snapshot(‘CON’,’C_HISTORY’,’TRT.WORLD’);
end;
begin
dbms_mview.unregister_snapshot(‘CON’,’C_HISTORY’,’WAFD.COM’);
end;
begin
dbms_mview.unregister_snapshot(‘CON’,’C_HISTORY’,’DRB.COM’);
end;
to find out materialized views in current database where I pass the table name
declare
p_get_dep_mv varchar2(4000);
begin
dbms_mview.get_mv_dependencies(‘con.c_history’, p_get_dep_mv);
dbms_output.put_line(p_get_dep_mv);
dbms_output.put_line(‘sridhar’);
end;
–created the primary key on the dummy table
create materialized view log on dummy with primary key;
create materialized view MV_F_REAL
on prebuilt table
refresh complete
as select * from dummy
select * from MV_F_REAL
select * from user_objects where object_name like ‘%MV%’
drop table mv_f_real
drop
Please find below the detailed syntax for materialized views from psoug.org.
Oracle Materialized Views | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Version 10.2 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Contact Us ? Legal Notices and Terms of Use ? Privacy Statement |