Ask the Expert

Partitioning a table in Oracle to 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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: