Oracle7 Tuning, release 7.3.3 Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

Managing Partition Views

This section explains how to create and manage partition views, and includes the following topics:

Attention: In Oracle8 partitioned tables will provide most of the functionality currently provided by partition views, plus additional performance and manageability features. You should therefore use only those aspects of this feature which are easily migratable to partitioned tables. Features which might introduce migration difficulty have been indicated in the relevant places in this chapter.

See Also: Oracle7 Server Concepts for a complete discussion of partitioning.

Partition View Guidelines

Partition View Highlights

You may wish to create an index, reorganize the database, or perform other operations, only to discover that the resulting downtime may be too long for very large or mission-critical tables. One way to avoid significant downtime during operations is to create and use partition views.

You can use partition views by dividing very large tables into multiple, small pieces (partitions), which offer significant improvements in availability, administration and table scan performance. You create a partition view by dividing a large table into multiple physical tables using partitioning criteria. Then, for future queries, you can bring the table together as a whole. Also, you can use a key range to select from a partition view only the partitions that fall within that range.

Partition views offer increased manageability and flexibility during queries. Individual partitions can be:

Rules and Guidelines for Use

Guidelines: To create and maintain partition views, follow these guidelines:

To use partition views, the PARTITION_VIEW_ENABLED parameter must be set.


DDL commands must be issued separately for each underlying table.


For example, to add an index to a partition view, you must add indexes to all underlying tables. To analyze a partition view, you must analyze all underlying tables. However, you can submit operations on each partition in parallel.


Administrative operations must be performed as operations on the underlying tables of the partition view, not on the partition view itself.


For example, a split operation consists of either one or two CREATE TABLE AS SELECT operations (one if the split is "in place"), followed by a redefining of the partition view's view text.


You can create referential integrity constraints on underlying tables, but for the constraints to be true for the partition view, the primary key must contain the partition column.


Similarly, you can have an unique index on underlying tables, but for uniqueness to be true for the partition view, the partition column must be contained in the unique index. (You can have only one unique index.)


Every partition has its own index, so any index lookup must be done in all indexes for partitions that are not skipped.


A partition view cannot be the target of a DML statement.


Partition views do not support concatenated partitioning keys.


SQL*Loader does not support partition views.


Compared to non-partitioned tables, partition views should not add significant CPU overhead.

Rules for Use: This section describes the circumstances under which UNION ALL views let the optimizer push join predicates into the branches of the view and use indexes to execute the joins. A UNION ALL view functions in this way if each branch has a query that conforms to the following rules:

It has exactly one table in the FROM clause and it is a base table.


The query in the branch can have a WHERE clause and still function in this way, provided that either PARTITION_VIEW_ENABLED is true, or that the WHERE clause does not contain a subquery.


None of the following are used within the query: group by, aggregate functions, distinct, rownum, start-with/connect by


The select list is "*" or the explicit expansion of "*".


The schema type of all branches is the same.


The indexes are on the same columns and of the same number on each branch.


Corresponding indexes in each branch are the same type, such as all bitmap, all reverse, or all regular B-tree.


This feature is available only with cost-based optimization.

Note: These UNION ALL views function as described above whether or not the PARTITION_VIEW_ENABLED parameter is set. Partition elimination, however, is only available with this parameter set.

Prospective Migration Issues: The following features of partition views may cause difficulty in migrating to Oracle8 partitioned tables. For this reason their use is not recommended.

Database links (that is, remote tables)


Non-contiguous partition bounds (holes), or overlapping partitioning bounds


Inclusive upper bounds for the partitioning constraints (such as "col <= '1-MAR-1995'", or "col between ...")


Multiple partitioning bounds for a single partition view table


Using predicates besides the partitioning bounds in the WHERE clause of each union-all branch


Using check or referential constraints which are applicable to only a subset of the partitions


Partition Elimination

Overview: When a query contains a predicate that constrains the result set to a subset of a view's partitions, the optimizer chooses a plan that skips, or eliminates partitions that are not needed for the query. This partition elimination takes place at run time, when the execution plan references all partitions.

Note that the size of an execution plan is not reduced simply because partitions are skipped. In fact, the size of the execution plan is proportional to the number of partitions, and there is a practical upper limit on the number of partitions you can have: from a few dozen for tables with a large number of columns, to a few hundred for tables with a small number of columns. Also, even when partitions are skipped, there is a small amount of overhead (a fraction of a second) at run time per partition. Such overhead can be noticeable for a query that uses an index to retrieve a small number of records.

