Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: designing a database with partitioning

Re: designing a database with partitioning

From: Timasmith <timasmith_at_hotmail.com>
Date: Tue, 19 Jun 2007 13:47:48 -0700
Message-ID: <1182286068.678052.19800@n2g2000hse.googlegroups.com>


On Jun 19, 11:26 am, DA Morgan <damor..._at_psoug.org> wrote:
> Timasmithwrote:
> > I have two types of tables, for sake of argument lets call it these:
>
> > 1) product 10,000,000 rows
> > 2) product_activity 1,000,000,000 rows
>
> > 90% of the type the product table is accessed by product_id, 80% of
> > the time that product id would be in the last 1,000,000 rows of the
> > table i.e. a recent product id.
>
> > 90% of the time the product_activity table is accessed by product_id +
> > a date range - often the last 24 hours.
>
> > One option seems to be not to use partitions at all and have a
> > product_history table and a product_activity_history table. The work
> > falls on the application to use UNION queries to extract data when
> > needed across both tables, implement a criteria for moving the records
> > into the history table and dealing with the issues of related tables
> > referencing product ids that are in other tables.
>
> > Alternatively I could partition the two tables by date range. I am
> > not sure how effective that would be for product but I guess I could
> > hit that table first and if it hits, great - performance saved, and if
> > it doesnt oh well get it from the history table.
>
> > Certainly product_activity seems like a good partitioning contender.
> > Of course maintaining monthly partitions is a lot of work but I guess
> > you could create them in advance for several years.
>
> > Is partitioning the way to go in this case?
>
> Test partitioning by date and subpartitioning by hash of the product_id.
>
> One can speculate, endlessly, about what to do. But no amount of
> prognostication beats running your actual queries in a test environment.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

This is not a question of which of the two alternatives will perform faster. I suspect either schema will meet with the performance requirements by keeping the most recently activity small.

It is more of a question whether people of experience in whether one option is a lot harder to maintain than the other - from an entire system perspective (dbas, developers, users etc.) Received on Tue Jun 19 2007 - 15:47:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US