OraFAQ Forum: Performance Tuning » Nasty query re-write

Home » RDBMS Server » Performance Tuning » Nasty query re-write (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Nasty query re-write [message #329224]

Tue, 24 June 2008 10:39

svguerin3
Messages: 44
Registered: November 2005
Location: TX

Member

Ok, just a forewarning that this query is huge, ugly and horribly built.. Please note that I did not write it; it was unfortunately "passed down" to our team at work. I have been working hard at re-writing this thing, and I've made some headway thanks to great help from these forums. However, the query is still not up to speed, so I figured instead of breaking it up and asking questions about each individual part, I'd go ahead and post the whole thing.

I would never expect anyone to re-write this horrible thing for me, but any words of wisdom for helping consolidate this thing into something that might perform better would be VERY much appreciated. Some initial background about the tables:

t_fbsos_offr_stg: 40-50 million rows
t_fbsco_chan_ord: 80-100 million rows
These 2 tables are joined by "ord_id" field, and there are typically 2 entries on t_fbsco_chan_ord for each entry on t_fbsos_offr_stg. Primary key on fbsos is "ord_id" and primary key on fbsco is "ord_id,seq_n".

The "Beast" Query:

SELECT 
   os.ord_id, os.cpgn_c, os.run_id, os.part_id, 
   os.client_id_n, os.plan_n, os.prod_id_c, 
   os.cpgn_st_c, os.fesco_offr_id, os.exec_mode_c, 
   os.schd_dlvr_d, os.dlvr_mode_c, os.cell_c, 
   os.insrt_bck_slp_i, os.nigo_i, os.orig_c, 
   os.crea_corp_id, os.mod_corp_id, os.sel_d, 
   os.lst_mod_d, os.crea_src_c, os.offr_c, 
   os.dflt_fprs_kit_c, os.prty_n, os.run_ops_seq_n, 
   os.run_ops_chan_x, os.fprs_rules_c, 
   os.fprs_lgc_flg_c, os.admin_x, os.aprv_nm, 
   os.brgn_unt_c, os.ben_grp_id, os.call_ctr_nm, 
   os.call_ctr_tele_n, os.client_id_logo_x, 
   os.client_nm, os.cfrm_id, os.evnt_d, os.evnt_nm, 
   os.evnt_t, os.frm_addr_nm, os.fst_nm, os.mid_nm, 
   os.lst_nm, os.life_evnt_wndw_beg_d, 
   os.life_evnt_wndw_end_d, os.msg_cntnt_x, os.psswd_x, 
   os.plan_id_logo_x, os.plan_list_html_x, 
   os.plan_list_x, os.pref_plan_nm, os.proj_nm, 
   os.room_loc_x, os.shrt_plan_nm, os.site_nm, 
   os.smrt_urls_x, os.smrt_urls_html_x, os.user_def1_x, 
   os.user_def2_x, os.user_def3_x, os.user_def4_x, 
   os.user_def5_x, os.user_def6_x, os.user_def7_x, 
   os.user_def8_x, os.user_def9_x, os.user_def10_x, 
   os.user_def11_x, os.user_def12_x, os.user_def13_x, 
   os.user_def14_x, os.user_def15_x, os.user_def16_x, 
   os.user_def17_x, os.user_def18_x, os.user_def19_x, 
   os.user_def20_x, '' AS cpgn_ttl_nm, osc.seq_n, 
   osc.chan_type_c, osc.actv_chan_i, 
   osc.chan_cntnt_key_type_c, osc.cntnt_key_val1_c, 
   osc.cntnt_key_val2_c, osc.email_addr_x, 
   osc.line_1_ad_x, osc.line_2_ad_x, osc.line_3_ad_x, 
   osc.city_nm, osc.st_c, osc.ctry_nm, osc.po_code_x, 
   osc.frgn_addr_i, osc.prvn_x, osc.ctgy_c, 
   osc.pref_ctgy_desc_x, osc.rsvp_c, osc.is_dflt_pref_i, 
   osc.pref_sel_i 
       FROM t_fbsos_offr_stg os 
          LEFT OUTER JOIN (SELECT /*+ NO_MERGE(OSC1) ORDERED FULL(OSC1) INDEX(OSC2) USE_NL(OSC1,OSC2) +*/ 
         osc2.ord_id,osc2.seq_n,osc2.chan_type_c, 
         osc2.actv_chan_i,osc2.chan_cntnt_key_type_c, 
         osc2.cntnt_key_val1_c,osc2.cntnt_key_val2_c, 
         osc2.email_addr_x,osc2.line_1_ad_x,osc2.line_2_ad_x,                
         osc2.line_3_ad_x,osc2.city_nm,osc2.st_c, 
         osc2.ctry_nm,osc2.po_code_x,osc2.frgn_addr_i, 
         osc2.prvn_x,osc2.ctgy_c,pc.pref_ctgy_desc_x, 
         osc2.rsvp_c,osc2.is_dflt_pref_i,osc2.pref_sel_i 
           FROM (select /*+                                                    
                    parallel(os,8,1)                                                    
                    parallel(co,8,1)                                                     
                 +*/                                                                 
                co.ord_id, max(co.seq_n) 
                 keep(dense_rank first order by co.actv_chan_i 
                      desc,co.seq_n desc) seq_n                                                                 
                 from t_fbsos_offr_stg os,
                      t_fbsco_chan_ord co 
                 where co.ord_id = os.ord_id
                 and os.run_id = :RUN_ID
                 group by co.ord_id) osc1,                          
                t_fbsco_chan_ord osc2 
            LEFT OUTER JOIN s_fbpc pc ON osc2.ctgy_c = pc.pref_ctgy_c 
             WHERE osc1.ord_id = osc2.ord_id 
             AND osc1.seq_n = osc2.seq_n) osc ON os.ord_id = osc.ord_id 
        WHERE os.run_id = :RUN_ID
        ORDER BY os.dlvr_mode_c, os.ord_id

Thanks in advance!! Please let me know of any other info that would be useful (i.e. explain plans, index info, etc). FYI we DO have the ability to ask our DBA's to add additional indexes if needed.

[Mod-edit: Frank added code-tags to improve readability]

[Updated on: Tue, 24 June 2008 11:59] by Moderator