update statement tune

  • From: raja rao <raja4list@xxxxxxxxx>
  • To: oracle list <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 26 Oct 2005 00:01:53 -0700 (PDT)

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: