Query Improvement

From: <amerar_at_iwc.net>
Date: Thu, 30 Aug 2007 08:12:00 -0700
Message-ID: <1188486720.076335.326870@o80g2000hse.googlegroups.com>

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. Received on Thu Aug 30 2007 - 10:12:00 CDT