when to Partition ?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: when to Partition ?

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    when to Partition ?

    Hi all, Need some advice here - the project that I am working on is going live this fall and we do have some estmiates on the usage but are not sure of the rate of the data growth. Is it wise to partition the table right now on intuition or is it better to wait to see how it is growing and use that as a guideline in deciding the partitions ? These particular tables that I have in mind will be used for OLTP & some online repor

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    depends. If you don't know how you will ultimately partition the tables, how can you pick a partition key?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Partition does NOT work very well with high volume OLTP.

    Tamil

  4. #4
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    I meant to write - "These particular tables that I have in mind will be used for OLTP & online reporting"....
    Tamil, but the partition will help reporting process though....is there any other method that could benefit a table used for both OLTP & reporting ? we are estimating that these tables can end up having about 10 million records..

    Shiva.

  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    I meant to write - "These particular tables that I have in mind will be used for OLTP & online reporting"....
    Tamil, but the partition will help reporting process though....is there any other method that could benefit a table used for both OLTP & reporting ? we are estimating that these tables can end up having about 10 million records..

    Shiva.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well only range partitioning will benefit your reports and range partitioning needs some meaningful partition key in order to use partition elimination

    if you dont know the key we dont neither

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Shiva,

    You must decide what you want - online perf (high volume inserts) or reporting query perf.

    You can't have both.

    Choose one. Let me know it.

    Tamil

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by rshivagami
    we are estimating that these tables can end up having about 10 million records..
    10 Million records? I thought you were talking about a big table. I wouldn't worry about partitioning for only 10 Million rows.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    Originally posted by tamilselvan
    Partition does NOT work very well with high volume OLTP.

    Tamil
    Tamil -
    Can you explain why Partitioning is bad with OLTP?

    I haven't yet had the opportunity to use partitioning - none of our db siizes could justify the cost. However, we have a new app coming in and they request it. The table that will need it has ~80 Million rows and will be about 4 Gig in size. It's mainly an OLTP system - but not HUGE volume. The entire DB is refreshed from the mainframe once a week, takes daily inserts and updates, and has a batch update process every night. The batch refresh is done using SQL*Loader.

    Does the benefit of the load offset the problems with OLTP - in your opinion?

    Thanks for your input...
    Jodie

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    In a HIGH volume OLTP inserts on a partitioned table and on partitioned indexes, oracle has to do MORE work.
    1 Find out the partition key value
    2 Find out the appropriate table partiton where the row should go
    3 Find out the appropriate partition from the indexes where the key valeus should be inserted.

    In a normal heap table, Oracle will do less work for inserts.

    I am not against partition as many of you guys think. In fact I use partition very cautiously.

    Basically, partition is defined for DSS/DW system.
    Even in OLTP, many systems use hash partition and global indexes. You need to evaluate your system carefully. What are the pros and cons of using partition?

    I give a real life example:
    There are 5500 users using a system. The system runs from 7 am to 8 PM Monday to Friday. There is a table, say tableX. On an average the table gets 15 Million rows per day. We decided to keep 3 days worth of data at any time on the table. That means, 45 Million rows.
    On the 4th day, we delete 3 days older data. B/c of heavy concurrent inserts on the table during the day time, initially I thought hash partition with global indexes would be OK. But on the test, insert took longer time. So I changed it to a normal heap table with appropriate freelists and freelist groups. And the perf went up.

    Bottom line:
    If you do not know how many insert/update/delete are going to happen on the table, then you do not partition it.

    Tamil

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