Re: Partition Pruning on NULL -- need help.

Good idea, but after I applied the 9.2.0.4 patch, the explain plan showed that it would just FTS the null partition. This list partitioning is extremely powerful -- now I can isolate the few NULL records so that FTS's don't need to go through the whole thing.

Dom.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<bq8i0h$6jc$1$8300dec7_at_news.demon.co.uk>...
> 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
Received on Fri Nov 28 2003 - 22:40:25 CST