Use of Partitioning

[Raminder]

I read somewhere that partitioning the tables will only be effective in improving performance if parallel query operations are performed. Otherwise, they might be a bottleneck.
In what situations should one partition tables?
Parallel queies are set up by inserting hints in the queries or is there any other way?
What are the OS requirements for enabling parallel queries?
In what situations would partitioning be a bad choice?

[jgmagnus]

I am sure Jeff hunter can answer you very precisely regarding all of your questions since he has used partitions frequently.

From what I understand you partition tables for better performance if they are large and have many rows (over 1mil?). You can also partition rows by groups if let's say you need to delete a certain amount of rows (by group) on a very large table during certain times of the month or year or whatever.
I can't imagine it hurting performance on a very large table if you partitioned it, but let's see what some of the experts say ...

- magnus

[julian]

Oracle Parallel Processing is not something that can be explained with a couple of sentances.

I would suggest that you buy and read "Oracle Parallel Processing" (O'Reilly, ISBN 1-56592-701-X).

[marist89]

You don't have to use parallel query to utilize the performance benefits of partitioned tables. Just using partitions can spread your I/O over multiple physical devices. Also, the optimizer has advantages when doing FTS on partitions only. Parallel query definitely does help, but it is not a requirement.

[julian]

I read somewhere that partitioning the tables will only be effective in improving performance if parallel query operations are performed. Otherwise, they might be a bottleneck.

Where did you read that? That is not true.

[Zaggy]

The system I inherited has poor partition purning. The developer overcame this by specifing the partition in the SQL and iterated through the partitions they needed. This iteration design increased performance greatly, but has a flaw. The flaw has to do with the columns of the tables that are partitioned. The partitioning columns are all dates, but the dates come many different sources. So, when you query across multiple tables and specify partitions you can end up with data missing in your result set. But hey, they just found out that they are losing about 33% of there data in the software, Ops!

Here is a link to partitioning:
http://otn.oracle.com/docs/products/...94/parpart.htm

[Raminder]

Thanks for the link Zaggy. It was indeed educative and cleared lot of doubts.