Q

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.

This was first published in April 2004
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close