update statement tune

From: raja rao <raja4list_at_yahoo.com>
Date: Wed, 26 Oct 2005 00:01:53 -0700 (PDT)
Message-ID: <20051026070153.46062.qmail@web31405.mail.mud.yahoo.com>

Hi All,  

The below udate statement is running for long time.  

update unbilled_report set
ADT_FLAG=(select ADT_FLAG from hospital where unbilled_report.hospital_id=hospital.hospital_id and unbilled_report.acct#=hospital.acct# and unbilled_report.mr#=hospital.mr# );  

HOSPITAL HAS indexing on acct#, mr# (primary key) and hospital_id (non-uniq index) and adt_flag has a bit map index.  

the explain plan is like this:
QUERY_PLAN

---

UPDATE STATEMENT
  UPDATE UNBILLED_REPORT
    TABLE ACCESS FULL UNBILLED_REPORT
    TABLE ACCESS BY INDEX ROWID HOSPITAL       INDEX UNIQUE SCAN SYS_C003868     select adt_flag from hospital is taking long time.  

select hospital.ADT_FLAG from hospital,unbilled_report where unbilled_report.hospital_id=hospital.hospital_id and unbilled_report.acct#=hospital.acct# and unbilled_report.mr#=hospital.mr#;  

Can someone help me in tuning this.  

Thanks,
Raj                   

---

 Yahoo! FareChase - Search multiple travel sites in one click.
--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 26 2005 - 02:09:36 CDT