Re: Optimising the Query

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 5 Nov 2006 18:15:19 -0800
Message-ID: <1162779319.853205.162100@h48g2000cwc.googlegroups.com>

pankaj_wolfhunter_at_yahoo.co.in wrote:
> Greetings,
>
> No of records in TABLE1 = 46697622
> No of records in TABLE2 = 9433275
> No of records in TABLE3 = 9576297
>
> SELECT TAB1.P_TDATE,
> FLOOR(TAB1.STIME/100) T_TIME,
> SUM (CASE WHEN TAB1.LIND <> 1
> AND (TAB2.CNAME NOT IN ('PN','AR')
> OR (NVL(TAB3.OMNI,0) <> 7)
> OR (TAB1.TIF <> 3))
> THEN 1
> ELSE 0
> END ) ORDPOSTMKT,
> SUM (CASE WHEN (TAB2.CNAME IN ('PN','AR')
> OR (TAB3.OMNI = 7)
> OR (TAB1.TIF = 3))
> THEN 1
> ELSE 0
> END ) ORDOARS
> FROM TABLE1 TAB1
> LEFT OUTER JOIN (
> ( SELECT ID,
> P_TDATE,
> SYMBOL,
> REFNUMBER,
> CNAME,
> ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
> FROM TABLE2
> ) TAB2
> JOIN (SELECT ID,
> P_TDATE,
> SYMBOL,
> OMNI,
> REFNUMBER,
> ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK
> FROM TABLE3
> ) TAB3
> ON (
> TAB2.P_TDATE =
> TAB3.P_TDATE AND TAB2.SYMBOL =
> TAB3.SYMBOL
> AND TAB2.ID =
> TAB3.ID
> AND TAB2.REFNUMBER = TAB3.REFNUMBER
> AND TAB3.RANK = 1
> AND TAB2.RANK = 1)
> )
> ON (TAB1.P_TDATE =
> TAB3.P_TDATE
> AND TAB1.SYMBOL =
> TAB3.SYMBOL
> AND TAB1.ID =
> TAB3.ID)
> WHERE TAB1.OIND <> 0
> AND TAB1.OTYPE IN
> ('MR','GMR')
> GROUP BY TAB1.P_TDATE,
> FLOOR(TAB1.STIME/100)
> ORDER BY T_TIME
>
> Currently the query is taking 2+ hrs to execute.
>
> The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN
> with TABLE1.
> I know I am not giving much details abt each step and conditions used
> here but the query is
> running fine and just wanted to know whether the same query can be
> written
> in a more efficient manner.
>
> Any help would be appreciated.
>
> TIA
>
> DB version Info:
>
> Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
> PL/SQL Release 10.2.0.1.0 - Production

I agree with Daniel Mogan, an explain plan would be helpful to see what is happening. Slightly reformatting your SQL statement: SELECT
  TAB1.P_TDATE,
  FLOOR(TAB1.STIME/100) T_TIME,
  SUM (CASE WHEN

         TAB1.LIND <> 1
         AND (TAB2.CNAME NOT IN ('PN','AR')
           OR NVL(TAB3.OMNI,0) <> 7
           OR TAB1.TIF <> 3)
       THEN 1
       ELSE 0
       END) ORDPOSTMKT,
  SUM (CASE WHEN
         (TAB2.CNAME IN ('PN','AR')
          OR TAB3.OMNI = 7
          OR TAB1.TIF = 3)
       THEN 1
       ELSE 0
       END) ORDOARS

FROM
  TABLE1 TAB1
  LEFT OUTER JOIN (
  (SELECT
    ID,
    P_TDATE,
    SYMBOL,
    REFNUMBER,
    CNAME,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK   FROM
    TABLE2) TAB2
  JOIN
  (SELECT
    ID,
    P_TDATE,
    SYMBOL,
    OMNI,
    REFNUMBER,
    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY REFNUMBER) RANK   FROM
    TABLE3) TAB3
  ON (TAB2.P_TDATE=TAB3.P_TDATE

      AND TAB2.SYMBOL = TAB3.SYMBOL
      AND TAB2.ID = TAB3.ID
      AND TAB2.REFNUMBER = TAB3.REFNUMBER
      AND TAB3.RANK = 1
      AND TAB2.RANK = 1))
  ON (TAB1.P_TDATE = TAB3.P_TDATE
      AND TAB1.SYMBOL = TAB3.SYMBOL
      AND TAB1.ID = TAB3.ID)

WHERE
  TAB1.OIND <> 0
  AND TAB1.OTYPE IN ('MR','GMR')
GROUP BY
  TAB1.P_TDATE,
  FLOOR(TAB1.STIME/100)
ORDER BY
  T_TIME; You are perfoming two ROW_NUMBER analytical operations, and discarding the results. The ROW_NUMBER operation is sorting quite a few rows and this sort is likely hitting the temporary tablespace quite hard. Your query includes TAB3.RANK = 1 and TAB2.RANK = 1, but not TAB3.RANK = TAB2.RANK. The explain plan would indicate if you are performing a full table scan on TABLE1's 46,697,622 rows.

Check the size of your SORT_AREA_SIZE. Try rewriting the SQL statement into an alternate form, similar to this: SELECT
  TAB1.P_TDATE,
  FLOOR(TAB1.STIME/100) T_TIME,
  SUM (CASE WHEN

         TAB1.LIND <> 1
         AND (TAB2.CNAME NOT IN ('PN','AR')
           OR NVL(TAB3.OMNI,0) <> 7
           OR TAB1.TIF <> 3)
       THEN 1
       ELSE 0
       END) ORDPOSTMKT,
  SUM (CASE WHEN
         (TAB2.CNAME IN ('PN','AR')
          OR TAB3.OMNI = 7
          OR TAB1.TIF = 3)
       THEN 1
       ELSE 0
       END) ORDOARS

FROM
  TABLE1 TAB1,
  TABLE2 TAB2,
  TABLE3 TAB3
WHERE
  TAB1.P_TDATE = TAB3.P_TDATE(+)

  AND TAB1.SYMBOL = TAB3.SYMBOL(+)
  AND TAB1.ID = TAB3.ID(+)
  AND TAB3.P_TDATE = TAB2.P_TDATE(+)
  AND TAB3.SYMBOL = TAB2.SYMBOL(+)
  AND TAB3.ID = TAB2.ID(+)
  AND TAB3.REFNUMBER = TAB2.REFNUMBER(+)
  AND TAB3.RANK = TAB2.RANK(+)
  AND TAB3.RANK(+) = 1
  AND TAB2.RANK(+) = 1
  AND TAB1.OIND <> 0
  AND TAB1.OTYPE IN ('MR','GMR')

GROUP BY
  TAB1.P_TDATE,
  FLOOR(TAB1.STIME/100)
ORDER BY
  T_TIME; Do you need the outer join? If possible, remove that and performance may improve considerably. I could be wrong, but I believe that the CASE statements may not be functioning as expected if an outer join would be required to retrieve all rows, for instance: TAB2.CNAME NOT IN ('PN','AR') - this may need to be rewritten as NVL(TAB2.CNAME,'PN') NOT IN ('PN','AR') to obtain the expected results.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Nov 05 2006 - 20:15:19 CST