purohitatul_at_gmail.com wrote:
> On Aug 30, 9:07 am, Anurag Varma <avora..._at_gmail.com> wrote:
>> On Aug 30, 11:53 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>>
>>
>>
>>
>>
>>> 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
>>> 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 -
> 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;
>
The 10g CBO would also consider this rewriting. Even in older versions
it should help to write it as an equijoin without IN or EXISTS and let
the CBO decide about the driving table.
I also didn't understand the function based index. Could you tell us how
exactly this index is defined? As far as I know nvl() requires two
parameters. I would suggest nvl(participating, 'Y'), but then you should
use it in the same way it was defined
...
and nvl(b.participating, 'Y') = 'Y'
How is the distribution of the participating value?
Jan
Received on Fri Aug 31 2007 - 13:50:53 CDT
