DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Which partitioning strategy is better - Range or Hash?

  1. #1
    Join Date
    Mar 2002
    Posts
    2

    Question

    We are building a Loyalty system where the data is centered around the customer. We have the customer, all his transactions and the loyalty points derived from the transactions. The volume is expected to be more than 14 million customers and point transactions more than 30-40 million.
    In such a scenario, if I want to partition the tables of customer, his transaction and point tables, which strategy is better.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Depends.

    Will you be keeping the point transactions forever?
    Do you have multiple filesystems?
    Will you retrive by index or FTS?

    There's a lot of variables involved with partitioning and it would be difficult to suggest a strategy without knowing the characteristics of the system.
    Jeff Hunter

  3. #3
    Join Date
    Mar 2002
    Posts
    2
    The point transactions are to be retained only for three quarters(current qrtr + 2 previous qrtrs). Only the customer information is to be retained. All cusotmers who are deactivated can be removed periodically.

    We are planning to retreive based on indexes and we are having multiple file systems.

    I am trying to understand how we can have partitions that store records based on say the customer status(active, deactive), the status of various request placed by the cutomer(like change in personal details, redemption of loyalty points etc.) whose status gets closed on a daily basis. The processing would be real fast if I have all the open request alone in one partition. Can we have such dynamic partitions? Can we try to move closed status records to other partition on a periodic basis?is it feasible?What is the overhead?

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    If you are looking to truncate the data, go RANGE. HASH partitioning is used to spread the rows across the partitions, thereby making the partitions the same size and balancing the load. I also depend on your retention period for the data and the way in which data is archived or deleted/truncate.

    A huge benefit of RANGE partitioning, is that you can partition on a date and then truncate the paritition once the data becomes unwanted. You can't perform this on HASH paritions, simple becasue the data (iin my case) would be spread across all paritions, therefore truncating a partition based on date wouldn't be possible.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

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