Home » RDBMS Server » Performance Tuning » View Performance tunning (DB2)
View Performance tunning [message #324789] Wed, 04 June 2008 00:38 Go to next message
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
i am getting lot of performane problem in this view .. specially by inner query.
create view adw.com_item_hier_suv as 

select item_hier.com_item_seq_i, item_hier.ent_co_c, item_hier.ent_co_sub_c, item_hier.dept_i,item_hier.class_i, item_hier.sbcl_i, item_hier.item_i, item_hier.com_item_type_c, item_hier.com_item_crte_d, item_hier.com_item_desc_t, item_hier.com_item_stat_c, item_hier.item_actv_d, item_hier.com_item_src_del_f, item_hier.dir_class_seq_i, item_hier.dir_sbcl_seq_i, item_hier.dir_item_i, item_hier.dir_cost_a, item_hier.dir_vend_seq_i, item_hier.src_sys_c, item_hier.sbcl_seq_i, item_hier.styl_seq_i, item_hier.item_colr_seq_i, item_hier.item_ptrn_seq_i, item_hier.mdse_prslt_seq_i, item_hier.item_size_seq_i, item_hier.crte_d, item_hier.crte_ti,
from adw.com_item_hier_perf item_hier 
left outer join (select  dept.div_seq_i, dept.dept_i  from adw.dept dept  join
 (select dept_i,max(eff_d) dp_eff_d from adw.dept group by dept_i) curr_dept_i   on  dept.dept_i=curr_dept_i.dept_i    and                      dept.eff_d=curr_dept_i.dp_eff_d   ) latest_dept on item_hier.dept_i=latest_dept.dept_i left outer join 
(select div.mdse_grp_seq_i,div.div_i,div.div_n,div.div_seq_i,div.eff_d	from adw.div div
(select div_i,max(eff_d) di_eff_d from adw.div group by div_i) curr_div_i on div.div_i = curr_div_i.div_i and
div.eff_d= curr_div_i.di_eff_d) latest_div  on latest_dept.div_seq_i=latest_div.div_seq_i
left outer join
(select mdse.mdse_grp_i, mdse.mdse_grp_seq_i,mdse.mdse_grp_n from adw.mdse_grp mdse join
(select mdse_grp_i,max(eff_d) m_eff_d from adw. mdse_grp group by mdse_grp_i) curr_mdse_grp_i
on mdse. mdse_grp_i = curr_mdse_grp_i.mdse_grp_i  and mdse.eff_d= curr_mdse_grp_i.m_eff_d) latest_mdse
on latest_div.mdse_grp_seq_i=latest_mdse.mdse_grp_seq_i
Re: View Performance tunning [message #324791 is a reply to message #324789] Wed, 04 June 2008 00:41 Go to previous messageGo to next message
Messages: 24963
Registered: January 2009
Location: SoCal
Senior Member
Please read & follow Posting Guidelines as stated in URL above.

what you posted is unreadable!
Re: View Performance tunning [message #324792 is a reply to message #324789] Wed, 04 June 2008 00:44 Go to previous message
Michel Cadot
Messages: 63917
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, this is an Oracle forum, your title says DB2.

Previous Topic: difference between db block gets and consistent gets (merged)
Next Topic: Performance issue with Oracle Cluster
Goto Forum:

Current Time: Tue Oct 25 00:34:28 CDT 2016

Total time taken to generate the page: 0.08745 seconds