Tuning an in list query on a partitioned table

From: Richard Kuhler <noone_at_nowhere.com>
Date: Mon, 28 Jul 2003 22:51:50 GMT
Message-ID: <aOhVa.12139$j%4.510685@twister.socal.rr.com>

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 - 17:51:50 CDT