Re: webmail user mailbox schema design?

From: Michael Austin <>
Date: Fri, 01 May 2009 14:01:47 -0500
Message-ID: <2NHKl.15848$>

Tegiri Nenashi wrote:
> 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?

You, my friend have obviously only ever worked on very small databases. In a DW enviroment, you would be insane to not use partitions. You really need to get out more often if you can't think of a single advantage. :)

In your "shop" you may not ever need this feature, but when you do, you will learn very quickly to appreciate it :)

Before being laid off from my last job, we were adding 200+ partitions a day to a DW that was several hundredTB. When you add hundreds of millions of rows/day, it is paramount to use partitions.

When you need to query with this much data, being able to restrict your search to a single or even a couple of partitions - you WILL become a believer in the use of partitions.

> 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 - 21:01:47 CEST

Original text of this message