Re: Huge query speed difference between 2 similar queries

"Marcus" <marcusyeoh_at_gmail.com> wrote in 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.
>
>

Why don't you produce EXPLAIN_PLANS for both & post them here? Received on Fri Nov 19 2004 - 20:14:53 CST