update statement tune
- From: raja rao <raja4list@xxxxxxxxx>
- To: oracle list <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Oct 2005 09:01:10 -0700 (PDT)
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:
