Re: webmail user mailbox schema design?

From: Tegiri Nenashi <>
Date: Fri, 1 May 2009 10:51:19 -0700 (PDT)
Message-ID: <>

On May 1, 9:37 am, ddf <> wrote:
> Partitioning the MESSAGES table on user_id could provide speed and
> manageability by segregating each users messages to a single
> partition; partition pruning would eliminate visits to unrelated
> partitions effectively reducing the data set to a fraction of the
> total number of messages stored.

I struggle to understand a single advantage of partitioning. Well, you listed one advantage -- easy deletion. However, since when deletion became a determining factor in database design?

Let me list disadvantages.

  • It is just yet another complication on top of already messy vendor implementation. A partitioned table is fundamentally a union view; some database vendors (oracle), however, have decided that it is something completely different.
  • I refuse to understand why a single solution - table - doesn't scale, and where the demarcation line between "small" and "large" lies.
  • Just look onto the syntax

create table trans (

	trans_id	number,
	trans_dt	date,
	product_code	number,
	store_id	number,
	trans_amount	number(12,2)

partition by range (trans_dt)
  partition y05q1 values less than (to_date('04/01/2005','mm/dd/ yyyy')) tablespace y05q1,
  partition y05q2 values less than (to_date('07/01/2005','mm/dd/ yyyy')) tablespace y05q2,
  partition y05q3 values less than (to_date('10/01/2005','mm/dd/ yyyy')) tablespace y05q3,
  partition y05q4 values less than (to_date('01/01/2006','mm/dd/ yyyy')) tablespace y05q4,
  partition pmax values less than (maxvalue) tablespace users )

The idea that DBA have to revisit that table definition every quarter is just beyond me. Apparently oracle DBA has too little to do than to tell computer in details how to organize its storage in each and every minute detail. I propose browser vendors roll out a special DBA edition where a user has to explicitly allocate heap memory for each web page.

And I'm not even going into kitchen sink of subpartitions... Received on Fri May 01 2009 - 12:51:19 CDT

Original text of this message