Re: Query-plan for partitioned UPDATE/DELETE slow and swaps vmem compared to SELECT

Sunday, December 5, 2010, 12:19:29 AM you wrote:

> Hmmm, what happens if I need 10 years of data, in monthly partitions? It
> would be 120 partitions. Can you please elaborate on that limitation?
> Any plans on lifting that restriction?

I'm running a partitioning scheme using 256 tables with a maximum of 16
million rows (namely IPv4-addresses) and a current total of about 2.5
billion rows, there are no deletes though, but lots of updates.

Using triggers or rules on the main table in my case showed to be not very
effective, so I reverted to updating the inherited tables directly. This
way you still can use a SELECT on the main table letting the optimizer do
it's work, but do not run into the problem of oversized shared memory usage
when doing DELETEs or UPDATEs

IMHO if you are using large partitioning schemes, handle the logic of which
table to update or delete in your application. In most cases extending the
underlying application will be much less work and more flexible than trying
to write a dynamic rule/trigger to do the same job.

--
Jochen Erwied | home: jochen(at)erwied(dot)eu +49-208-38800-18, FAX: -19
Sauerbruchstr. 17 | work: joe(at)mbs-software(dot)de +49-2151-7294-24, FAX: -50
D-45470 Muelheim | mobile: jochen(dot)erwied(at)vodafone(dot)de +49-173-5404164