Query Tuning - outer join
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Query Tuning - outer join

  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Query Tuning - outer join

    Hi,
    I have a base table with 10,000 rows.The base table columns will store 8 diff IDs.To display with the description i'm doing outer joining with 8 tables.
    In that 8 one table contains nearly 4 lakhs rows & rest of the tables contains some 100 rows.
    My query takes nearly 20 min. to execute. HOw to tune that.

    As want tune this with in half-day,pls try send some tips & suggestions imm.

    Thanks in advance

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448

    Re: Query Tuning - outer join

    Originally posted by ananthkt_sat


    As want tune this with in half-day,pls try send some tips & suggestions imm.

    Thanks in advance
    oh and asking for time limit huh?

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Post the SQL statement here.

    Tamil

  4. #4
    Join Date
    Aug 2004
    Posts
    4
    Hi,
    Thanks for the responce.
    Here is the query .


    select fi.fin_instrm_id,
    bcel.row_id,
    decode(upper(bcel.value),
    'PRIMEXCHCD', decode(fi.prim_stk_exch_id , null, '', pe.exch_cd),
    'CRNCY', decode(fi.prim_rept_ccy_id, null,'',c.iso3_cd),
    'CRNCYNAME', decode(fi.prim_rept_ccy_id, null,'',c.long_nm),
    'PRICEDATE', decode(fim.value_dt, null,'',to_char(fim.value_dt,'DD Mon YYYY')),
    'PRICECRNCY', decode(fim.uom_id, null,'', u.nm), --Code added by Satyam Team for PRICECRNCY - Release 3.2
    'OPTEXCHCD', decode(fi.prim_opt_exch_id,
    0, decode(fi.prim_stk_exch_id, null, '', pe.exch_cd),
    null, decode(fi.prim_stk_exch_id, null,'', pe.exch_cd),
    oe.exch_cd),
    'PRIMEXCHNM', decode(fi.prim_stk_exch_id, null, '', pe.exch_nm),
    'OPTEXCHNM', decode(fi.prim_opt_exch_id,
    0, decode(fi.prim_stk_exch_id, null,'', pe.exch_nm),
    null, decode(fi.prim_stk_exch_id, null, '', pe.exch_nm),
    oe.exch_nm),
    'CVRG', ct.descr,
    'TYPE', fit.nm,
    'STATUSREVW', decode(fi.revw_in,'X','Under extended review',
    'Y','Under review',
    'N','',
    ''),
    'STATUSRSTR', decode(fi.rstrct_in,'Y','Restricted',
    'N','',
    ''),
    'STATUSRPT', decode(ct.nm,'ON','On',
    'OFF','Off',
    'HOLD','Hold',
    ''),
    'IQED', decode(fi.iq_in, 'Y', 'iQed', 'Non-iQed'),
    'PRIMARYTICKER', fi.prim_stk_in, --Code added by Satyam Team for prim_stk_in
    'PARENTTICKER', decode(fi_assoc.prim_fin_instrm_id,null,'',fi_assoc.parent_ticker),
    'PARENTTICKERSTATUS', decode(fi_assoc.secnd_fin_instrm_id,null,'Y','N'),
    'ADRRATIO',fi.adr_ratio,
    'TEMPLATEID',template.block_id,
    '') value
    from block_typ bt,
    block b,
    block_cell bcel,
    block_col bcol,
    attrib_typ att,
    col_attrib_level cal,
    fin_instrm fi,
    --feed_instrm_measr fim, - fin_instrm_measr fim, - (
    select fia.secnd_fin_instrm_id, fia.prim_fin_instrm_id, fis.fin_instrm_symbol_upper parent_ticker --, fia.adr_ratio
    from fin_instrm_assoc_v fia, fin_instrm_symbol fis, class c, obj_typ ot
    where fia.prim_fin_instrm_id = fis.fin_instrm_id(+)
    and fis.class_id = c.class_id
    and c.shrt_nm = 'TICKER'
    and c.ent_typ_id = ot.obj_typ_id
    and fis.actv_in = 'Y'
    --and fia.actv_in = 'Y'
    and upper(ot.nm) = 'INSTRUMENT'
    ) fi_assoc,
    block template,
    exch pe,
    exch oe,
    ccy c,
    uom u, -- covrg_typ ct,
    fin_instrm_typ fit
    where bt.nm = 'IQEXCEL'
    and b.block_typ_id = bt.block_typ_id
    and b.nm = 'EQX'
    and b.block_id = bcel.block_id
    and bcol.block_id = bcel.block_id
    and bcol.col_id = bcel.col_id
    and bcol.block_id = cal.block_id
    and bcol.col_id = cal.col_id
    and cal.attrib_typ_id = att.attrib_typ_id
    and att.src_obj_nm = 'IQEXCEL'
    and att.src_proprty_nm = 'MNEMONIC'
    and pe.exch_id (+)= fi.prim_stk_exch_id
    and oe.exch_id (+)= decode(fi.prim_opt_exch_id,0,fi.prim_stk_exch_id,
    null,fi.prim_stk_exch_id, fi.prim_opt_exch_id)
    and fi.prim_rept_ccy_id = c.ccy_id(+)
    and ct.covrg_typ_id (+)= fi.covrg_typ_id
    and fit.fin_instrm_typ_id (+)= fi.leaf_fin_instrm_typ_id
    and fi.fin_instrm_id = fim.fin_instrm_id(+)
    and fim.mt_id (+)= v_nprice_mt_id
    and fim.uom_id = u.uom_id (+) and fi.fin_instrm_id = fi_assoc.secnd_fin_instrm_id(+)
    and bcel.value not in ('LASTSUBMITDT', 'CURRSUBTYPE', 'LASTAPPSUBTYPE')
    and fi.fin_instrm_id = template.fin_instrm_id(+)
    and fi.issr_id = template.issr_id(+)
    order by 1,2;


    Please suggest me

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    please post the explain plan

  6. #6
    Join Date
    Aug 2004
    Posts
    4
    Is the delay is 'coz of the decodes in select st ?
    Is there any other alternatives for that ?

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Does this run? Perhaps there's some cut & paste errors:
    --feed_instrm_measr fim, - fin_instrm_measr fim, - (
    select fia.secnd_fin_instrm_id, fia.prim_fin_instrm_id,
    . . . .
    uom u, -- covrg_typ ct,
    Does it give the right result? The following will NOT give an outer join since deficient rows in fis will have NULL columns:
    where fia.prim_fin_instrm_id = fis.fin_instrm_id(+)
    and fis.class_id = c.class_id
    . . . .
    and fis.actv_in = 'Y'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width