query taking time

From: raja rao <raja4list_at_yahoo.com>
Date: Tue, 18 Oct 2005 19:08:04 -0700 (PDT)
Message-ID: <20051019020804.40550.qmail@web31401.mail.mud.yahoo.com>

Hi All,

Can someone help me in tuning the below query.

select /*+ parallel (4) */ sum(UB.charges),

                             count(UB.acct#),
                             to_char(MAX(UB.RECEIVED_DATE),'mm-dd-yyyy'),
                             to_char(MAX(UB.RECEIVED_DATE),'HH24:MI')

from UNBILLED_REPORT UB
where UB.HOSPITAL_ID='MENONITA'
AND trim(UB.PATIENT_CASE_TYPE)

in (select DISTINCT(PST.PATIENT_CASE_TYPE)

              from  PATIENT_CASE_TYPE PST,HIM_PATIENT_CASE_TYPE HPST
                where PST.HOSPITAL_ID='MENONITA'
               AND HPST.HIMCODE=PST.HIMCODE
                and  HPST.PATIENT_CASE_TYPE ='INP')
               AND trunc(UB.received_date) = trunc((SELECT max(RECEIVED_DATE)
                                                      FROM UNBILLED_REPORT
                                                          WHERE HOSPITAL_ID='MENONITA'))

This took 16 seconds to get the one row data.

the autotrace output is:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=457 Card=1 Bytes=53) 1 0 SORT (AGGREGATE)

   2    1     HASH JOIN (SEMI) (Cost=457 Card=23 Bytes=1219)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'UNBILLED_REPORT' (Co
          st=451 Card=2252 Bytes=58552)
   4    3         INDEX (RANGE SCAN) OF 'UNBIL_FN_RECD_DT' (NON-UNIQUE
          ) (Cost=49 Card=15763)
   5    4           SORT (AGGREGATE)
   6    5             TABLE ACCESS (FULL) OF 'UNBILLED_REPORT' (Cost=3
          208 Card=135108 Bytes=1621296)
   7    2       VIEW OF 'VW_NSO_1' (Cost=5 Card=1 Bytes=27)
   8    7         HASH JOIN (Cost=5 Card=1 Bytes=89)
   9    8           TABLE ACCESS (FULL) OF 'PATIENT_CASE_TYPE' (Cost=2
           Card=1 Bytes=49)
  10    8           TABLE ACCESS (FULL) OF 'HIM_PATIENT_CASE_TYPE' (Co
          st=2 Card=1 Bytes=40)

Statistics

          0  recursive calls
          0  db block gets
      22250  consistent gets
      21293  physical reads
          0  redo size
        787  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

The table statistics:

unbilled_report:

21:48:41 SQL> select count(*) from unbilled_report; COUNT(*)

945758
Elapsed: 00:00:17.77
Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3208 Card=1) 1 0 SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'UNBILLED_REPORT' (Cost=3208 Card
          =945758)

Statistics

          0  recursive calls
          0  db block gets
      21136  consistent gets
      21124  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 
indexes on unbilled_report:
INDEX_NAME                     INDEX_TYPE


------------------------------ ---------------------------

UB_FN_CASETYPE                 NORMAL
UNBIL_FN_RECD_DT               FUNCTION-BASED NORMAL
UNBIL_HSID                     NORMAL

21:51:48 SQL> select count(*) from PATIENT_CASE_TYPE; COUNT(*)

21:51:49 SQL> select count(*) from HIM_PATIENT_CASE_TYPE; COUNT(*)

I will provide more details if needed.

Thanks in advance,
Raj

Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

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

Received on Tue Oct 18 2005 - 21:11:15 CDT