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?
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