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

Some suggestions.

1. Note that there is no correlation between the outer SQL and inner SQL. Thus for each outer row returned, you execute the inner SQL in its entirety (16271 * 6335) and then perform the sorts for the grouping, etc. Perhaps you could add a correlation on voucher_number on the inner SQL (and d.voucher_number = a.voucher_number).
2. In the outer SQL try changing

    a.voucher_number in

to

    b.voucher_number in

(Yeah, I know, the optimizer SHOULD blah blah blah...try it anyhow).

3. If you have the EXPLAIN PLAN utility in RDB, run explain plan and see if anything unexpected shows up.

4. One alternative you might consider is creating a temporary table with the inner SQL and then running the outer SQL against the temporary table.

Steve Long
IT Services, Inc.
804-262-6332
answers_at_ix.netcom.com

In <329F4CB1.1EF5_at_monsanto.com> Saqib Mausoof <ssmaus_at_monsanto.com> writes:
>
>Hi,
>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.
>
>Thanks in advance. Any help would be much appreciated.
Received on Sun Dec 01 1996 - 00:00:00 CET