Huge query speed difference between 2 similar queries

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 Fri Nov 19 2004 - 20:11:04 CST