Re: Tuning an in list query on a partitioned table

This query should cause Oracle to use nested loops join:

select /*+ ORDERED USE_NL(tmp_partitioned) */ * from tmp_partitioned
where n in (select 1 from dual)
/

Note that using partitioned tables in a query *requires* the use of CBO.   So your "rule" hint in your query for the partitioned table is ignored. Take a look at:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#74098

Cheers,
Dave

Richard Kuhler wrote:
> I have a basic IN subquery predicate that gives me this plan which
> performs superbly:
>
> SELECT STATEMENT
> NESTED LOOPS
> VIEW OF 'VW_NSO_1'
> SORT (UNIQUE)
> TABLE ACCESS (FULL) OF 'DUAL'
> TABLE ACCESS (BY INDEX ROWID) OF 'TMP_UNPARTITIONED'
> INDEX (UNIQUE SCAN) OF 'TMP_UNPARITIONED_I' (UNIQUE)
>
>
> However, when the table is partitioned, this horribly inefficient plan
> is produced:
>
> SELECT STATEMENT
> HASH JOIN
> PARTITION RANGE (ALL)
> TABLE ACCESS (FULL) OF 'TMP_PARTITIONED'
> VIEW OF 'VW_NSO_1'
> SORT (UNIQUE)
> TABLE ACCESS (FULL) OF 'DUAL'
>
>
> How can I force this to the nested loops plan (preferably using hints)?
>
>
>
> Here is a working demonstration of the problem ...
>
>
> create table tmp_partitioned (
> pd date,
> n number)
> partition by range (pd) (
> partition p1 values less than (to_date('01-jan-2003',
> 'dd-mon-yyyy')),
> partition p2 values less than (maxvalue)
> )
> /
>
> create table tmp_unpartitioned (
> pd date,
> n number)
> /
>
> create unique index tmp_paritioned_i on tmp_partitioned (n)
> /
>
> create unique index tmp_unparitioned_i on tmp_unpartitioned (n)
> /
>
> set autotrace trace explain
>
> select /*+ rule */ *
> from tmp_unpartitioned
> where n in (select 1 from dual)
> /
>
> select /*+ rule */ *
> from tmp_partitioned
> where n in (select 1 from dual)
> /
>
>
> Note: I realize this is based on the RULE based optimizer. The COST
> based optimizer produces a horrible plan with the actual tables (yes the
> statistics are up to date).
>
>
> Thanks,
> Richard Kuhler
>
Received on Mon Jul 28 2003 - 21:52:51 CDT