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.
Received on Thu Aug 30 2007 - 11:07:42 CDT
