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.
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.