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

Thread: Table Partitioning

  1. #1
    Join Date
    Aug 2001
    Posts
    40
    Hi,
    i want to know the advantages of Table Partitioning.
    when can one think of implementing table partitioning technique,i mean what should be the size of table be(i know there is no hard and fast rule for this)
    are there any disadvantages of goin for table partitioning technique.
    do we need have any special privillages to implement the partitioninig technique.
    please let me know,
    thanks,

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Partitioning is very useful in situations where you have really large tables and your queries end up scanning large numbers of rows. For example, if you have 200M rows in a table and you are constantly looking for ranges of 5M rows, you have a good candidate for partitioning.

    Also, if you have a VERY hot table with a unique identifier (usually ID number) you can setup hash partitioning on multiple devices to help speed up your queries.

    Personally, I don't look at partitioning a table until it gets over 10M rows unless there are maintenace considerations.

    You need to make sure you understand the different indexing methodologies with partitioning in order to alleviate some of the maintenace head-aches associated with it.

    Partitioning is an option that you have to pay extra for, so that's always a consideration.
    Jeff Hunter

  3. #3
    Join Date
    Aug 2001
    Posts
    40
    Thanks a lot Jeff,
    u mean to say if the table has 10Million Records,then partition can add some difference to it ( i mean for the queries based on it).
    are there any disadvantages?
    when can we opt for Hash partitioning,can u please tell me some of the techniques to speed up queries
    thanks,


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by r_adusumil
    u mean to say if the table has 10Million Records,then partition can add some difference to it ( i mean for the queries based on it).

    Depends. If your queries are executing FTS to pull out only 20% of the data, then yes, range partitioning will probably help. If you have a VERY hot table, then hash partitioning would help if you could spread your table over multiple devices. I would suggest reading http://technet.oracle.com/docs/produ...rti.htm#436962 to understand how partitioning can help you out in your specific application.


    are there any disadvantages?

    Yes, there are disadvantages. You should understand how indexes are affected when you modify your partition structure. See http://technet.oracle.com/docs/produ...rti.htm#430618 for details.


    when can we opt for Hash partitioning,can u please tell me some of the techniques to speed up queries
    You will need to do nothing additional to speed up your queries when you use hash partitioning. The largest benefit comes from spreading your table out on multiple devices.
    Jeff Hunter

  5. #5
    Join Date
    Aug 2001
    Posts
    40
    Jeff,
    What do you mean by Hot Table
    Thanks

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    A table that gets lots of activity.
    Jeff Hunter

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