How to Partition orders and order_items tables?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to Partition orders and order_items tables?

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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?

    e.g.
    ORDERS: ORDER_ITEMS
    order_no order_no
    year item_no
    month qty
    day value
    total_value

    Thanks.
    Rgds.
    Sheryl



  2. #2
    Join Date
    Jul 2002
    Posts
    2

    Smile

    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.

  3. #3
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    You could consider using hash-partitioning on order_no.

    Depends on what you want to achieve with the partitioning

    Gert


  4. #4
    Join Date
    Apr 2001
    Posts
    219
    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?
    ______________________
    Applications come and go,
    but the data remains!

  5. #5
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    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).

  6. #6
    Join Date
    Apr 2001
    Posts
    219
    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.

    Good luck!

    ______________________
    Applications come and go,
    but the data remains!

  7. #7
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width