Re: Optimisation of a sql query

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)?
>

Why are you performing a GROUP BY when there is no aggregate/group functions in your SELECT clause? Are you sure you do not mean ORDER BY? If that is the case, then realize that there is not true ordering of rows in a table so the ORDER BY is unnecessary.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown