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

1 No version. How do you expect help
2 No explain plan. How do you expect help 3 How did you establish there is a 'having issue'? Did you look in a crystal ball, or went to your work on a broom stick? 4 Did you check where your bottlenecks are located? Disk? Logwr? Database archiving like hell? If you didn't do any of those things, how do you expect anyone can help you out.

-- 
Sybrand Bakker
Senior Oracle DBA