Re: query taking time

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Wed, 19 Oct 2005 08:54:57 +0200
Message-Id: <1129704897.5405.15.camel@frlinux2.roughsea.com>

Raj,

I think that what is doing you in is your subquery

   select max(received_date)

If I read your information well, you have created a function based index (FBI) on trunc(received_date) (in the case of dates, I would rather rewrite the queries as

      received_date >= trunc(...) and received_date < trunc(...) + 1 because it would allow you to have regular index, less costly to maintain. But this isn't the problem here). Your subquery doesn't use the FBI. In fact, it cannot use it. Rewrite your query as follows, it should fly:

select 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) =
                          (SELECT max(trunc(RECEIVED_DATE))
                           FROM UNBILLED_REPORT
                           WHERE HOSPITAL_ID='MENONITA')

And remove hints, they are rarely necessary ...

HTH Stephane Faroult

On Tue, 2005-10-18 at 19:08 -0700, raja rao wrote:
> 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(*)
> ----------
> 44
>
> 21:51:49 SQL> select count(*) from HIM_PATIENT_CASE_TYPE;
> COUNT(*)
> ----------
> 4
>
> 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 Wed Oct 19 2005 - 01:56:00 CDT