DBA Views for Partitioned Objects

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 of Partitioned Objects 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.

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.
dba_tab_partitions view

1. Describe the dba_tab_partitions view.
2. The output of the desc dba_tab_partitions query is listed above.For purposes of this simulation, the num_rows, chain_count and avg_row_len values are highlighted in yellow. These indicate when to reorganize the partition.

DBA_TAB_PARTITIONS displays partition-level partitioning information, partition storage parameters, and partition statistics determined by ANALYZE statements for all partitions in the database. Its columns are the same as those in "ALL_TAB_PARTITIONS".

DBA_TAB_PARTITIONS provides the partition-level partitioning information, partition storage parameters, and partition statistics determined by ANALYZE statements for all partitions in the database. Its columns are the same as those in "ALL_TAB_PARTITIONS"

• ALL_TAB_PARTITIONS: ALL_TAB_PARTITIONS provides
  1. partition-level partitioning information,
  2. partition storage parameters, and
  3. partition statistics
  
  collected by ANALYZE statements for partitions accessible to the current user.
• Related Views:
  1. DBA_TAB_PARTITIONS provides such information for all partitions in the database
  2. USER_TAB_PARTITIONS provides such information for partitions of all partitioned objects owned by the current user.

1. Type where object_name like '%PARTITION%'; and hit Enter.
2. Query and analyze the DBA partition views to decide when to re-organize your data.

In late Oracle7 releases the concept of partitioned views was introduced. A partitioned view consists of several tables, identical except for the name, which is joined through a view. A partition view is a view that for performance reasons brings together several tables to behave as one. The effect is as though a single table were divided into multiple tables (partitions) that could be independently accessed. Each partition contains some subset of the values in the view, typically a range of values in some column. Among the advantages of partition views are the following:

1. Each table in the view is separately indexed, and all indexes can be scanned in parallel.
2. If Oracle can tell by the definition of a partition that it can produce no rows to satisfy a query, Oracle will save time by not examining that partition.
3. The partitions can be as sophisticated as can be expressed in CHECK constraints.
4. If you have the parallel query option, the partitions can be scanned in parallel.
5. Partitions can overlap. Among the disadvantages of partition views are the following:
6. They (the actual view) cannot be updated. The underlying tables however, can be updated.
7. They have no master index; rather each component table is separately indexed. For this reason, they are recommended for DSS (Decision Support Systems or "data warehousing") applications, but not for OLTP.

Partitioned views are a legacy technique for partitioning data that are used rarely today. Oracle added the ability to partition tables back in Oracle 8, which provides much more functionality than partitioned views, at which point partitioned views became obsolete. The only reason to consider using partitioned views would be if you cannot afford a license for the partitioning option and you are willing to accept the reduced functionality and extra maintenance^[1] required to use partitioned views. The documentation on partitioned views dates back to the Oracle 7 version, which was in use in 1998. 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.

[1]partition maintenance operation: A partition-related operation such as adding, exchanging, merging, or splitting table partitions.