Problem solve Get help with specific problems with your technologies, process and projects.

All about partitioning

I am new to Oracle and still learning Oracle9i. Could you please elaborate more on partitions?
Partitioning lets you achieve a few things:

1. Higher availability
2. Better performance

When you partition a table, you are saying that part of the rows of the table will go into one partition, another set of rows in another partition, and so on.

Higher availability is achieved when you place these different partitions on different disk devices. Should you lose a disk device, then only attempts to access that partition will fail. If your application accesses other partitions that are still available, the application will not notice. Regular tables do not enjoy this feature.

Better performance is achieved by one of two ways. First, you can spread the table's data among multiple disk devices by placing each partition in a different tablespace on different disk devices. More disk devices means more disk spindles at work to satisfy your I/O requests.

Better performance is really noticable with a concept of "partition pruning." If your table has three partitions, PART_A, PART_B, and PART_C and each partition contains 1 million rows of data, then when you query looks for data that is in only PART_B, the system automatically knows not to look in the other partitions. Instead of reading three million rows, you only have to read one million.

I would recommed reading the Oracle Concepts Guide in your documentation for more information as partitioning can be a large topic.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.