sql tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: sql tuning

  1. #1
    Join Date
    Feb 2000
    Posts
    142
    How do I make an update query run faster? When I saw the explain plan, it was doing a ful table scan.

    What are the options? Please explain as this is urgent.

    Thanks.



  2. #2
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    Hi,
    Please paste the result from Plan Table and the query here.
    Are you using select statement to update it.Please check the joins and the indexes.
    take care,
    Nishant.

  3. #3
    Join Date
    Feb 2000
    Posts
    142
    Following is the query:


    update PREFORMAT A set A.BENE_REF = (select b.INDV_CO_ID from ACH_PREFMT b where a.clnt_id = b.clnt_id
    and a.prefmt_cod = b.prefmt_cod
    and a.prefmt_sta_typ = b.prefmt_sta_typ)
    where exists (select 'x' from PREFORMAT c
    where c.proc_loca in ('920','930','940') and
    c.tran_typ IN ('ACH', 'ACHI')
    and a.clnt_id = c.clnt_id
    and a.prefmt_cod = c.prefmt_cod
    and a.prefmt_sta_typ = c.prefmt_sta_typ
    and c.BENE_REF IS NULL)


    The plan is as follows:
    UPDATE STATEMENT COST =134
    2.1 UPDATE - PREFORMAT
    3.1 FILTER
    4.1 TABLE ACCESS (FULL) - PREFORMAT
    4.2 TABLE ACCESS (BY INDEX ROWID) - PREFORMAT
    5.1 INDEX (UNIQUE SCAN) - PK_PREFORMAT(UNIQUE)
    2.2 TABLE ACCESS(BY INDEX ROWID) - ACH_PREFMT
    3.1 INDEX(UNIQUE SCAN) - PK_ACH_PREFMT(UNIQUE)



    Please explain ASAP. It's urgent!!

    Thanks.

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