Printable View
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,
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.
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,
Quote:
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.
Quote:
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.
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.Quote:
when can we opt for Hash partitioning,can u please tell me some of the techniques to speed up queries
Jeff,
What do you mean by Hot Table
Thanks
A table that gets lots of activity.