Partitioning is probably the single best option available for increasing the performance related to large tables. Partitioning is a way to increase efficiency by accessing smaller pieces of a table or index instead of accessing the full table or index.
This can be particularly useful when one or more users are accessing multiple parts of the same table. If these partitions (pieces) of the table reside on different devices, the throughput is greatly increased. Partitions can also be backed up and recovered independently of each other (even while they are in use), eliminating potential disk I/O issues during backup times. Only when partitions are properly implemented are Oracle’s best performance-enhancing features realized. The best way to understand partitioning is to look at an example. Consider the following simple example, where you partition the DEPT table into three partitions (pieces) using the DEPTNO column.
The TABLE DEPT is created with three partitions:
This example builds three distinct partitions on the DEPT table. The key to getting better throughput is to ensure that each partition is placed on a different physical disk so that all three partitions can be accessed simultaneously if you are not using ASM. The tablespaces DEPT1, DEPT2, and DEPT3 must have physical files that are located on different physical disks. Remember that the tablespace is the logical holder of information where the datafile is the physical disk. You can have one tablespace that includes multiple datafiles, but a datafile can only relate to a single tablespace. The key to partitioning to improve disk I/O is to ensure that the partitions that will be accessed simultaneously are either located on different physical disks or use ASM.
Data is then entered into all three table partitions:
The DEPT table still looks like a single table when you select from it:
Here you selected all records from all of the partitions in the preceding example. In the next three examples, you select individually from each partition.
In this instance, you select from a single partition and access only a single partition:
Note that in the final example, you eliminate the need to access the first or second partition (partition elimination). Partitioning indexes and using the parallel option along with partitions make partitioning even more powerful.
TIP
To minimize disk I/O on a single large table, you can break the table into multiple partitions that reside in tablespaces on different physical disks.
Leave a Reply