RelationalDBDesign RelationalDBDesign 

Partitioned Tuning   «Prev  Next»
Lesson 7

Database Partitioned Features - Conclusion

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.

Partition Characteristics

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.

Partition Key

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.

Partitioning Strategies

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.

New terms

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.

Oracle Partitioning - Quiz

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