Partitioned Tuning   «Prev 

Oracle Partitioning Summary

This module discussed Oracle partitioning, the types of partitions, how to load a partition, and how partitions are maintained by the DBA.
The main points of this module include:
  1. Large Oracle table and indexes can be partitioned, either by a range of columns values or by a hash value.
  2. Oracle provides several new views to show the data characteristics of table and index partitions, most notably DBA_TAB_PARTITIONS and DBA_IDX_PARTITIONS.
  3. The Oracle optimizer will recognize a partitioned query and only read the effected partition to service the query.
  4. Disk load balancing is easier with partitioned table because each partition typically resides in a separate tablespace with separate data files.
  5. Oracle offers two types of index for partitioned tables, global and local.
  6. Global indexes are best for OLTP applications that need to minimize index node reads.
  7. Local indexes are fastest for data warehouse and DSS applications.
  8. With local partitioned indexes, each partition can be re-built while the other partitions are still servicing SQL queries.

Database Performance Techniques

Using Partitions

You can use partitions to isolate data physically. For example, you can store each month's transactions in a separate partition of an ORDERS table. If you perform a bulk data load or deletion on the table, you can customize the partitions to tune the data manipulation operation. For example:
  1. You can truncate a partition and its indexes without affecting the rest of the table.
  2. You can drop a partition, via the DROP PARTITION clause of the ALTER TABLE command.
  3. You can drop a partition's local index.
  4. You can set a partition to NOLOGGING, reducing the impact of large transactions.
From a performance perspective, the chief advantage of partitions lies in their ability to be managed apart from the rest of the table. For example, being able to truncate a partition enables you to delete a large amount of data from a table (but not all of the table's data) without generating any redo information. In the short term, the beneficiary of this performance improvement is the DBA; in the longer term, the entire enterprise benefits from the improved availability of the data.
You can use the EXCHANGE PARTITION option to greatly reduce the impact your data-loading processes have on system availability. Start by creating an empty table that has the same column structure as your partitioned table. Load your data into the new table and then analyze the new table. Create indexes on the new table to match the partitioned table's indexes; the indexes must be local, and not global, indexes. When these steps are complete, alter the partitioned table using the EXCHANGE PARTITION clause to exchange an empty partition with the new table you populated. All the loaded data will now be accessible via the partitioned table. There is little impact to the system availability during this step because it is a DDL operation