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: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 19 Jun 2007 17:13:33 -0700
Message-ID: <1182298410.414043@bubbleator.drizzle.com>


Timasmith wrote:

> 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.)

Maintenance is dependent upon factors you've not mentioned such as whether data is maintained forever or ages out. Tom Kyte has some great advice at asktom.oracle.com on how to make this simple to do.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jun 19 2007 - 19:13:33 CDT

Original text of this message

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