Re: Partition Pruning on NULL -- need help.

Event 10128, level 2
The trace will show something like this: (which is a range partition, rather than a list pt, but you get the idea).

Event 10128 - level 2
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [5, 6]
   index = 5
  current partition: part# = 5, subp# = 65535, abs# = 5   current partition: part# = 6, subp# = 65535, abs# = 6

Depending on your version of Oracle, you may need to create a kkpap table first in your schema with:

create table kkpap_pruning(
 partition_count number,
 iterator varchar2(32),
 partition_level varchar2(32),
 order_pt varchar2(12),
 call_time varchar2(12),
 part# number,
 subp# number,
 abs# number
)
;

Alternatively, put the two partitions into separate tablespaces, put the default one offline, and run a query against null - and see if it crashes.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Domenic G." <domenicg_at_hotmail.com> wrote in message
news:c7e08a19.0311280910.3576c23a_at_posting.google.com...


> I need some help here.  I have an application that searches for null

> values on a very large table on a specific column, always forcing a

> full table scan.  Can't use a bitmap index on it (locking reasons) so

> I thought I could list partition it with a null partition ...

>

> example ...

>

> create table xyz (

>    c1 number,

>    c2 number)

> partition by list (c1) (

>    partition p1 values (null),

>    partition p2 values (default));

>

> But, when I do the explain plan on the "NULL" search ...

>

> explain plan ...

> select * from xyz where c1 is null;

>

> I get "KEY" in the partition start and stop in the plan table, so I

> can't tell if it will restrict itself to p1.  Is there an event I can

> turn on to show me this?

>

> Thanks in advance,

>

> Dom