Q

Creating materialized view for a query

Thanks for replying to my question. I tried your query.
1) Item group is not in mst_month_summary
---------------------------------------
OLD QUERY
---------
Select
 (sum(nvl(nu_year_open_balance,0)*nvl(b.nu_eval_rate,0)))
   from
 mst_item b,
  mst_month_summary a
  where
   b.vc_comp_code = :comp_code  and
   a.vc_comp_code=b.vc_comp_code and
   a.vc_item_code=b.vc_item_code and
   nvl(a.nu_year_open_balance,0)<>0 and
    b.vc_item_group=:item_group and
    dt_fin_start_date=:start_date
---------------------------------------
NEW QUERY
---------
Select
  (sum(nvl(nu_year_open_balance,0)*nvl(b.nu_eval_rate,0)))
    from
  mst_item b,
   mst_month_summary a
   where
 a.vc_comp_code = :comp_code
    b.vc_item_group=:item_group 
    dt_fin_start_date=:start_date
    b.vc_comp_code=a.vc_comp_code and
    b.vc_item_code=a.vc_item_code and
    nvl(a.nu_year_open_balance,0)<>0

I set the autotrace on and then fire both the queries but the result is exactly the same... consistent get, physical reads, etc. are exactly the same. How about creating materialized view for this query? I tried but I'm not able to do it because I am not proficient in making materialized views. I hope you can help. Okay, I do not know whether you are using Oracle8i or Oracle9i. In either case, you may want to create materialized view...

logs on the source tables as follows:

1) CREATE MATERIALIZED VIEW LOG ON MST_ITEM;
2) CREATE MATERIALIZED VIEW LOG ON MST_MONTH_SUMMARY;

For Oracle8i you may want to create a refresh schedule (every hour etc.). If you need the data real time on the MATERIALIZED VIEW you may have to write triggers on MST_ITEM and MST_MONTH_SUMMARY table and create an updateable materialized view. Now, you will notice that I have taken out the parameters from the materialized views. It is because, once the MATERIALIZED VIEW is created and the refresh schedule is set to every hour, then you will query the MV. I will recommend that you create an index on the materialized view on VC_COMP_CODE, VC_ITEM_GROUP and DT_FIN_START_DATE.

Once the logs are created, then you can create the MATERIALIZED VIEW as follows:

CREATE MATERIALIZED VIEW MV_MONTH_SUMMARY
REFRESH FAST NEXT SYSDATE+1/24
AS
SELECT a.vc_comp_code, b.vc_item_group, dt_fin_start_Date,
  (sum(nvl(nu_year_open_balance,0)*nvl(b.nu_eval_rate,0)))
    FROM 
  mst_item b,
   mst_month_summary a
  WHERE
     b.vc_comp_code=a.vc_comp_code and
    b.vc_item_code=a.vc_item_code and
    nvl(a.nu_year_open_balance,0)<>0
GROUP BY A.VC_COMP_CODE, B.VC_ITEM_GROUP, DT_FIN_START_DATE;

If you are using Oracle9i then you can replace the

REFRESH FAST NEXT SYSDATE+1/24 with REFRESH ON COMMIT and the data in the MATERIALIZED VIEW will be real time.

This was first published in June 2004
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close