Re: query taking time
- From: Arul Ramachandran <contactarul@xxxxxxxxx>
- To: oracle list <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 Oct 2005 23:19:19 -0700
Hi Raja,
1. lose the parallel hint ( 2 CPU box, 1 million row table )
2. lose the TRIM, TRUNC functions - I don't see a need
3. create index new_ix on
unbilled_report
(hospital_id, received_date, patient_case_type, charges, acct#);
i am just guessing the order of the columns in the index, you'll have to
validate this
4. execute dbms_stats.gather_table_stats(user,'UNBILLED_REPORT',cascade=>
true);
5. of course, test the re-written query below:
select
sum(charges),
count(acct#),
to_char(received_date, 'mm-dd-yyyy'),
to_char(received_date, 'hh24:mi')
from unbilled_report ub
where hospital_id = 'MENONITA'
and 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 received_date = (select max(received_date)
from unbilled_report
where hospital_id = 'MENONITA')
6. also see if you can use subquery factoring.
HTH,
-Arul
Other related posts:
