Re: [Q] Nested SQL query - what am I doing wrong

> >
> > 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
> >

Ouch!
Using your own timings you are asking Oracle to do this:

1 master query _at_ 20 sec		= 20 sec
6335 sub-queries _at_20 sec	= 126,700 sec (1 for each master returned)

Total time = 126,720 secs or just over 35 hours. You have to take out the nested query part and build it in to the main query 'somehow' but there's not enough information in your original for me to propose a method. Hope you see what you're asking your Alpha to do now.

The Wils. Received on Thu Dec 05 1996 - 00:00:00 CET