I am getting really frusturated at this query, maybe someone else can
see what I am doing wrong. This is running against an Oracle RDB
database, 6.1 on an Alpha.
select a.wo_or_cap_num, a.voucher_number, b.date_status_change,
sum(a.cost) from
invoice_dist a, invoice_header b where a.plant_code ='M' and
a.capital_expense_num starting with 'AR' and
a.voucher_number=b.voucher_number
and a.voucher_number in
(select c.voucher_number from invoice_detail c, invoice_header d where
c.plant_code ='M' and c.work_order_base starting with 'AR' and
d.invoice_type
='PO' and d.invoice_status between 40 and 49 and c.voucher_number=
d.voucher_number) group by a.wo_or_cap_num, a.voucher_number,
b.date_status_change
This query consumes as much as 1 HOUR of CPU time, and then I just kill
it. If I look at the process it would show as many as 2 million I/O's.
It is really working, but it does not give me ANY results.
The sub query runs within 20 secs, returns 16271 records. The master
query runs with in 20 secs and returns 6335 rows. Together they hang up.
I have run these kinds of queries before, can someone explain what is
going on here, it is beyond my intelligence to figure this out.
