| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: query taking time
You are absolutely right.
when i dropped the fn based index and exeucted teh query supplied by you, it just took 2 seconds instead of 10-12 seconds.
Thanks for all your help.
Stephane Faroult <sfaroult_at_roughsea.com> wrote: 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-lReceived on Wed Oct 19 2005 - 04:08:02 CDT
 |
 |