In the following example, a partitioned view on column C with the partitions P0, P1 and P2 has the following bounds:

P0.C <  10
P1.C => 10 and P1.C < 20
P2.C => 20 and P2.C < 30

Thus the following query does not access the bulk of the blocks of P0 or P2:

SELECT * FROM partition_view WHERE C BETWEEN 12 and 15;

Rules for Partition Elimination: Constant predicates are considered with column transitivity. For a WHERE clause such as "WHERE c1 = 1 and c1 = 2", the optimizer applies transitivity rules to generate an extra predicate of "1=2". This extra predicate is always false, thus the table need not be accessed.

Transitivity applies to predicates which conform to the following rules:

relation AND relation ...
where relation is of the form column_name relop constant_expression and relop is one of =, >, >=, <, <= .
Note that BETWEEN is allowed by these rules, but IN is not.

Transitivity is useful for operations such as the following:

SELECT * FROM mpview WHERE e = 5; 

when the view is defined as

SELECT * FROM emp1 WHERE e = 1
SELECT * FROM emp2 WHERE e = 2
SELECT * FROM emp5 WHERE e = 5

Partition Views and Parallelism

Partition views are scanned in parallel when all partitions are either skipped 
or accessed in parallel. Partition constraints are for skipping only, not for 
allocating work to query server processes. The number of partitions is 
unrelated to the degree of parallelism. Full parallelism is used even if a single 
partition is not skipped.

The UNION ALL operation can be parallelized if each branch contains a parallel table scan, or if each branch contains an index lookup and the UNION ALL is combined with a parallel nested loops join.

Defining Partition Views

There are two ways to define partition views that will enable you to skip unneeded partitions:

Defining Partition Views Using Check Constraints

Oracle Corporation recommends that you define partition views via the check constraint method, because:

Note: Partition views on a remote table cannot be migrated to partitioned tables in Oracle8.

The following example defines partition views for sales data over a calendar year:

ALTER TABLE Q1_SALES ADD CONSTRAINT C0 check (sale_date < 'Apr-01-1995');
'Apr-01-1995' and sale_date < 'Jun-30-1995');
'Jul-01-1995' and sale_date < 'Sep-30-1995');
'Oct-01-1995' and sale_date < 'Dec-31-1995');
SELECT * FROM Q1_SALES WHERE sale_date < 'Apr-01-1995' UNION ALL
SELECT * FROM Q2_SALES WHERE sale_date >= 'Apr-01-1995' and
sale_date < 'Jun-30-1995' UNION ALL
SELECT * FROM Q3_SALES WHERE sale_date >= 'Jul-01-1995' and
sale_date < 'Sep-30-1995' UNION ALL
SELECT * FROM Q4_SALES WHERE sale_date >= 'Oct-01-1995' and
sale_date < 'Dec-31-1995';

Defining Partition Views Using WHERE Clauses

Alternatively, you can express the criteria in the WHERE clause of a view definition:

SELECT * FROM Q1_SALES WHERE sale_date between
'Jan-01-1995' and 'Mar-31-1995' UNION ALL
SELECT * FROM Q2_SALES WHERE sale_date between
'Apr-01-1995' and 'Jun-30-1995' UNION ALL
SELECT * FROM Q3_SALES WHERE sale_date between
'Jul-01-1995' and 'Sep-30-1995' UNION ALL
SELECT * FROM Q4_SALES WHERE sale_date between
'Oct-01-1995' and 'Dec-31-1995';

Note: An advantage of using this method is that the partition view can be located at a remote database. However, this is not the recommended method for defining a partition view because the partitioning predicate is applied at runtime for all rows in all partitions that are not skipped. Also, if a user mistakenly inserts a row with sale_date = 'Apr-04-1995' in Q1_SALES, the row will "disappear" from the partition view. The partitioning criteria are also difficult to retrieve from the data dictionary because they are all embedded in one long view definition.

Partition Views: Example

To get the most benefit from partition views, the parameter PARTITION_VIEW_ENABLED must be set. This example shows how to:

Create the Tables Underlying the Partition View

This example involves two tables, created with the following syntax:

