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

Try the following modification:-

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 exists
(select 'x' from invoice_detail c, invoice_header d where d.voucher_number = a.voucher_number 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

I assume you have an index on invoice_header.voucher_number and invoice_detail.voucher_number.

Often a correlated subquery is faster than a nested subquery where the subquery retrieves many rows. This is due to the subquery creating a temporary table that cannot be indexed so all of your 6335 rows create a full table scan of the temp table. When this temp table is big it will be written to disc causing a lot of IO.

Is voucher number your primary key for invoice_header? If it is then the records in B and D are the same so I would take the extra clauses into the outer select and I amthe inner select would be considerably simplified. If it is not the primary key then how can you be sure you are retrieving the correct details for the header without specifying more columns in the a-b c-d joins?

Please email me if you need further assistance.

-- 
Richard Avery, e-mail:<Richard_Avery_at_nortel.com>
The content of this message represents personal opinion and is not in any
way representative of Nortel.

Saqib Mausoof <ssmaus_at_monsanto.com> wrote in article
<329F4CB1.1EF5_at_monsanto.com>...


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


>