Partitioned Tuning   «Prev 

DBA Partition Views

  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.

Use of Partitioned Views

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.

What is a "partitioned view"?

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.
[1] partition maintenance operation: A partition-related operation such as adding, exchanging, merging, or splitting table partitions.