Re: Tuning for IN subquery

>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 Received on Tue May 30 2000 - 00:00:00 CEST