Query Tuning - outer join

[ananthkt_sat]

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

[pando]

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?

[tamilselvan]

Post the SQL statement here.

Tamil

[ananthkt_sat]

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

[mike9]

please post the explain plan

[ananthkt_sat]

Is the delay is 'coz of the decodes in select st ?
Is there any other alternatives for that ?

[DaPi]

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'