Re: Huge query speed difference between 2 similar queries

Is this an Oracle Financials database ?
If so, please give product version.
If it is a version 11, please output your sql plans here (see sqlplus with autotrace option).

This will help us to help you.

Yang
dba oracle.

"Marcus" <marcusyeoh_at_gmail.com> a écrit dans le message de news: 1100916664.279169.276810_at_z14g2000cwz.googlegroups.com...
> I am puzzled by this. I've 2 similar queries. Query 1 took forever to
> run and query 2 took 14.210 secs. The difference is that query 2 has
> another subquery that selects the inv_id first.
> Why is there such a huge time difference?
>
> Query 1 -- forever (ran for 30 mins and gave up)
> SELECT inv.*, inv.quantity * pd.gross_wgt
> FROM
> (SELECT ih.inv_id, id.prod_id, id.quantity
> FROM invoice_detail id, invoice_header ih
> WHERE ih.cust_id='ca0105' AND id.inv_id = ih.inv_id ) inv,
> portal.product pd
> WHERE inv.prod_id = pd.prod_id
>
> Query 2 -- 14.210 sec
> SELECT inv.*, inv.quantity * pd.gross_wgt
> FROM
> (SELECT ih.inv_id, id.prod_id, id.quantity
> FROM invoice_detail id, invoice_header ih
> WHERE ih.inv_id IN (SELECT inv_id FROM invoice_header WHERE
> cust_id='ca0105')
> AND id.inv_id = ih.inv_id) inv,
> portal.product pd
> WHERE inv.prod_id = pd.prod_id
>
> invoice_detail has PK of inv_id and line_no.
> invoice_header has PK of inv_id.
> product has PK of prod_id.
> Could it be there is no foreign key in invoice_header for cust_id?
> Appreciate any advice.
>
> Thank you.
>
Received on Sat Nov 20 2004 - 12:20:44 CST