Re: Optimising the Query

EscVector wrote:
> 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
Thanks every1.

Charles, thanks for the efforts. I still have to compare the xplan results.

Daniel, I'll keep that thing in mind.

EscVector, as u suggested for matview.
I was not familiar with that. I did some googling on that and found it can greatly increase performance in
terms of querying large tables.

Some question, how can I apply concept of matview here? I mean I have two "SUM's" here. Do I have to have to create two matview in this case?
Will the whole "FROM" clause condition needs to specify in matview? Is there something needs to be taken into consideration while using matview?

I'll be helpful if u ppl can guide me here?

TIA Received on Fri Nov 10 2006 - 09:58:18 CST