Re: Tuning for IN subquery

[Quoted] [Quoted] 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
> >
> >
> >
>
> Hi Dave,
> Have you tried the PUSH_SUBQ hint ?
> HTH,
> Rob
Received on Tue May 30 2000 - 00:00:00 CEST