Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> update statement tune

update statement tune

From: raja rao <raja4list_at_yahoo.com>
Date: Wed, 26 Oct 2005 09:01:10 -0700 (PDT)
Message-ID: <20051026160110.57693.qmail@web31406.mail.mud.yahoo.com>

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_at_yahoo.com> 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.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 26 2005 - 11:04:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US