Re: Query Improvement

On Aug 30, 11:12 am, "ame...@xxxxxxx" <ame...@xxxxxxx> 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



.

Relevant Pages

  • Query Improvement
    ... I have the query below with the ... explain plan. ... INDUSTRY: Index on IND_ID column. ... I see the table access is FULL on both the table and snapshot. ...
    (comp.databases.oracle.server)
  • Re: Query Improvement
    ... only 128 records in the table named INDUSTRY and about 150,000 records ... actually a snapshot. ... Well, I tried the query below adding the NVL function, and received ...
    (comp.databases.oracle.server)
  • Re: Why is this
    ... would be to have a single index on (lastrecon, acctnum, deposit) [or ... cover the query and handle the restrictive condition immediately. ... there is no perfect query plan. ... use the index for TOP 1, even if it used quite a few bookmark lookups, ...
    (microsoft.public.sqlserver.server)
  • Re: Query optimizer issue
    ... Consider a stored proc defined as follows: ... when compiling a plan. ... parameter @p1 into the query at compile time before a plan for the query ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.server)
  • Re: Execute SQL UD Function call timeout, 1-3 seconds in query ana
    ... We did build a little ASP.NET page yesterday that lets us put a given sql ... query into a text box and then return the execution plan (using Showplan_Text ... Query Analyzer. ...
    (microsoft.public.dotnet.framework.adonet)