Re: Optimising the Query

From: <pankaj_wolfhunter_at_yahoo.co.in>
Date: 5 Nov 2006 23:19:09 -0800
Message-ID: <1162797549.461017.211250@b28g2000cwb.googlegroups.com>

Charles Hooper wrote:

> 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.

Thanks for the replies.

Here's the explain plan generated by the query:

Execution Plan

| Id  | Operation                              | Name                 |

|   0 | SELECT STATEMENT                       |                      |
  762K|
   51M|   520K|       |       |

|   1 |  SORT ORDER BY                         |                      |
  762K|
   51M|   520K|       |       |

|   2 |   HASH GROUP BY                        |                      |
  762K|
   51M|   520K|       |       |

|   3 |    HASH JOIN RIGHT OUTER               |                      |
 1527K|
  103M|   520K|       |       |

|   4 |     VIEW                               |                      |
36522 |
 1248K|   251K|       |       |

|   5 |      HASH JOIN                         |                      |
36522 |
 4529K|   251K|       |       |

|   6 |       VIEW                             |                      |
 9583K|
  539M| 79490 |       |       |

|   7 |        WINDOW SORT PUSHED RANK         |                      |
 9583K|
  246M| 79490 |       |       |

|   8 |         PARTITION RANGE SINGLE         |                      |

9583K|
246M| 10858 | 28 | 28 |

|   9 |          PARTITION HASH ALL            |                      |
 9583K|
  246M| 10858 |     1 |     4 |

|  10 |           TABLE ACCESS FULL            | TABLE2           |

9583K|
246M| 10858 | 109 | 112 |

|  11 |       VIEW                             |                      |
 9434K|
  611M|   109K|       |       |

|  12 |        WINDOW SORT PUSHED RANK         |                      |
 9434K|
  224M|   109K|       |       |

|  13 |         PARTITION RANGE SINGLE         |                      |

9434K|
224M| 44772 | 29 | 29 |

|  14 |          PARTITION HASH ALL            |                      |
 9434K|
  224M| 44772 |     1 |     4 |

|  15 |           TABLE ACCESS FULL            | TABLE3           |

9434K|
224M| 44772 | 113 | 116 |

|  16 |     PARTITION RANGE SINGLE             |                      |

1527K|
52M| 269K| 30 | 30 |

|  17 |      PARTITION HASH ALL                |                      |
 1527K|
   52M|   269K|     1 |    16 |

|  18 |       TABLE ACCESS BY LOCAL INDEX ROWID| TABLE1 |  1527K|

52M| 269K| 465 | 480 |

|  19 |        INDEX RANGE SCAN                | TABLE1_P_UK1     |
46M|
      | 20636 |   465 |   480 |

-------------------------------------------------------------------------------
-------------------------------

Note

'PLAN_TABLE' is old version

Statistics

       8199  recursive calls
       8695  db block gets

1568210 consistent gets
1429061 physical reads

          0  redo size
      10581  bytes sent via SQL*Net to client
       1203  bytes received via SQL*Net from client
         36  SQL*Net roundtrips to/from client
         51  sorts (memory)
          2  sorts (disk)
        514  rows processed

Received on Mon Nov 06 2006 - 01:19:09 CST