update statement tune

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.  

Other related posts: