Re: Tuning for IN subquery

Rob,

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