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 partition-level partitioning information, partition storage parameters, and 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.
• This view does not display the OWNER column
  

  

This table presents a comprehensive view of the parameters and characteristics associated with partitioned tables in Oracle, useful for administrative and performance tuning tasks.

Column	Datatype	NULL	Description
TABLE_OWNER	VARCHAR2(30)	NOT NULL	Table owner
TABLE_NAME	VARCHAR2(30)	NOT NULL	Table name
COMPOSITE	VARCHAR2(3)	NOT NULL	YES if the table is composite-partitioned, NO if it is not
PARTITION_NAME	VARCHAR2(30)		Partition name
SUBPARTITION_COUNT	NUMBER		If this is a Local index on a table partitioned using a Composite method, the number of subpartitions in the partition
HIGH_VALUE	LONG		Partition bound value expression
HIGH_VALUE_LENGTH	NUMBER	NOT NULL	Length of partition bound value expression
PARTITION_POSITION	NUMBER	NOT NULL	Position of the partition within the table
TABLESPACE_NAME	VARCHAR2(30)		Name of the tablespace containing the partition
PCT_FREE	NUMBER	NOT NULL	Minimum percentage of free space in a block
PCT_USED	NUMBER	NOT NULL	Minimum percentage of used space in a block
INI_TRANS	NUMBER	NOT NULL	Initial number of transactions
MAX_TRANS	NUMBER	NOT NULL	Maximum number of transactions
INITIAL_EXTENT	NUMBER	NOT NULL	Size of the initial extent in bytes (for a range partition); size of the initial extent in blocks (for a composite partition)
NEXT_EXTENT	NUMBER		Size of secondary extents in bytes (for a range partition); size of secondary extents in blocks (for a composite partition)
MIN_EXTENT	NUMBER	NOT NULL	Minimum number of extents allowed in the segment
MAX_EXTENT	NUMBER	NOT NULL	Maximum number of extents allowed in the segment
PCT_INCREASE	NUMBER	NOT NULL	Percentage increase in extent size
FREELISTS	NUMBER		Number of process freelists allocated in this segment
FREELIST_GROUPS	NUMBER		Number of freelist groups allocated in this segment
LOGGING	VARCHAR2(3)		Logging attribute of partition
NUM_ROWS	NUMBER		Number of rows in the partition
BLOCKS	NUMBER		Number of used blocks in the partition
EMPTY_BLOCKS	NUMBER		Number of empty (never used) blocks in the partition
AVG_SPACE	NUMBER		Average available free space in the partition
CHAIN_CNT	NUMBER		Number of chained rows in the partition
AVG_ROW_LEN	NUMBER		Average row length, including row overhead
SAMPLE_SIZE	NUMBER		Sample size used in analyzing this partition
LAST_ANALYZED	DATE		Date on which this partition was most recently analyzed
BUFFER_POOL	VARCHAR2(7)		The default buffer pool to be used for the partition blocks
GLOBAL_STATS	VARCHAR2(3)		Indicates whether statistics were collected for the partition as a whole (YES) or were estimated from statistics on underlying subpartitions (NO)
USER_STATS	VARCHAR2(3)		Indicates whether statistics were entered directly by the user (YES) or not (NO)

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.