From oracle-l-bounce@freelists.org Wed Oct 26 02:17:23 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9Q7HM6g029175 for ; Wed, 26 Oct 2005 02:17:23 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9Q7HBvX029146 for ; Wed, 26 Oct 2005 02:17:13 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5FB1F210A8F; Wed, 26 Oct 2005 02:12:05 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01098-06; Wed, 26 Oct 2005 02:12:05 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CB40C2109C8; Wed, 26 Oct 2005 02:12:04 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=1GPWFo5cPLwxXPZVGoxvJ6BTR1GVYIXKDn69+gkdtrR0EvCZ2ggSniK3tKqQ8DEC27a9V0EU+AODwfQHmYwitF/vspv9ZtW6RI5iwqInWJeVjaPjnCIrN7oTcQf+e2nqBmlPVUaLfp5RJOaM8dR/iUg6StFCzimMUs2HGkyzucc= ; Message-ID: <20051026070954.42688.qmail@web31410.mail.mud.yahoo.com> Date: Wed, 26 Oct 2005 00:09:54 -0700 (PDT) From: raja rao Subject: Re: update statement tune To: raja rao , oracle list MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1686827073-1130310594=:42612" X-archive-position: 27595 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: raja4list@yahoo.com Precedence: normal Reply-To: raja4list@yahoo.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-3.8 required=5.0 tests=AWL,BAYES_00, FORGED_YAHOO_RCVD,HTML_MESSAGE,UPPERCASE_25_50 autolearn=no version=2.63 --0-1686827073-1130310594=:42612 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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 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. --0-1686827073-1130310594=:42612 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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@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. --0-1686827073-1130310594=:42612-- -- http://www.freelists.org/webpage/oracle-l