Tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Tuning

  1. #1
    Join Date
    Jun 2001
    Posts
    45
    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

    /
    -

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    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?

    Oracle DBA and Developer

  3. #3
    Join Date
    Jun 2001
    Posts
    45
    yes all are tables

  4. #4
    Join Date
    Jun 2001
    Posts
    3
    Tell me the Indexes on each table, with column name, column position, and table name and I can do wonders for you here.

    Parijat.

  5. #5
    Join Date
    Aug 2000
    Posts
    462
    Don't forget the rest of my questions from above . . .

    thanks
    Oracle DBA and Developer

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