Re: Optimizing SQL to Reduce Table Scans

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: 4 Jan 2006 14:11:10 -0800
Message-ID: <1136412670.530359.241140@f14g2000cwb.googlegroups.com>

ct witter wrote:
> Thanks for the response. Even without the plan any suggestions for
> improvement? Is this the proper way to conduct this kind of query?

your query was
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 I do not see the need for tehORDER_HeEADER in the in-line view query. IOW, I would immediately try:
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_DETAIL OD
WHERE
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 ; But to really fix the performance, do as other suggested and at least run EXPLAIN PLAN. (and you don't need the DBA to do it for you).

   Ed Received on Wed Jan 04 2006 - 16:11:10 CST