Re: Query Improvement

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

Well, I tried the query below adding the NVL function, and received the same results:

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 = NVL(b.ind_id,500))
order by a.name; Received on Thu Aug 30 2007 - 10:29:56 CDT