Pre-build the sums in a matview.
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
Received on Tue Nov 07 2006 - 10:44:29 CST
