Re: Query Improvement

On Aug 30, 9:07 am, Anurag Varma <avora...@xxxxxxxxx> wrote:
On Aug 30, 11:53 am, "ame...@xxxxxxx" <ame...@xxxxxxx> wrote:





On Aug 30, 10:18 am, Anurag Varma <avora...@xxxxxxxxx> wrote:

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

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.- Hide quoted text -

- Show quoted text -

Try "IN" instead..
Though it is dependent on the amount of data your inner query is
returning.

select name
,ind_code
from industry a
where a.ind_code IN
(select b.ind_id
from broker_rep_lookup b
where b.pdf = 'E'
and (b.participating = 'Y' or b.participating is null))
order by a.name;

.

Relevant Pages

  • (OT) : The Number - America You Are # 1 In My Heart
    ... Amount a full-time worker in the U.S. owes toward that debt ... Though we know who we payed the money too, (war industry) I wonder who's ... getting the interest on these trillion dollar loans? ...
    (rec.radio.shortwave)
  • OT: THE NUMBERS
    ... Amount a full-time worker in the U.S. owes toward that debt ... Though we know who we payed the money too, (war industry) I wonder who's ... getting the interest on these trillion dollar loans? ...
    (rec.radio.shortwave)
  • Re: Java 1.0 rt.jar availability?
    ... "The most significant trend in the US industry has been the decline in the amount ...
    (comp.lang.java.programmer)
  • Re: Serial I/O package for windows
    ... "The most significant trend in the US industry has been the decline in the amount ...
    (comp.lang.java.softwaretools)
  • Re: A Confession
    ... > An excellent analogy and right on the button. ... > Yes, Tom...Matthew complains a lot about the industry, but he still buys ... > a large amount of your product. ... > even worse, debase him, leaders in your industry should listen to him. ...
    (rec.music.classical.recordings)