Re: Optimisation of a sql query

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 19 Jul 2006 09:21:21 -0700
Message-ID: <1153326081.129539.87190@b28g2000cwb.googlegroups.com>

Abhijat wrote:
> Hi group,
> I have a simple create table query.
> CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
> SELECT *
> FROM rd_at2.AUTH_MAY06 A LEFT JOIN
> dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
> ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
> GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM
> HAVING A.RSPNS_CDE = "00"
> AND A.TST_ACCT_CDE = ""
> AND A.AUTH_TRAN_CDE IN ("0110","0120")
> AND A.AUTH_PROC_1_CDE = "09";
>
> The data table AUTH_MAY06 is of size 25 GB and the other table is about
> 500MB!
> I have two questions regarding the performance tuning of this query.
> 1. What should be the order of having clause so that the processing
> time is decreased ?
> 2. Is there another way out (ex. using some options in oracle/sas)
> which would further decrease the processing speed (assuming we have
> fixed the "having" issue, if any)?

HAVING is resolved after the GROUP BY operation. This is helpful when trying to do something like this: SUM(MATERIAL_COST) > 10000. This means that the GROUP BY operation must be performed before the restrictions on the HAVING can be applied, hence a full table scan of the 25GB is likely being performed, along with an attempted GROUP BY of that 25GB of data, an a possible sort operation.

WHERE is resolved prior to a GROUP BY operation, and should be used when possible to restict data before it enters the GROUP BY operation.

CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR

WHERE     A.RSPNS_CDE = "00"
AND        A.TST_ACCT_CDE = ""
AND        A.AUTH_TRAN_CDE  IN ("0110","0120")
AND       A.AUTH_PROC_1_CDE = "09"

GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM; I personally prefer the SQL89 syntax of left outer joins, which are less complicated to decipher when more than two tables are involved: CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS SELECT
  *
FROM
  rd_at2.AUTH_MAY06,
  dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
WHERE
  A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR(+)

  AND A.RSPNS_CDE = '00'
  AND A.TST_ACCT_CDE = ''
  AND A.AUTH_TRAN_CDE  IN ('0110','0120')
  AND A.AUTH_PROC_1_CDE = '09'

GROUP BY
  B.MCC_CTGY_KEY,
  B.SLS_GRP_LVL_6_NM; "SELECT * FROM" does not seem approproiate for use with "GROUP BY".

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Jul 19 2006 - 11:21:21 CDT