Re: Query Improvement

From: <purohitatul_at_gmail.com>
Date: Thu, 30 Aug 2007 11:22:27 -0700
Message-ID: <1188498147.145196.42750@r23g2000prd.googlegroups.com>

On Aug 30, 9:07 am, Anurag Varma <avora..._at_gmail.com> wrote:
> On Aug 30, 11:53 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
>
>
>
>
> > On Aug 30, 10:18 am, Anurag Varma <avora..._at_gmail.com> wrote:
>
> > > On Aug 30, 11:12 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > > > Hi All,
>
> > > > I'm hoping someone can help me. I have the query below with the
> > > > explain plan. It takes more than 10 minutes to complete. There are
> > > > only 128 records in the table named INDUSTRY and about 150,000 records
> > > > in the table named BROKER_REP_LOOKUP, and BROKER_REP_LOOKUP is
> > > > actually a snapshot.
>
> > > > There are also the following indexes on the tables:
>
> > > > INDUSTRY: Index on IND_ID column.
> > > > BROKER_REP_LOOKUP: Functional Index on IND_ID. The function is NVL.
>
> > > > I see the table access is FULL on both the table and snapshot. I'm
> > > > not sure why this is, or why tables with such a small amount of
> > > > records takes so long.
>
> > > > I'm open to any help.
>
> > > > select name,ind_code
> > > > from industry a where exists
> > > > (select 'x' from broker_rep_lookup b where b.pdf = 'E' and
> > > > (b.participating = 'Y' or b.participating is null) and a.ind_code
> > > > = b.ind_id)
> > > > order by a.name;
>
> > > > Execution Plan
> > > > ----------------------------------------------------------
> > > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=13
> > > > Bytes=260)
> > > > 1 0 SORT (ORDER BY) (Cost=28 Card=13 Bytes=260)
> > > > 2 1 FILTER
> > > > 3 2 TABLE ACCESS (FULL) OF 'INDUSTRY' (Cost=1 Card=13
> > > > Bytes=260)
> > > > 4 2 TABLE ACCESS (FULL) OF 'BROKER_REP_LOOKUP' (Cost=2411
> > > > Card=3026 Bytes=21182)
>
> > > > Thanks in advance for your time.
>
> > > You have a NVL(ind_id) index on broker_rep_lookup ... However, in the
> > > query you do not use a NVL function for the join.
> > > So thats why probably the index does not get used.
>
> > > Anurag
>
> > Was unaware that you had to also set these parameters:
>
> > QUERY_REWRITE_ENABLED=TRUE
> > QUERY_REWRITE_INTEGRITY=TRUSTED
>
> > Runs like lightening now.........
>
> You should specify *always* the oracle version. The above details
> are version dependent.- Hide quoted text -
>
> - Show quoted text -

Try "IN" instead..
Though it is dependent on the amount of data your inner query is returning.

select name

      ,ind_code
from industry a
where a.ind_code IN

     (select b.ind_id
        from broker_rep_lookup b
       where b.pdf = 'E'
         and (b.participating = 'Y' or b.participating is null))

order by a.name; Received on Thu Aug 30 2007 - 13:22:27 CDT