update statement tune


Explain plan for the below select is:
 
 
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#;
 
QUERY_PLAN
--------------------------------------------------------------------------------
SELECT STATEMENT
  HASH JOIN
    VIEW  index$_join$_001
      HASH JOIN
        HASH JOIN
          INDEX FAST FULL SCAN SYS_C003868
          BITMAP CONVERSION TO ROWIDS
            BITMAP INDEX FULL SCAN ADT_BITMAP_INDX
        INDEX FAST FULL SCAN HSP_ID
    TABLE ACCESS FULL UNBILLED_REPORT

 


raja rao <raja4list@xxxxxxxxx> wrote:
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. 

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

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

Other related posts: