Re: Optimisation of a sql query

Abhijat wrote:
> Hi Sybrand/David/Brian/Charles/Mladen,
> First of all many thanks for your valuable thoughts and invaluable
> time.
> I understand my mistake and below I m going to mention the steps that I
> am going to take to correct. Please look into it too.
>
> My actual code doesn't contain select *, it has fixed selections, hence
> what David is mentioning about select * is automatically taken care of.
> About using group by and having, I have to confess that the query I
> have mentioned here is slightily different from the actual query. The
> actual query does contain selections like "SUM(var1)".
> The actual query that I intend to execute is like this
> CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
> SELECT
> DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
> NEW_ISSUER,"DISCOVER" AS ISSUER,
> B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
> SUM(A.AUTH_AMT) AS AUTHORIZED ,
> SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - CALCULATED
> CASHOVER ) AS MERCHANDISE
> FROM rd_at2.AUTH_MAY06 A LEFT JOIN dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
> ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
> GROUP BY B.MCC, B.MERCHANT
> 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";
> So now that I m using aggregated functions, please suggest the best way
> to improve my code. Shall I go ahead with the where clause as suggested
> by you all or anything else. Please suggest with regards to the fact
> that the AUTH_MAY06 is about 25 GB in size and the other table is about
> 500 MB.
> Once again thanks to all for your overwhelming response.
>
> Regards,
> Abhijat

Here are appropriate uses of HAVING - note the aggregate in the HAVING: SELECT
  NAME,
  BLOCK_SIZE,
  SUM(BUFFERS)
FROM
  V$BUFFER_POOL
GROUP BY
  NAME,
  BLOCK_SIZE
HAVING
  SUM(BUFFERS) > 0; SELECT
  SQL_TEXT,
  SUM(ONEPASS_EXECUTIONS) ONEPASS_CNT,
  SUM(MULTIPASSES_EXECUTIONS) MPASS_CNT
FROM
  V$SQL S,
  V$SQL_WORKAREA WA
WHERE
  S.ADDRESS = WA.ADDRESS
GROUP BY
  SQL_TEXT
HAVING
  SUM(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0; SELECT
  SUBSTR(SQL_TEXT, 1, 60),
  COUNT(*)
FROM
  V$SQLSTATS
WHERE
  EXECUTIONS < 4
GROUP BY
  SUBSTR(SQL_TEXT, 1, 60)
HAVING
  COUNT(*) > 1; You will want to eliminate as many rows/records as soon as possible in the select statement - this does not necessarily refer to the order of the items in the WHERE clause, as Oracle will often change the order of the items in the WHERE clause when trying to find the most efficient way to process the SQL statement. Since your HAVING clause still does not include a COUNT(), SUM(), MIN(), MAX(), etc., you do not need the HAVING. Instead, move those items currently in the HAVING to a WHERE clause, as has been suggested by several people already. The WHERE clause will remove the unnecessary rows/records before those rows are passed on to the grouping (and possible sorting operation for Oracle releases before 10.2). Once you have the WHERE and HAVING clauses fixed, you can start tuning the SQL statement.

The tuning method varies depending on the version of Oracle that contains the SAS data, the initialization parameters that are in effect for the database, the indexes that are present for the database tables, etc. Please excecute the following query and post the results: SELECT
  NAME,
  VALUE
FROM
  V$PARAMETER
ORDER BY
  UPPER(NAME); Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Jul 19 2006 - 17:56:37 CDT