Re: Tuning for IN subquery

>Rob,
> That doesn't help with my complex view, v1. It does help if I substitute
>one of the large joined tables for v1. In the latter case, I get a NESTED
>LOOPS join instead of a HASH join so the index on the big table is used.
> If I do an explain plan on similar code that works, SELECT * FROM TABLE2
>WHERE KEY1 IN ('X1', 'X2', 'X3'), I see:
>CONCATENATION
> TABLE ACCESS BY ROWID TABLE2
> INDEX RANGE SCAN indexname
> TABLE ACCESS BY ROWID TABLE2
> INDEX RANGE SCAN indexname
> TABLE ACCESS BY ROWID TABLE2
> INDEX RANGE SCAN indexname
>I can't find any hint that would give me concatenation. Looks like I need to
>resort to a package that uses a cursor to do view v1 lookups one at a time
>and
>store the results in a work table. The end use is in a Microsoft Access
>report, connecting via ODBC.
> Thanks for your help.
>Dave
>
>
>L120bj wrote:
>
>> >I forgot to mention that I tried DISTINCT in the subquery which should
>> >have the same result as PUSH_SUBQ (per page 288 of "Advanced Oracle
>> >Tuning and Adminstration", 1997, Oracle Press") . I just tried PUSH_SUBQ
>> >
>> >with no luck. VIEW1 below joins a dozen tables and runs quickly when
>> >subset
>> >on a single value of KEY1.
>> >
>> >Thanks for your comment.
>> >
>> >Dave
>> >
>> >L120bj wrote:
>> >
>> >> >The following query returns the results quickly:
>> >> > SELECT * FROM VIEW1 WHERE KEY1 IN ('X1', 'X2', 'X3')
>> >> > while this query is very slow:
>> >> > SELECT * FROM VIEW1 WHERE KEY1 IN (SELECT KEY1 FROM TABLE1)
>> >> >
>> >> > VIEW1 joins many tables by KEY1. These tables are indexed by KEY1.
>> >> > TABLE1 has three rows with the same values for KEY1 as in the first
>> >> >query. It is not indexed.
>> >> >
>> >> > Is there a hint I can use to make the second query as fast as the
>> >> >first?
>> >> >
>> >> > Thanks,
>> >> > Dave Ball
>> >> > Lockheed Martin
>>
>> Another thing that may be worth trying is
>> select /*+ USE_NL (t1 v1) */ v1.*
>> from view1 v1,
>> table1 t1
>> where t1.key1 = v1.key1;
>>
>> HTH,
>> Rob
>
>

Hello again Dave,
  We had some similar views at a site I used to work at a couple of years ago and the join method worked fine. One thing we did have was the init.ora parameter
partition_view_enabled was set to true (this may not be it's exact name, it's some time since I used it). Also, it may not be relevant for your version of Oracle, we were using 7.3.2.3 on Solaris at the time. HTH,
  Rob Received on Tue May 30 2000 - 00:00:00 CEST