Re: query taking time

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

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