Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> designing a database with partitioning
I have two types of tables, for sake of argument lets call it these:
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? Received on Tue Jun 19 2007 - 08:00:48 CDT