sql tuning

[lee]

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.

[nishant]

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.

[lee]

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.