Tuning

[programmer]

the following query takes 20 seconds to retrive one record. if there are more records it takes multiples in time. how to restructure so that the time is reduced.
all the fields used in the where condition is indexed.
suggestion will be very much appreciated.

SELECT a.row_id, a.name, a.master_ou_id,
d.addr, d.city, d.state, d.zipcode,
d.country, j.attrib_04, p.prod_catg_cd,
j.x_attrib_104, a1.x_attrib_112,
a1.x_attrib_102, p.divn_cd, aa.name,
a.ou_type_cd, j.attrib_34, p.prod_cd,
a1.x_attrib_104, t.x_esid,
a1.x_attrib_102,
a1.attrib_28,
a1.attrib_29, a1.attrib_10,
a1.x_attrib_113, f.serv_accnt_id,
a.ou_type_cd,
u.val
FROM fa.s_org_ext a,
fa.s_org_ext aa,
fa.s_org_ext_x a1,
fa.s_org_ext_utx o,
fa.s_quote_soln f,
fa.s_con_addr c,
fa.s_addr_per d,
fa.s_prod_int p,
fa.s_prod_int_x j,
fa.s_quote_soln_x t,
fa.s_lst_of_val u
WHERE f.inv_accnt_id = '1-8YAY'
AND f.asset_id = '1-BYRQ'
AND c.addr_type_cd(+) = 'Service'
AND a.row_id = a1.par_row_id(+)
AND a.row_id = f.serv_accnt_id
AND a.row_id = o.par_row_id
AND aa.row_id = o.x_tdsp_id(+)
AND a.row_id = c.accnt_id(+)
AND c.addr_per_id = d.row_id(+)
AND f.prod_id = p.row_id
AND f.row_id = t.par_row_id
AND p.row_id = j.par_row_id(+)
AND a1.attrib_44 = u.row_id(+)
/
Thanks

/
-

[kmesser]

Eleven tables,
Seven outer joins,
and a partridge in a pear tree!

What are these data sources? Are they all tables? How many rows in each table? Have you analyzed them to create/update statistics?

[programmer]

yes all are tables

[parijat]

Tell me the Indexes on each table, with column name, column position, and table name and I can do wonders for you here.

Parijat.

[kmesser]

Don't forget the rest of my questions from above . . .

thanks