Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query Improvement

Re: Query Improvement

From: Anurag Varma <avoracle_at_gmail.com>
Date: Thu, 30 Aug 2007 08:18:02 -0700
Message-ID: <1188487082.193760.188510@19g2000hsx.googlegroups.com>


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 Received on Thu Aug 30 2007 - 10:18:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US