create table line_item_1992  (
constraint C_send_date_1992
check(send_date < 'Jan-01-1993')
order_key number ,
part_key number ,
source_key number ,
send_date date ,
promise_date date ,
receive_date date );
create table line_item_1993 (
constraint C_send_date_1993
check(send_date => 'Jan-01-1993' and send_date < 'Jan-01-1994')
order_key number ,
part_key number ,
source_key number ,
send_date date ,
promise_date date ,
receive_date date );

Load Each Partition View

You can load each partition using a SQL*Loader process. Each load process can reference the same loader control file (in this example it is "LI.ctl"), but should use a different data file. Also, the data files must match the partitioning criteria given in the send_date check constraints. For improved performance, disable constraints that define the partitioning criteria until after the partitions are loaded.

slqldr scott/tiger direct=true control=LI.ctl data=LI1992.dat 
slqldr scott/tiger direct=true control=LI.ctl data=LI1993.dat

Enable Check Constraints

After loading the partitions, you define the partition view. This example does so by enabling the check constraints. Enabling the check constraints allows the optimizer to recognize and skip irrelevant partitions.

alter table line_item_1992 enable constraint C_send_date_1992 
alter table line_item_1993 enable constraint C_send_date_1993

Add Additional Overlapping Partition Criteria

It is possible to have additional partitioning criteria or partitions that overlap. The application in this example guarantees that all line items are received within 90 days of shipment.

Attention: These constructs will not be directly available for partitioned tables in Oracle8. Using them might introduce additional complexity in migrating partition views to partitioned tables.

alter table line_item_1992
add constraint C_receive_date_1992 check ( receive_date between
'Jan-01-1992' and 'Jan-01-1993' + 90);
alter table line_item_1993
add constraint C_receive_date_1993 check ( receive_date between
'Jan-01-1993' and 'Jan-01-1994' + 90);

Create Indexes for Each Partition

If you need an index on a partition view, you must create the index on each of the partitions. If you do not index each partition identically, the optimizer will be unable to recognize your UNION ALL view as a partition view.

create index part_key_source_key_1992
on line_item_1992 (part_key, source_key)
create index part_key_source_key_1993
on line_item_1993 (part_key, source_key)

Analyze the Partitions

Now analyze the partitions.

analyze table line_item_1992 compute statistics;
analyze table line_item_1993 compute statistics;

Note: The cost-based optimizer is always used with partition views. You must therefore perform ANALYZE at the partition level with partitioned tables. You can submit an ANALYZE statement on each partition in parallel, using multiple logon sessions.

Create the View that Ties the Partitions Together

Once you identify or create the tables that you wish to use, you can create the view text that ties the partitions together.

create or replace view line_item as
select * from line_item_1992 union all
select * from line_item_1993;

Note: To keep users from accessing inconsistent or intermediate states of the partition view, this step has been deferred to a point after the data has been loaded, indexes have been built, and analyses are complete.


To confirm that the system recognizes your partition views, you must look at the following entries in the EXPLAIN PLAN output:



This entry should include optimizer cost information.




This entry should specify the option PARTITION for partition views.




When an operation is a child of the UNION ALL operation, FILTER indicates that a constant predicate was generated for the partition. If the predicate evaluates to FALSE, the partition will be skipped.


These operations appear in the EXPLAIN PLAN output in the OPERATIONS column. The keyword PARTITION for UNION ALL operations appears in the OPTIONS column of the EXPLAIN PLAN. If PARTITION does not appear in this column, either of the following may have occurred:

Note that partitions are shipped using filters only when the constraints and WHERE clause predicates are relations ( =, <, ..., BETWEEN), with no ORs. Thus "col = 1 OR col = 2" would not have a filter. Other predicates (LIKE, IN (...)) do not generate filters.


The following sample EXPLAIN PLAN output indicates that the optimizer recognized that the query has a WHERE clause limiting the data returned from the partition view. Also, the FILTER shows that the system recognized and used the check constraints to eliminate a partition. The keyword PARTITION in the UNION ALL line shows that the system recognized that the underlying tables are the same shape and have the same indexes.

explain plan for select * from line_item
where receive_date = 'Feb-01-1992';

select substr (
lpad (' ',2*(level-1))||decode(id,0,statement_id,operation)
||' '||options||' '||object_name, 1, 79) "plan steps"
from plan_table
start with id = 0
connect by prior id = parent_id;
plan steps


Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index