query taking time
- From: raja rao <raja4list@xxxxxxxxx>
- To: oracle list <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Oct 2005 19:08:04 -0700 (PDT)
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.
Other related posts:
