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.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 26 2005 - 11:04:33 CDT