Lesson 7

Oracle Partitioning Summary. This module discussed the Oracle partitioned features, and how they can be used to improve the performance of the Oracle database. The main points of this module are:

1. Partitioned tables have a real performance benefit for Oracle maintenance and Oracle queries.
2. Oracle offers two types of index for partitioned tables, global and local.
3. Oracle provides several new views to show the data characteristics of table and index partitions, most notably DBA_TAB_PARTITIONS and DBA_IDX_PARTITIONS.

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.

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

Each partition of a table or index must have the same logical attributes, such as column names, data types, and constraints. For example, all partitions in a table share the same column and constraint definitions. However, each partition can have separate physical attributes, such as the tablespace to which it belongs.

The partition key is a set of one or more columns that determines the partition in which each row in a partitioned table should go. Each row is unambiguously assigned to a single partition. In the sales table, you could specify the time_id column as the key of a range partition. The database assigns rows to partitions based on whether the date in this column falls in a specified range. Oracle Database automatically directs insert, update, and delete operations to the appropriate partition by using the partition key.

Oracle Partitioning offers several partitioning strategies that control how the database places data into partitions. The basic strategies are range, list, and hash partitioning. A single-level partitioning uses only one method of data distribution, for example, only list partitioning or only range partitioning. In composite partitioning, a table is partitioned by one data distribution method and then each partition is further divided into subpartitions using a second data distribution method. For example, you could use a list partition for channel_id and a range subpartition for time_id.

The following terms used in this module may be new to you.

1. Online Transaction Processing (OLTP):Online transaction processing (OLTP) is a class of information systems that facilitate and manage transaction-oriented applications, typically for data entry and retrieval transaction processing on a database management system.
2. (DSS)Decision Support System : A decision support system (DSS) is a computer program application that analyzes business data and presents it to users to make better decisions.

The next module explores specific tuning techniques for Oracle indexes.

Before you go on, test your knowledge of Oracle partitioning with a short Quiz.
Oracle Partitioning - Quiz