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

In article <329F4CB1.1EF5_at_monsanto.com>, Saqib Mausoof <URL:mailto:ssmaus_at_monsanto.com> wrote:
>
> 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.

I you want an official answer call your local Oracle support centre. I am not at work at the moment so don't consider this official.

One method that might work is to install Rdb7 MV. Convert a copy of the database to V7. Collect all the new statistics. Run the query again and get a query outline. Implement the query outline on V6.1 (assuming you don't want to upgrade yet).

I used this on one query that took 17 hours on V6.1. It took the time down to about 30 minutes on both versions. That query also had a group by and a subquery.

-- 
Peter Jackson - work address pjackson_at_uk.oracle.com
Everything I write is my own opinion