Re: Optimising the Query

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 10 Nov 2006 12:45:26 -0800
Message-ID: <1163191526.638121.139930@b28g2000cwb.googlegroups.com>

pankaj_wolfhunter_at_yahoo.co.in wrote:
> 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
I suggest that you implement the other advice given here. You are currently asking Oracle to do work that it does not need to do - the advice provided will help you remove the unnecessary work. If the performance is not acceptable, and you have the Enterprise Edition of Oracle, take a look at materialized views.

Jonathan Lewis recently placed on his website an article about DBMS_XPLAN. See:
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/#comments

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Nov 10 2006 - 14:45:26 CST