Re: designing a database with partitioning

Timasmith wrote:
> 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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org