Optimizing SQL to Reduce Table Scans

From: ct witter <norcold1_at_yahoo.com>
Date: 4 Jan 2006 09:28:15 -0800
Message-ID: <1136395695.160044.178870@f14g2000cwb.googlegroups.com>

I am using 2 tables: an Order Header and Order Detail. The problem that I keep running into is for a request to pull some criteria based on the detail lines, then display all the lines for orders meeting that criteria.

So I believe that the SQL is first executing to get all the line(s) that meet the criteria. Then it is going back through the table to gather the header and all detail lines that meet the previous criteria. This results in multiple table scans.

Is this the correct way to do this? Is there a more efficient way?

Below is an example of this SQL:

SELECT DISTINCT

OH.ORDER_NUMBER,
OH.ORDER_DATE,
OD.LINE_NUMBER,
OD.ORDER_CODE,
OD.ORDER_STATUS

FROM
DB.ORDER_HEADER OH,
DB.ORDER_DETAIL OD
(SELECT DISTINCT
OH.ORDER_NUMBER
FROM
DB.ORDER_HEADER OH,
DB.ORDER_DETAIL OD
WHERE
OH.ORDER_ID = OD.ORDER_ID AND
OD.ORDER_CODE IN('05','52','63','558','998','999') )SQRY_1 WHERE
OH.ORDER_ID = OD.ORDER_ID AND
OH.ORDER_NUMBER = SQRY_1.ORDER_NUMBER Received on Wed Jan 04 2006 - 11:28:15 CST