Re: Table Partitioning, Part 1

Tom Lane <tgl@sss.pgh.pa.us> writes:

> > Partition Elimination relies upon being able to prove at execution time
> 
> You mean plan time.

Fwiw, both are possible.

In oracle there are (at least) three different cases:

1. For queries like "select * from tab" the plan shows a multiple partition
scan.

2. For queries like "select * from tab where partition_key = 1" the plan
shows the partition that the planner proved would suffice.

3. For queries like select * from tab where partition_key = ?" the plan
shows a single partition scan of a partition to be determined at run-time.

This last case can be very important for joins as well. For queries like
select * from tab,tab2 where tab1.partition_key = tab2.fk" the planner can
prove that each nested loop iteration will only require a single btree lookup.
This is important since if it can't prove this the nested loop has to do
multiple index lookups making it look very poor compared to a merge join or
hash join.

There are also cases where Oracle knows that the plan node will involve
reading multiple partitions but not all.

-- 
greg