Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need help creating a Materialized View

 
Abigail Moore
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
This query is taking a very long time to run, someone suggested I make a materialized view of this sql query, can someone help me get started. Thanks I appreciate it!

SELECT *
FROM (SELECT ROW_NUMBER () OVER (ORDER BY d1.call_dt,
d1.call_time,
d2.hierarchy_level_6_nm,
d2.hierarchy_id,
d1.primary_station,
d1.display_ind,
d1.display_station,
d1.bill_date DESC,
d1.detl_provider,
d1.format_sln_grp,
d1.detl_refnum,
d1.call_desc) AS row_nbr,
d1.plan_cd, d1.display_type_cd, d1.from_place,
d1.detl_provider, d1.call_dt, d1.call_juris_cd, d1.detl_qty,
d1.detl_auth_cd, d1.detl_billed_for, d1.call_type_2,
d1.amount, d1.detl_charge, d1.display_station,
d1.report_month, d1.pob2, d1.to_nmbr, d1.IDENTIFIER,
d1.detl_refnum, d2.hierarchy_level_6_nm AS hierarchy_nm,
d1.display_ind, d1.detl_rate, d1.call_time, d1.bill_date,
d1.ban_lbl_txt, d1.call_type, d1.to_place,
d2.hierarchy_level_6_desc AS hierarchy_desc, d1.from_nmbr,
d1.detl_disc_amt, d1.detl_calling_plan_ind, affiliate_cd
FROM ebat.rp_usage_det_763 d1 INNER JOIN ebat.rp_station_hierarchy_master_mv d2 ON d1.customer_id =
d2.customer_id
AND d1.primary_station =
d2.primary_station
AND d1.primary_type_cd =
d2.primary_type_cd
AND d1.report_month =
d2.report_month
AND d2.hierarchy_type_cd =
'R'
WHERE d1.customer_id = '9999999999'
AND d1.report_month = 200510
AND d1.super_type_2 = 'USG-DETL'
AND (detl_auth_cd IS NOT NULL OR hier_lbl_nm IS NOT NULL)
ORDER BY row_nbr)
WHERE row_nbr BETWEEN 1 AND 2501
 
Abigail Moore
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I found out that I have to ask a DBA to do this. Thanks!
 
Reid M. Pinchback
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are going to be talking to a DBA anyways, you may try asking him if he knows how to generate and interpret explain plans. This may be less an issue of a materialized view, and more an issue of controlling query processing. Materialized views can be used to speed up performance, but tend to be better when the reason for the performance problem has to do with how you distribute your databases. Definitely not a good idea if your query is looking at transactional data which is changing frequently.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic