Re: Partitioned table performance

Josh Berkus <josh(at)agliodbs(dot)com> writes:

> Stacy,
>
> > Thanks again for the reply.  So it sounds like the answer to my original
> > question is that it's expected that the pseudo-partitioning would introduce
> > a fairly significant amount of overhead.  Correct?
>
> Correct. For that matter, Oracle table partitioning introduces significant
> overhead, from what I've seen. I don't think there's a way not to.

Well Oracle has lots of partitioning intelligence pushed up to the planner to
avoid overhead.

If you have a query with something like "WHERE date = '2004-01-01'" and date
is your partition key (even if it's a range) then Oracle will figure out which
partition it will need at planning time.

Even if your query is something like "WHERE date = ?" then Oracle will still
recognize that it will only need a single partition at planning time, though
it has to decide which partition at execution time.

We didn't notice any run-time performance degradation when we went to
partitioned tables. Maybe we were so blinded by the joy they brought us on the
maintenance side though. I don't think we specifically checked for run-time
consequences.

But I'm a bit puzzled. Why would Append have any significant cost? It's just
taking the tuples from one plan node and returning them until they run out,
then taking the tuples from another plan node. It should have no i/o cost and
hardly any cpu cost. Where is the time going?

> What would improve the situation significantly, and the utility of
> pseudo-partitioning, is the ability to have a single index span multiple
> partitions. This would allow you to have a segmented index for the
> partitioned axis, yet still use an unsegmented index for the other columns.
> However, there's a *lot* of work to do to make that happen.

In my experience "global indexes" defeat the whole purpose of having the
partitions. They make dropping and adding partitions expensive which was
always the reason we wanted to partition something anyways.

It is handy having a higher level interface to deal with partitioned tables.
You can create a single "local" or "segmented" index and not have to manually
deal with all the partitions as separate tables. But that's just syntactic
sugar.

--
greg