Oracle7 Server Tuning

Library

Product

Contents

Index

Managing Partition Views

Managing Partition Views
Partition View Guidelines
Defining Partition Views
Partition Views: Example

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.

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:

added and dropped independently and efficiently
reorganized, backed up and restored independently
split, merged and loaded incrementally while maintaining local indexes. They can also be loaded in parallel.
used in a SELECT clause (query or subquery)
partitioned simultaneously by more than one column
split, added or dropped online by replacing the view when ready to switch to a new configuration

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.

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:

The predicates in the check constraints or WHERE clauses for the branches must be of the form:
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.
At least one predicate of the same form exists in the query referencing the view.

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
UNION ALL
SELECT * FROM emp2 WHERE e = 2
UNION ALL 
... 
UNION ALL 
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:

the check constraint predicates are not evaluated per row for queries
the check constraint predicates guard against inserting rows into the wrong partition
it is easier to query the data dictionary and find the partitioning criteria

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');
ALTER TABLE Q2_SALES ADD CONSTRAINT C1 check (sale_date >=
  'Apr-01-1995' and sale_date < 'Jun-30-1995');
ALTER TABLE Q3_SALES ADD CONSTRAINT C2 check (sale_date >=
  'Jul-01-1995' and sale_date < 'Sep-30-1995');
ALTER TABLE Q4_SALES ADD CONSTRAINT C3 check (sale_date >=
  'Oct-01-1995' and sale_date < 'Dec-31-1995');
CREATE VIEW sales AS
  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:

CREATE VIEW sales AS 
  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')
   disable,
      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')   
   disable,
     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.

EXPLAIN PLAN Output

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

VIEW	This entry should include optimizer cost information.
UNION ALL	This entry should specify the option PARTITION for partition views.
FILTER	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:

not all partitions have the same columns, in the same order, with the same data types as in the CREATE TABLE statements
not all partitions have the same indexes, defined on the same columns, in the same order as in the CREATE INDEX statement

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.

Example

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
------------------------------------------------------------------

 VIEW LINE_ITEM  
  UNION_ALL PARTITION
   TABLE ACCESS FULL LINE_ITEM_1992 
  FILTER 
    TABLE ACCESS FULL LINE_ITEM_1993

Prev Next

Library

Product

Contents

Index