I have two tables I want to partition.
Orders and Order_items, and I want to partition by month and year. The problem is the order_items table do not have the columns month and year, its link via order_no to the Orders table. How do I partition the order_items table?
You will not be able to partition order_items per month and year unless the table contains those fields (date).
This means that you would have to consider denormalising the table to carry a date field. (This could have formed part of the primary key of the orders table).
I might be wrong and am not afraid to be corrected.
You could consider using hash-partitioning on order_no.
Depends on what you want to achieve with the partitioning
Like denevge said, what are your trying to accomplish? There are trade-offs to going to partitioning. To help you avoid those pitfalls, can you give us more information?
The table has over a million rows and increasing each month. The performance on select statements are very slow, and the users query the sum(total_value) by year and month (these tables are in a data warehouse).
I had a DB that contained range partitioning on 5 tables by day, with the biggest table containing 20-50 million rows a day. The average size of a row in my largest table was 66 bytes. The tables would go back 60-90 days. So, I have experience with range partitioning, but not with the others.
If you have 8.1.6, I would go to metalink and look problems with partitioning. You say that you have a million rows and growing. A million rows is not that much, if your avg. row size is 100 bytes( that under 100MB), but if they are 1MB (thats over 900GB). 1MB avg. row size is above avg.. So, if your avg. row size is around 100 bytes at a million rows and performance is bad, something else is wrong. To go to partitioning now will most likely add extra overhead and slow you down. This based on the your avg. row size being around 100 bytes.
For us to get the peerformance we needed out of our database we had to specify the partition name, because Oracle was horrible at narrowing things down.
Think of some mechanism ( based on current data) to add date columns to the current table and partion the table based on data and archive the old data that you are not interested to query anymore.