Q

Partitioning a table in Oracle to improve performance

An Oracle user asks if partitioning a table will improve performance.

Will partitioning a large table (several million records) provide any performance improvement if the underlying tablespace (and in fact the entire database) is on a SAN and we are using ASM?

We are running Oracle 10g RAC with ASM. Our allocation of SAN space is 400GB. This 400GB is logically divided via

ASM into 8-50GB disks, of which four are DATA and four are FLASH. The 400GB is presented to Oracle and the OS logically as a simple block of space. However, physically, the space is cobbled together over 14+ disks and managed by the SAN software/hardware. The concept of partitioning a table over several tablespaces in this environment would not seem to provide any benefit. What is your opinion?

You can place your partitioned table on one physical disk and still have performance benefits to partitioning the table. One of the biggest advantages of partitioning a table is to leverage "partition pruning." When a query is issued and all the data resides in one or two partitions, Oracle will prune the other partitions from being used. So instead of reading data in all 20 partitions, maybe only 2 partitions are read. This would still be faster whether or not the partitions are physically spread among multiple disk units or not.
This was first published in July 2008

Dig deeper on Oracle database administration

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close