Re: webmail user mailbox schema design?

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Fri, 01 May 2009 14:01:47 -0500
Message-ID: <2NHKl.15848$pr6.9489_at_flpi149.ffdc.sbc.com>



Tegiri Nenashi wrote:
> On May 1, 9:37 am, ddf <orat..._at_msn.com> 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 - 14:01:47 CDT

Original text of this message