Oracle7 Tuning, release 7.3.3 | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
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.
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:
Guidelines: To create and maintain partition views, follow these guidelines:
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:
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.
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
UNION ALL
SELECT * FROM emp2 WHERE e = 2
UNION ALL
...
UNION ALL
SELECT * FROM emp5 WHERE e = 5
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.
There are two ways to define partition views that will enable you to skip unneeded partitions:
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');
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';
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.
To get the most benefit from partition views, the parameter PARTITION_VIEW_ENABLED must be set. This example shows how to:
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 );
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
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
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);
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)
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.
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:
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
------------------------------------------------------------------
VIEW LINE_ITEM
UNION_ALL PARTITION
TABLE ACCESS FULL LINE_ITEM_1992
FILTER
TABLE ACCESS FULL LINE_ITEM_1993
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |