Materialized View

Here is a query which takes a long time because of the join:

SELECT a13.PT_CODE PT_CODE,
a16.PT_NAME PT_NAME,
a15.SCIENTIFICGROUPCODE SCIENTIFICGROUPCODE,
a15.SCIETNIFICGROUPNAME SCIENTIFICGROUPNAME,
count(distinct a11.SAFETYREPORTKEY) WJXBFS1
from DWH.F_SAFETYREPORT a11
join DWH.F_ADVERSEREACTION a12
on (a11.SAFETYREPORTKEY = a12.SAFETYREPORTKEY)
join DWH.M_LLT_PT_61_CUR1_VW a13
on (a12.REACTIONMEDDRALLT = a13.LLT_CODE)
join DWH.C_INDEX_SCIENTIFIC a14
on (a12.PRODUCTINDEXCODE = a14.PRODUCTINDEXCODE)
join DWH.H_SCIENTIFICPRODUCT a15
on (a14.SCIENTIFICPRESENTATIONID = a15.SCIENTIFICPRESENTATIONID)
join DWH.H_MD61_PSFGY_VW a16
on (a13.PT_CODE = a16.PT_CODE)
where (a11.CASEVALIDFROM <= To_Date('09-06-2006', 'dd-mm-yyyy') and a11.CASEVALIDTO > To_Date('09-06-2006', 'dd-mm-yyyy')
and a11.CLASSIFICATION not in (4)
and a15.SCIENTIFICPRODUCTID in (20923))
group by a13.PT_CODE,
a16.PT_NAME,
a15.SCIENTIFICGROUPCODE,
a15.SCIETNIFICGROUPNAME

To use Materialized View, this is how we should create a Materialized View:

CREATE MATERIALIZED VIEW mv_scigroup_pt
build immediate
refresh on DEMAND
enable query rewrite
AS
select a13.PT_CODE PT_CODE,
a16.PT_NAME PT_NAME,
a15.SCIENTIFICGROUPCODE SCIENTIFICGROUPCODE,
a15.SCIETNIFICGROUPNAME SCIENTIFICGROUPNAME,
a11.casevalidfrom,
a11.casevalidto,
a15.scientificproductid,
a11.safetyreportkey
from DWH.F_SAFETYREPORT a11
join DWH.F_ADVERSEREACTION a12
on (a11.SAFETYREPORTKEY = a12.SAFETYREPORTKEY)
join DWH.M_LLT_PT_61_CUR1_VW a13
on (a12.REACTIONMEDDRALLT = a13.LLT_CODE)
join DWH.C_INDEX_SCIENTIFIC a14
on (a12.PRODUCTINDEXCODE = a14.PRODUCTINDEXCODE)
join DWH.H_SCIENTIFICPRODUCT a15
on (a14.SCIENTIFICPRESENTATIONID = a15.SCIENTIFICPRESENTATIONID)
join DWH.H_MD61_PSFGY_VW a16
on (a13.PT_CODE = a16.PT_CODE)
where a11.CLASSIFICATION not in (4)

In this case the where clause is common to all queries and the other where clauses are removed and the columns used in the select statement. Once the Materialized view is created, if you run an explain plan for the query, it should use the Materialized View instead of the underlying tables.

You can drop the Materialized View if you dont want the plan to use it or you can do this:
ALTER MATERIALIZED VIEW mv_scigroup_pt DISABLE QUERY REWRITE

to analyze the MV, you treat it as a table:

Analyze table mv_scigroup_pt compute statistics;

To put indexes on the Materialized View, again treat it as an index on an ordinary table:
CREATE INDEX mv_srid_idx ON mv_scigroup_pt(safetyreportkey)

and analyze the index in the usual way.

In this example we have used on demand clause to Refresh the Materialized View. In order to refresh it, say for a DWH, use the dbms_mview package at the end of the ETL process.
See:
http://www.lc.leidenuniv.nl/awcourse/oracle/appdev.920/a96612/d_mview2.htm#94135