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.