RelationalDBDesign RelationalDBDesign 


Partitioned Tuning   «Prev  Next»
Lesson 3 DBA Views for Partitioned Objects
Objective List the major DBA views for partitioned objects.

DBA Views for Partitioned Objects

Overview of Partitions

In an Oracle database, partitioning enables you to decompose very large tables and indexes into smaller and more manageable pieces called partitions. Each partition is an independent object with its own name and optionally its own storage characteristics.
For an analogy that illustrates partitioning, suppose an HR manager has one big box that contains employee folders. Each folder lists the employee hire date. Queries are often made for employees hired in a particular month. One approach to satisfying such requests is to create an index on employee hire date that specifies the locations of the folders scattered throughout the box. In contrast, a partitioning strategy uses many smaller boxes, with each box containing folders for employees hired in a given month. Using smaller boxes has several advantages. When asked to retrieve the folders for employees hired in June, the HR manager can retrieve the June box. Furthermore, if any small box is temporarily damaged, the other small boxes remain available. Moving offices also becomes easier because instead of moving a single heavy box, the manager can move several small boxes. From the perspective of an application, only one schema object exists. SQL statements require no modification to access partitioned tables. Partitioning is useful for many different types of database applications, particularly those that manage large volumes of data.

Benefits include:

  1. Increased availability: The unavailability of a partition does not entail the unavailability of the object. The query optimizer automatically removes unreferenced partitions from the query plan so queries are not affected when the partitions are unavailable.
  2. Easier administration of schema objects: A partitioned object has pieces that can be managed either collectively or individually. DDL statements can manipulate partitions rather than entire tables or indexes. Thus, you can break up resource-intensive tasks such as rebuilding an index or table. For example, you can move one table partition at a time. If a problem occurs, then only the partition move must be redone, not the table move. Also, dropping a partition avoids executing numerous DELETE statements.
  3. Reduced contention for shared resources in OLTP systems In some OLTP systems, partitions can decrease contention for a shared resource. For example, DML is distributed over many segments rather than one segment.
  4. Enhanced query performance in data warehouses In a data warehouse, partitioning can speed processing of ad hoc queries. For example, a sales table containing a million rows can be partitioned by quarter.

Using the DBA partition views

In using partitioned objects in Oracle, you must know how to use the DBA partition views. These new data dictionary views are very similar in content to the DBA_TABLES and DBA_INDEXES views, and they contain useful information about the partition data. You can query and analyze these views to decide when to re-organize your data. To run a query that outputs the dba_tab_partitions view, click the Simulation button below.
DBA Tab Partitions View
If you are using Oracle, you will see the DBA_LOB_PARTITIONS and DBA_LOB_SUBPARTITIONS for the Long Object (LOB) datatype.
As discussed, partitioned views are similar to the DBA_TABLES and DBA_INDEXES. For example the Dba_tab_partitionsdba_tab_partitions view contains num_rows, avg_row_len and chain_count, all of which are independent of the table as a whole.
You can see that the DBA_TAB_PARTITIONS view contains the information listed in the table below. This information is particularly useful since one of the best features of partitioning is that we can re-organize partitioned objects independently from other partitions. The num_rows, chain_count, and avg_row_len indicate when to reorganize the partition.

DBA TAB PARTITION table

Num_rows Number of rows in the partition
Avg_row_len Average row length for rows in this partition
Chain_count Number of chained rows for this partition

There are two types of partitions, global and local. The next lesson looks at global partitioned indexes, and how they complement partitioned tables.