Table 4-4 lists the views that contain information specific to partitioned tables and indexes:
Table 4-4 Views With Information Specific to Partitioned Tables and Indexes
| View | Description |
|---|---|
|
|
|
|
|
Display partition-level partitioning information, partition storage parameters, and partition statistics generated by the |
|
|
Display subpartition-level partitioning information, subpartition storage parameters, and subpartition statistics generated by the |
|
|
Display the partitioning key columns for partitioned tables. |
|
|
Display the subpartitioning key columns for composite-partitioned tables (and local indexes on composite-partitioned tables). |
|
|
Display column statistics and histogram information for the partitions of tables. |
|
|
Display column statistics and histogram information for subpartitions of tables. |
|
|
Display the histogram data (end-points for each histogram) for histograms on table partitions. |
|
|
Display the histogram data (end-points for each histogram) for histograms on table subpartitions. |
|
|
Display partitioning information for partitioned indexes. |
|
|
Display the following for index partitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the |
|
|
Display the following information for index subpartitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the |
|
|
Display information about existing subpartition templates. |
|
See Also:
|
This chapter describes new features in Oracle Database to support very large databases (VLDB).
|
Note: This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2). |
These are the new features in Oracle Database 11g Release 2 (11.2.0.2) to support very large databases:
Enhancements for managing segments
Segment creation on demand for partitioned tables
This feature enables the creation of partitioned tables with deferred segment creation. With this feature, on-disk segments are not created for a subpartition and its dependent objects until the first row is inserted.
For information about partitioning, refer to "Using Partitioning with Segments".
Enhanced TRUNCATE functionality
If a partition or subpartition has a segment, the truncate feature drops the segment if the DROP ALL STORAGE clause is specified.
For information about partitioning, refer to "Using Partitioning with Segments".
Maintenance package for segment creation on demand
New procedures are added to the PL/SQL DBMS_SPACE_ADMIN package to enable you to maintain segment creation on demand.
For information about partitioning, refer to "Using Partitioning with Segments".
Managing Parallel Statement Queuing
By default, parallel statements are dequeued from the parallel statement queue in a simple first in, first out (FIFO) order. This feature enables you to use resource manager to manage the parallel statement queue by configuring a resource plan that controls the order in which parallel statements are dequeued. For example, you can ensure that parallel statements associated with a high-priority workload or consumer group are dequeued ahead of parallel statements from low-priority consumer groups. Alternatively, you could implement a fair-share policy that dequeues parallel statements based on the resource allocations configured for each consumer group.
For information about parallel statement queuing, refer to "Parallel Statement Queuing". For information about managing parallel statement queuing, refer to "Managing Parallel Statement Queuing with Resource Manager".
|
See Also:
|
VLDB and Partitioning Guide
11g Release 2 (11.2)
E25523-01
September 2011
Oracle Database VLDB and Partitioning Guide, 11g Release 2 (11.2)
E25523-01
Copyright © 2008, 2011, Oracle and/or its affiliates. All rights reserved.
Contributors: Hermann Baer, Eric Belden, Jean-Pierre Dijcks, Steve Fogel, Lilian Hobbs, Paul Lane, Sue K. Lee, Diana Lorentz, Valarie Moore, Tony Morales, Mark Van de Wiel
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
Modern enterprises frequently run mission-critical databases containing upwards of several hundred gigabytes, and often several terabytes of data. These enterprises are challenged by the support and maintenance requirements of very large databases (VLDB), and must devise methods to meet those challenges. This chapter contains an overview of VLDB topics, with emphasis on partitioning as a key component of the VLDB strategy.
This chapter contains the following sections:
Partitioning addresses key issues in supporting very large tables and indexes by decomposing them into smaller and more manageable pieces called partitions, which are entirely transparent to an application. SQL queries and Data Manipulation Language (DML) statements do not need to be modified to access partitioned tables. However, after partitions are defined, Data Definition Language (DDL) statements can access and manipulate individual partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects.
Each partition of a table or index must have the same logical attributes, such as column names, data types, and constraints, but each partition can have separate physical attributes, such as compression enabled or disabled, physical storage settings, and tablespaces.
Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.
Partitioning offers these advantages:
It enables data management operations such as data loads, index creation and rebuilding, and backup and recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
It improves query performance. Often the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.
It significantly reduces the impact of scheduled downtime for maintenance operations.
Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
It increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
Parallel execution provides specific advantages to optimize resource utilization, and minimize execution time. Parallel execution against partitioned objects is key for scalability in a clustered environment. Parallel execution is supported for queries and for DML and DDL.
Partitioning enables faster data access within an Oracle database. Whether a database has 10 GB or 10 TB of data, partitioning can improve data access by orders of magnitude. Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the SELECT statements or DML statements that access that table. You do not need to rewrite your application code to take advantage of partitioning.
A very large database has no minimum absolute size. Although a VLDB is a database like smaller databases, there are specific challenges in managing a VLDB. These challenges are related to the sheer size and the cost-effectiveness of performing operations against a system of that size.
Several trends have been responsible for the steady growth in database size:
For a long time, systems have been developed in isolation. Companies have started to see the benefits of combining these systems to enable cross-departmental analysis while reducing system maintenance costs. Consolidation of databases and applications is a key factor in the ongoing growth of database size.
Many companies face regulations for storing data for a minimum amount of time. The regulations generally result in more data being stored for longer periods of time.
Companies grow by expanding sales and operations or through mergers and acquisitions, causing the amount of generated and processed data to increase. At the same time, the user population that relies on the database for daily activities increases.
Partitioning is a critical feature for managing very large databases. Growth is the basic challenge that partitioning addresses for very large databases, and partitioning enables a divide and conquer technique for managing the tables and indexes in the database, especially as those tables and indexes grow. Partitioning is the feature that allows a database to scale for very large data sets while maintaining consistent performance, without unduly increasing administrative or hardware resources. Chapter 3, "Partitioning for Availability, Manageability, and Performance" provides availability, manageability, and performance considerations for partitioning implementations.
Chapter 9, "Backing Up and Recovering VLDBs" addresses the challenges surrounding backup and recovery for a VLDB.
Storage is a key component of a very large database. Chapter 10, "Storage Management for VLDBs" focuses on best practices for storage in a VLDB.
Information Lifecycle Management (ILM) is a set of processes and policies for managing data throughout its useful life. One important component of an ILM strategy is determining the most appropriate and cost-effective medium for storing data at any point during its lifetime: newer data used in day-to-day operations is stored on the fastest, most highly-available storage tier, while older data which is accessed infrequently may be stored on a less expensive and less efficient storage tier. Older data may also be updated less frequently so it makes sense to compress and store the data as read-only.
Oracle Database provides the ideal environment for implementing your ILM solution. Oracle supports multiple storage tiers, and because all of the data remains in the Oracle database, multiple storage tiers are completely transparent to the application and the data continues to be completely secure. Partitioning provides the fundamental technology that enables data in tables to be stored in different partitions.
Although multiple storage tiers and sophisticated ILM policies are most often found in enterprise-level systems, most companies and most databases need some degree of information lifecycle management. The most basic of ILM operations, archiving older data and purging or removing that data from the database, can be orders of magnitude faster when using partitioning.
For more information about ILM, see Chapter 5, "Using Partitioning for Information Lifecycle Management".
The benefits of partitioning are not just for very large databases; every database, even small databases, can benefit from partitioning. While partitioning is a necessity for large databases, partitioning is obviously beneficial for the smaller database as well. Even a database whose size is measured in megabytes can gain the same type of performance and manageability benefits from partitioning as the largest multi-terabyte systems.
For more information about how partitioning can provide benefits in a data warehouse environment, see Chapter 6, "Using Partitioning in a Data Warehouse Environment".
For more information about how partitioning can provide benefits in an OLTP environment, see Chapter 7, "Using Partitioning in an Online Transaction Processing Environment".
This section contains some ideas for improving performance in a parallel execution environment and includes the following topics:
Oracle Database cannot return results to a user process in parallel. If a query returns a large number of rows, execution of the query might indeed be faster. However, the user process can receive the rows only serially. To optimize parallel execution performance for queries that retrieve large result sets, use PARALLEL CREATE TABLE ... AS SELECT or direct-path INSERT to store the result set in the database. At a later time, users can view the result set serially.
Performing the SELECT in parallel does not influence the CREATE statement. If the CREATE statement is executed in parallel, however, the optimizer tries to make the SELECT run in parallel also.
When combined with the NOLOGGING option, the parallel version of CREATE TABLE ... AS SELECT provides a very efficient intermediate table facility, for example:
CREATE TABLE summary PARALLEL NOLOGGING AS SELECT dim_1, dim_2 ..., SUM (meas_1) FROM facts GROUP BY dim_1, dim_2;
These tables can also be incrementally loaded with parallel INSERT. You can take advantage of intermediate tables using the following techniques:
Common subqueries can be computed once and referenced many times. This can allow some queries against star schemas (in particular, queries without selective WHERE-clause predicates) to be better parallelized. Note that star queries with selective WHERE-clause predicates using the star-transformation technique can be effectively parallelized automatically without any modification to the SQL.
Decompose complex queries into simpler steps to provide application-level checkpoint or restart. For example, a complex multitable join on a one terabyte database could run for dozens of hours. A failure during this query would mean starting over from the beginning. Using CREATE TABLE ... AS SELECT or PARALLEL INSERT AS SELECT, you can rewrite the query as a sequence of simpler queries that run for a few hours each. If a system failure occurs, the query can be restarted from the last completed step.
Implement manual parallel delete operations efficiently by creating a new table that omits the unwanted rows from the original table, and then dropping the original table. Alternatively, you can use the convenient parallel delete feature, which directly deletes rows from the original table.
Create summary tables for efficient multidimensional drill-down analysis. For example, a summary table might store the sum of revenue grouped by month, brand, region, and salesman.
Reorganize tables, eliminating chained rows, compressing free space, and so on, by copying the old table to a new table. This is much faster than export/import and easier than reloading.
Be sure to use the DBMS_STATS package to gather optimizer statistics on newly created tables. To avoid I/O bottlenecks, specify a tablespace that is striped across at least as many physical disks as CPUs. To avoid fragmentation in allocating space, the number of files in a tablespace should be a multiple of the number of CPUs. See Oracle Database Data Warehousing Guide, for more information about bottlenecks.
Use the EXPLAIN PLAN statement to see the execution plans for parallel queries. The EXPLAIN PLAN output shows optimizer information in the COST, BYTES, and CARDINALITY columns. You can also use the utlxplp.sql script to present the EXPLAIN PLAN output with all relevant parallel information.
There are several ways to optimize the parallel execution of join statements. You can alter system configuration, adjust parameters as discussed earlier in this chapter, or use hints, such as the DISTRIBUTION hint.
The key points when using EXPLAIN PLAN are to:
Verify optimizer selectivity estimates. If the optimizer thinks that only one row is produced from a query, it tends to favor using a nested loop. This could be an indication that the tables are not analyzed or that the optimizer has made an incorrect estimate about the correlation of multiple predicates on the same table. Extended statistics or a hint may be required to provide the optimizer with the correct selectivity or to force the optimizer to use another join method.
Use hash join on low cardinality join keys. If a join key has few distinct values, then a hash join may not be optimal. If the number of distinct values is less than the degree of parallelism (DOP), then some parallel query servers may be unable to work on the particular query.
Consider data skew. If a join key involves excessive data skew, a hash join may require some parallel query servers to work more than others. Consider using a hint to cause a BROADCAST distribution method if the optimizer did not choose it. Note that the optimizer considers the BROADCAST distribution method only if the OPTIMIZER_FEATURES_ENABLE is set to 9.0.2 or higher. See "V$PQ_TQSTAT" for more information.
The following example illustrates how the optimizer intends to execute a parallel query:
explain plan for SELECT /*+ PARALLEL */ cust_first_name, cust_last_name FROM customers c, sales s WHERE c.cust_id = s.cust_id; ---------------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | PX COORDINATOR | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | | 3 | NESTED LOOPS | | | 4 | PX BLOCK ITERATOR | | | 5 | TABLE ACCESS FULL | CUSTOMERS | | 6 | PARTITION RANGE ALL | | | 7 | BITMAP CONVERSION TO ROWIDS| | | 8 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | ---------------------------------------------------------- Note ----- - Computed Degree of Parallelism is 2 - Degree of Parallelism of 2 is derived from scan of object SH.CUSTOMERS
When you want to refresh your data warehouse database using parallel insert, update, or delete operations on a data warehouse, there are additional issues to consider when designing the physical database. These considerations do not affect parallel execution operations. These issues are:
If a parallel restriction is violated, the operation is simply performed serially. If a direct-path INSERT restriction is violated, then the APPEND hint is ignored and a conventional insert operation is performed. No error message is returned.
For tables that do not have the parallel DML itl invariant property (tables created before Oracle Database release 9.2 or tables that were created with the COMPATIBLE initialization parameter set to less than 9.2), the degree of parallelism (DOP) equals the number of partitions or subpartitions. That means that, if the table is not partitioned, the query runs serially. To see what tables do not have this property, issue the following statement:
SELECT u.name, o.name FROM obj$ o, tab$ t, user$ u WHERE o.obj# = t.obj# AND o.owner# = u.user# AND bitand(t.property,536870912) != 536870912;
For information about the interested transaction list (ITL), also called the transaction table, refer to Oracle Database Concepts.
If you have global indexes, a global index segment and global index blocks are shared by server processes of the same parallel DML statement. Even if the operations are not performed against the same row, the server processes can share the same index blocks. Each server transaction needs one transaction entry in the index block header before it can make changes to a block. Therefore, in the CREATE INDEX or ALTER INDEX statements, you should set INITRANS, the initial number of transactions allocated within each data block, to a large value, such as the maximum DOP against this index.
There is a limitation on the available number of transaction free lists for segments in dictionary-managed tablespaces. After a segment has been created, the number of process and transaction free lists is fixed and cannot be altered. If you specify a large number of process free lists in the segment header, you might find that this limits the number of transaction free lists that are available. You can abate this limitation the next time you re-create the segment header by decreasing the number of process free lists; this leaves more room for transaction free lists in the segment header.
For UPDATE and DELETE operations, each server process can require its own transaction free list. The parallel DML DOP is thus effectively limited by the smallest number of transaction free lists available on the table and on any of the global indexes the DML statement must maintain. For example, if the table has 25 transaction free lists and the table has two global indexes, one with 50 transaction free lists and one with 30 transaction free lists, the DOP is limited to 25. If the table had 40 transaction free lists, the DOP would have been limited to 30.
The FREELISTS parameter of the STORAGE clause is used to set the number of process free lists. By default, no process free lists are created.
The default number of transaction free lists depends on the block size. For example, if the number of process free lists is not set explicitly, a 4 KB block has about 80 transaction free lists by default. The minimum number of transaction free lists is 25.
Parallel DDL and parallel DML operations can generate a large number of redo logs. A single ARCH process to archive these redo logs might not be able to keep up. To avoid this problem, you can spawn multiple archiver processes manually or by using a job queue.
Parallel DML operations use a large number of data, index, and undo blocks in the buffer cache during a short interval. For example, suppose you see a high number of free_buffer_waits after querying the V$SYSTEM_EVENT view, as in the following syntax:
SELECT TOTAL_WAITS FROM V$SYSTEM_EVENT WHERE EVENT = 'FREE BUFFER WAITS';
In this case, you should consider increasing the DBWn processes. If there are no waits for free buffers, the query does not return any rows.
The [NO]LOGGING clause applies to tables, partitions, tablespaces, and indexes. Virtually no log is generated for certain operations (such as direct-path INSERT) if the NOLOGGING clause is used. The NOLOGGING attribute is not specified at the INSERT statement level but is instead specified when using the ALTER or CREATE statement for a table, partition, index, or tablespace.
When a table or index has NOLOGGING set, neither parallel nor serial direct-path INSERT operations generate redo logs. Processes running with the NOLOGGING option set run faster because no redo is generated. However, after a NOLOGGING operation against a table, partition, or index, if a media failure occurs before a backup is performed, then all tables, partitions, and indexes that have been modified might be corrupted.
Direct-path INSERT operations (except for dictionary updates) never generate redo logs if the NOLOGGING clause is used. The NOLOGGING attribute does not affect undo, only redo. To be precise, NOLOGGING allows the direct-path INSERT operation to generate a negligible amount of redo (range-invalidation redo, as opposed to full image redo).
For backward compatibility, [UN]RECOVERABLE is still supported as an alternate keyword with the CREATE TABLE statement. This alternate keyword might not be supported, however, in future releases.
At the tablespace level, the logging clause specifies the default logging attribute for all tables, indexes, and partitions created in the tablespace. When an existing tablespace logging attribute is changed by the ALTER TABLESPACE statement, then all tables, indexes, and partitions created after the ALTER statement have the new logging attribute; existing ones do not change their logging attributes. The tablespace-level logging attribute can be overridden by the specifications at the table, index, or partition level.
The default logging attribute is LOGGING. However, if you have put the database in NOARCHIVELOG mode, by issuing ALTER DATABASE NOARCHIVELOG, then all operations that can be done without logging do not generate logs, regardless of the specified logging attribute.
Multiple processes can work simultaneously to create an index. By dividing the work necessary to create an index among multiple server processes, Oracle Database can create the index more quickly than if a single server process created the index serially.
Parallel index creation works in much the same way as a table scan with an ORDER BY clause. The table is randomly sampled and a set of index keys is found that equally divides the index into the same number of pieces as the DOP. A first set of query processes scans the table, extracts key-rowid pairs, and sends each pair to a process in a second set of query processes based on a key. Each process in the second set sorts the keys and builds an index in the usual fashion. After all index pieces are built, the parallel execution coordinator simply concatenates the pieces (which are ordered) to form the final index.
Parallel local index creation uses a single server set. Each server process in the set is assigned a table partition to scan and for which to build an index partition. Because half as many server processes are used for a given DOP, parallel local index creation can be run with a higher DOP. However, the DOP is restricted to be less than or equal to the number of index partitions you want to create. To avoid this limitation, you can use the DBMS_PCLXUTIL package.
You can optionally specify that no redo and undo logging should occur during index creation. This can significantly improve performance but temporarily renders the index unrecoverable. Recoverability is restored after the new index is backed up. If your application can tolerate a window where recovery of the index requires it to be re-created, then you should consider using the NOLOGGING clause.
The PARALLEL clause in the CREATE INDEX statement is the only way in which you can specify the DOP for creating the index. If the DOP is not specified in the parallel clause of the CREATE INDEX statement, then the number of CPUs is used as the DOP. If there is no PARALLEL clause, index creation is done serially.
When creating an index in parallel, the STORAGE clause refers to the storage of each of the subindexes created by the query server processes. Therefore, an index created with an INITIAL value of 5 MB and a DOP of 12 consumes at least 60 MB of storage during index creation because each process starts with an extent of 5 MB. When the query coordinator process combines the sorted subindexes, some extents might be trimmed, and the resulting index might be smaller than the requested 60 MB.
When you add or enable a UNIQUE or PRIMARY KEY constraint on a table, you cannot automatically create the required index in parallel. Instead, manually create an index on the desired columns, using the CREATE INDEX statement and an appropriate PARALLEL clause, and then add or enable the constraint. Oracle Database then uses the existing index when enabling or adding the constraint.
Multiple constraints on the same table can be enabled concurrently and in parallel if all the constraints are in the ENABLE NOVALIDATE state. In the following example, the ALTER TABLE ... ENABLE CONSTRAINT statement performs the table scan that checks the constraint in parallel:
CREATE TABLE a (a1 NUMBER CONSTRAINT ach CHECK (a1 > 0) ENABLE NOVALIDATE) PARALLEL; INSERT INTO a values (1); COMMIT; ALTER TABLE a ENABLE CONSTRAINT ach;
This section provides an overview of parallel DML functionality. The topics covered include:
|
See Also:
|
The functionality available using an INSERT statement can be summarized as shown in Table 8-5:
Table 8-5 Summary of INSERT Features
| Insert Type | Parallel | Serial | NOLOGGING |
|---|---|---|---|
|
Conventional |
No See text in this section for information about using the |
Yes |
No |
|
Direct-path
( |
Yes, but requires
and one of the following:
Or the following
|
Yes, but requires:
|
Yes, but requires:
|
If parallel DML is enabled and there is a PARALLEL hint or PARALLEL attribute set for the table in the data dictionary, then insert operations are parallel and appended, unless a restriction applies. If either the PARALLEL hint or PARALLEL attribute is missing, the insert operation is performed serially. Note that automatic DOP only parallelizes the DML part of a SQL statement if and only if parallel DML is enabled or forced.
If parallel DML is enabled, then you can use the NOAPPEND hint to perform a parallel conventional insert operation. For example, you can use /*+ noappend parallel */ with the SQL INSERT statement to perform a parallel conventional insert.
SQL> INSERT /*+ NOAPPEND PARALLEL */ INTO sales_hist SELECT * FROM sales;
The advantage of the parallel conventional insert operation is the ability to perform online operations with none of the restrictions of direct-path INSERT. The disadvantage of the parallel conventional insert operation is that this process may be slower than direct-path INSERT.
The append mode is the default during a parallel insert operation: data is always inserted into a new block, which is allocated to the table. Therefore, the APPEND hint is optional. You should use append mode to increase the speed of INSERT operations, but not when space utilization must be optimized. You can use NOAPPEND to override append mode.
The APPEND hint applies to both serial and parallel insert operation: even serial insertions are faster if you use this hint. The APPEND hint, however, does require more space and locking overhead.
You can use NOLOGGING with APPEND to make the process even faster. NOLOGGING means that no redo log is generated for the operation. NOLOGGING is never the default; use it when you want to optimize performance. It should not typically be used when recovery is needed for the table or partition. If recovery is needed, be sure to perform a backup immediately after the operation. Use the ALTER TABLE [NO]LOGGING statement to set the appropriate value.
When the table or partition has the PARALLEL attribute in the data dictionary, that attribute setting is used to determine parallelism of INSERT, UPDATE, and DELETE statements and queries. An explicit PARALLEL hint for a table in a statement overrides the effect of the PARALLEL attribute in the data dictionary.
You can use the NO_PARALLEL hint to override a PARALLEL attribute for the table in the data dictionary. In general, hints take precedence over attributes.
DML operations are considered for parallelization only if the session is in a PARALLEL DML enabled mode. (Use ALTER SESSION ENABLE PARALLEL DML to enter this mode.) The mode does not affect parallelization of queries or of the query portions of a DML statement.
In the INSERT ... SELECT statement, you can specify a PARALLEL hint after the INSERT keyword, in addition to the hint after the SELECT keyword. The PARALLEL hint after the INSERT keyword applies to the INSERT operation only, and the PARALLEL hint after the SELECT keyword applies to the SELECT operation only. Thus, parallelism of the INSERT and SELECT operations are independent of each other. If one operation cannot be performed in parallel, it has no effect on whether the other operation can be performed in parallel.
The ability to parallelize insert operations causes a change in existing behavior if the user has explicitly enabled the session for parallel DML and if the table in question has a PARALLEL attribute set in the data dictionary entry. In that case, existing INSERT ... SELECT statements that have the select operation parallelized can also have their insert operation parallelized.
If you query multiple tables, you can specify multiple SELECT PARALLEL hints and multiple PARALLEL attributes.
Example 8-10 shows the addition of the new employees who were hired after the acquisition of ACME.
The PARALLEL hint (placed immediately after the UPDATE or DELETE keyword) applies not only to the underlying scan operation, but also to the UPDATE or DELETE operation. Alternatively, you can specify UPDATE or DELETE parallelism in the PARALLEL clause specified in the definition of the table to be modified.
If you have explicitly enabled parallel DML for the session or transaction, UPDATE or DELETE statements that have their query operation parallelized can also have their UPDATE or DELETE operation parallelized. Any subqueries or updatable views in the statement can have their own separate PARALLEL hints or clauses, but these parallel directives do not affect the decision to parallelize the update or delete. If these operations cannot be performed in parallel, it has no effect on whether the UPDATE or DELETE portion can be performed in parallel.
Example 8-11 shows the update operation to give a 10 percent salary raise to all clerks in Dallas.
Example 8-11 Parallelizing UPDATE and DELETE
UPDATE /*+ PARALLEL(employees) */ employees SET SAL=SAL * 1.1 WHERE JOB='CLERK' AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOCATION='DALLAS');
The PARALLEL hint is applied to the UPDATE operation and to the scan.
Example 8-12 shows the removal of all products in the grocery category because the grocery business line was recently spun off into a separate company.
Parallel DML combined with the updatable join views facility provides an efficient solution for refreshing the tables of a data warehouse system. To refresh tables is to update them with the differential data generated from the OLTP production system.
In the following example, assume a refresh of a table named customer that has columns c_key, c_name, and c_addr. The differential data contains either new rows or rows that have been updated since the last refresh of the data warehouse. In this example, the updated data is shipped from the production system to the data warehouse system by means of ASCII files. These files must be loaded into a temporary table, named diff_customer, before starting the refresh process. You can use SQL*Loader with both the parallel and direct options to efficiently perform this task. You can use the APPEND hint when loading in parallel as well.
After diff_customer is loaded, the refresh process can be started. It can be performed in two phases or by merging in parallel, as demonstrated in the following:
The following statement is a straightforward SQL implementation of the update using subqueries:
UPDATE customers SET(c_name, c_addr) = (SELECT c_name, c_addr FROM diff_customer WHERE diff_customer.c_key = customer.c_key) WHERE c_key IN(SELECT c_key FROM diff_customer);
Unfortunately, the two subqueries in this statement affect performance.
An alternative is to rewrite this query using updatable join views. To rewrite the query, you must first add a primary key constraint to the diff_customer table to ensure that the modified columns map to a key-preserved table:
CREATE UNIQUE INDEX diff_pkey_ind ON diff_customer(c_key) PARALLEL NOLOGGING; ALTER TABLE diff_customer ADD PRIMARY KEY (c_key);
You can then update the customers table with the following SQL statement:
UPDATE /*+ PARALLEL(cust_joinview) */ (SELECT /*+ PARALLEL(customers) PARALLEL(diff_customer) */ CUSTOMER.c_name AS c_name CUSTOMER.c_addr AS c_addr, diff_customer.c_name AS c_newname, diff_customer.c_addr AS c_newaddr FROM diff_customer WHERE customers.c_key = diff_customer.c_key) cust_joinview SET c_name = c_newname, c_addr = c_newaddr;
The underlying scans feeding the join view cust_joinview are done in parallel. You can then parallelize the update to further improve performance, but only if the customers table is partitioned.
The last phase of the refresh process consists of inserting the new rows from the diff_customer temporary table to the customers table. Unlike the update case, you cannot avoid having a subquery in the INSERT statement:
INSERT /*+PARALLEL(customers)*/ INTO customers SELECT * FROM diff_customer s);
However, you can guarantee that the subquery is transformed into an anti-hash join by using the HASH_AJ hint. Doing so enables you to use parallel INSERT to execute the preceding statement efficiently. Parallel INSERT is applicable even if the table is not partitioned.
You can combine update and insert operations into one statement, commonly known as a merge. The following statement achieves the same result as all of the statements in "Updating the Table in Parallel" and "Inserting the New Rows into the Table in Parallel":
MERGE INTO customers USING diff_customer ON (diff_customer.c_key = customer.c_key) WHEN MATCHED THEN UPDATE SET (c_name, c_addr) = (SELECT c_name, c_addr FROM diff_customer WHERE diff_customer.c_key = customers.c_key) WHEN NOT MATCHED THEN INSERT VALUES (diff_customer.c_key,diff_customer.c_data);
This section describes how to perform partition and subpartition maintenance operations for both tables and indexes.
This section contains the following topics:
|
See Also:
|
|
Note: The following sections discuss maintenance operations on partitioned tables. Where the usability of indexes or index partitions affected by the maintenance operation is discussed, consider the following:
|
Table 4-1 lists partition maintenance operations that can be performed on partitioned tables and composite partitioned tables, and Table 4-2 lists subpartition maintenance operations that can be performed on composite partitioned tables. For each type of partitioning and subpartitioning, the specific clause of the ALTER TABLE statement that is used to perform that maintenance operation is listed.
Table 4-1 ALTER TABLE Maintenance Operations for Table Partitions
| Maintenance Operation | RangeComposite Range-* | IntervalComposite Interval-* | Hash | ListComposite List-* | Reference |
|---|---|---|---|---|---|
|
|
|
|
|
|
N/AFoot 1 |
|
|
N/A |
N/A |
|
N/A |
N/AFootref 1 |
|
|
|
|
N/A |
|
N/AFootref 1 |
|
|
|
|
|
|
|
|
|
|
|
N/A |
|
N/AFootref 1 |
|
|
|
|
|
|
|
|
Modifying Real Attributes of Partitions |
|
|
|
|
|
|
Modifying List Partitions: Adding Values |
N/A |
N/A |
N/A |
|
N/A |
|
Modifying List Partitions: Dropping Values |
N/A |
N/A |
N/A |
|
N/A |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
N/A |
|
N/AFootref 1 |
|
|
|
|
|
|
|
Footnote 1 These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.
Table 4-2 ALTER TABLE Maintenance Operations for Table Subpartitions
| Maintenance Operation | Composite *-Range | Composite *-Hash | Composite *-List |
|---|---|---|---|
|
|
|
|
|
|
|
N/A |
|
N/A |
|
|
|
N/A |
|
|
|
|
N/A |
|
|
|
|
N/A |
|
|
|
|
|
|
|
Modifying Real Attributes of Partitions |
|
|
|
|
Modifying List Partitions: Adding Values |
N/A |
N/A |
|
|
Modifying List Partitions: Dropping Values |
N/A |
N/A |
|
|
Modifying a Subpartition Template |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
N/A |
|
|
|
|
|
|
|
Note: The first time you use table compression to introduce a compressed partition into a partitioned table that has bitmap indexes and that currently contains only uncompressed partitions, you must do the following:
These actions are independent of whether any partitions contain data and of the operation that introduces the compressed partition. This does not apply to partitioned tables with B-tree indexes or to partitioned index-organized tables. |
Table 4-3 lists maintenance operations that can be performed on index partitions, and indicates on which type of index (global or local) they can be performed. The ALTER INDEX clause used for the maintenance operation is shown.
Global indexes do not reflect the structure of the underlying table. If partitioned, they can be partitioned by range or hash. Partitioned global indexes share some, but not all, of the partition maintenance operations that can be performed on partitioned tables.
Because local indexes reflect the underlying structure of the table, partitioning is maintained automatically when table partitions and subpartitions are affected by maintenance activity. Therefore, partition maintenance on local indexes is less necessary and there are fewer options.
Table 4-3 ALTER INDEX Maintenance Operations for Index Partitions
| Maintenance Operation | Type of Index | Type of Index Partitioning | ||
|---|---|---|---|---|
| Range | Hash and List | Composite | ||
|
|
Global |
|
|
- |
|
Local |
N/A |
N/A |
N/A | |
|
|
Global |
|
- |
- |
|
Local |
N/A |
N/A |
N/A | |
|
Modifying Default Attributes of Index Partitions |
Global |
|
- |
- |
|
Local |
|
|
| |
|
Modifying Real Attributes of Index Partitions |
Global |
|
- |
- |
|
Local |
|
|
| |
|
|
Global |
|
- |
- |
|
Local |
|
|
| |
|
|
Global |
|
- |
- |
|
Local |
|
|
| |
|
|
Global |
|
- |
- |
|
Local |
N/A |
N/A |
N/A | |
Before discussing the individual maintenance operations for partitioned tables and indexes, it is important to discuss the effects of the UPDATE INDEXES clause that can be specified in the ALTER TABLE statement.
By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then rebuild the entire index or, for a global index, each of its partitions. The database lets you override this default behavior if you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the indexes at the time it executes the maintenance operation DDL statement. This provides the following benefits:
The indexes are updated with the base table operation. You are not required to update later and independently rebuild the indexes.
The global indexes are more highly available, because they are not marked UNUSABLE. These indexes remain available even while the partition DDL is executing and can access unaffected partitions in the table.
You need not look up the names of all invalid indexes to rebuild them.
Optional clauses for local indexes let you specify physical and storage characteristics for updated local indexes and their partitions.
You can specify physical attributes, tablespace storage, and logging for each partition of each local index. Alternatively, you can specify only the PARTITION keyword and let the database update the partition attributes as follows:
For operations on a single table partition (such as MOVE PARTITION and SPLIT PARTITION), the corresponding index partition inherits the attributes of the affected index partition. The database does not generate names for new index partitions, so any new index partitions resulting from this operation inherit their names from the corresponding new table partition.
For MERGE PARTITION operations, the resulting local index partition inherits its name from the resulting table partition and inherits its attributes from the local index.
For a composite-partitioned index, you can specify tablespace storage for each subpartition.
The following operations support the UPDATE INDEXES clause:
ADD PARTITION | SUBPARTITION
COALESCE PARTITION | SUBPARTITION
DROP PARTITION | SUBPARTITION
EXCHANGE PARTITION | SUBPARTITION
MERGE PARTITION | SUBPARTITION
MOVE PARTITION | SUBPARTITION
SPLIT PARTITION | SUBPARTITION
TRUNCATE PARTITION | SUBPARTITION
SKIP_UNUSABLE_INDEXES Initialization Parameter
SKIP_UNUSABLE_INDEXES is an initialization parameter with a default value of TRUE. This setting disables error reporting of indexes and index partitions marked UNUSABLE. If you do not want the database to choose an alternative execution plan to avoid the unusable elements, then you should set this parameter to FALSE.
Considerations when Updating Indexes Automatically
The following implications are worth noting when you specify UPDATE INDEXES:
The partition DDL statement takes longer to execute, because indexes that were previously marked UNUSABLE are updated. However, you must compare this increase with the time it takes to execute DDL without updating indexes, and then rebuild all indexes. A rule of thumb is that it is faster to update indexes if the size of the partition is less that 5% of the size of the table.
The DROP, TRUNCATE, and EXCHANGE operations are no longer fast operations. Again, you must compare the time it takes to do the DDL and then rebuild all indexes.
When you update a table with a global index:
The index is updated in place. The updates to the index are logged, and redo and undo records are generated. In contrast, if you rebuild an entire global index, you can do so in NOLOGGING mode.
Rebuilding the entire index manually creates a more efficient index, because it is more compact with better space utilization.
The UPDATE INDEXES clause is not supported for index-organized tables. However, the UPDATE GLOBAL INDEXES clause may be used with DROP PARTITION, TRUNCATE PARTITION, and EXCHANGE PARTITION operations to keep the global indexes on index-organized tables usable. For the remaining operations in the above list, global indexes on index-organized tables remain usable. In addition, local index partitions on index-organized tables remain usable after a MOVE PARTITION operation.
This section describes how to manually add new partitions to a partitioned table and explains why partitions cannot be specifically added to most partitioned indexes.
Use the ALTER TABLE ... ADD PARTITION statement to add a new partition to the "high" end (the point after the last existing partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.
For example, consider the table, sales, which contains data for the current month in addition to the previous 12 months. On January 1, 1999, you add a partition for January, which is stored in tablespace tsx.
ALTER TABLE sales
ADD PARTITION jan99 VALUES LESS THAN ( '01-FEB-1999' )
TABLESPACE tsx;
Local and global indexes associated with the range-partitioned table remain usable.
When you add a partition to a hash-partitioned table, the database populates the new partition with rows rehashed from an existing partition (selected by the database) as determined by the hash function. Consequently, if the table contains data, then it may take some time to add a hash partition.
The following statements show two ways of adding a hash partition to table scubagear. Choosing the first statement adds a new hash partition whose partition name is system generated, and which is placed in the default tablespace. The second statement also adds a new hash partition, but that partition is explicitly named p_named and is created in tablespace gear5.
ALTER TABLE scubagear ADD PARTITION;
ALTER TABLE scubagear
ADD PARTITION p_named TABLESPACE gear5;
Indexes may be marked UNUSABLE as explained in the following table:
| Table Type | Index Behavior |
|---|---|
| Regular (Heap) | Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
|
| Index-organized |
|
The following statement illustrates how to add a new partition to a list-partitioned table. In this example, physical attributes and NOLOGGING are specified for the partition being added.
ALTER TABLE q1_sales_by_region
ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
NOLOGGING;
Any value in the set of literal values that describe the partition being added must not exist in any of the other partitions of the table.
You cannot add a partition to a list-partitioned table that has a default partition, but you can split the default partition. By doing so, you effectively create a new partition defined by the values that you specify, and a second partition that remains the default partition.
Local and global indexes associated with the list-partitioned table remain usable.
You cannot explicitly add a partition to an interval-partitioned table unless you first lock the partition, which triggers the creation of the partition. The database automatically creates a partition for an interval when data for that interval is inserted. In general, you only must explicitly create interval partitions for a partition exchange load scenario.
To change the interval for future partitions, use the SET INTERVAL clause of the ALTER TABLE statement. This clause changes the interval for partitions beyond the current highest boundary of all materialized interval partitions.
You also use the SET INTERVAL clause to migrate an existing range partitioned or range-* composite partitioned table into an interval or interval-* partitioned table. To disable the creation of future interval partitions, and effectively revert to a range-partitioned table, use an empty value in the SET INTERVAL clause. Created interval partitions are transformed into range partitions with their current high values.
To increase the interval for date ranges, you must ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:
ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'); ORA-14767: Cannot specify this interval with existing high bounds
You must create another daily partition with a high bound of February 1, 2007 to successfully change to a monthly interval:
LOCK TABLE transactions PARTITION FOR(TO_DATE('31-JAN-2007','dd-MON-yyyy') IN SHARE MODE;
ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');
The lower partitions of an interval-partitioned table are range partitions. You can split range partitions to add more partitions in the range portion of the interval-partitioned table.
To disable interval partitioning on the transactions table, use:
ALTER TABLE transactions SET INTERVAL ();
Partitions can be added at both the partition level and at the hash subpartition level.
Adding a new partition to a [range | list | interval]-hash partitioned table is as described previously. For an interval-hash partitioned table, interval partitions are automatically created. You can specify a SUBPARTITIONS clause that lets you add a specified number of subpartitions, or a SUBPARTITION clause for naming specific subpartitions. If no SUBPARTITIONS or SUBPARTITION clause is specified, then the partition inherits table level defaults for subpartitions. For an interval-hash partitioned table, you can only add subpartitions to range or interval partitions that have been materialized.
This example adds a range partition q1_2000 to the range-hash partitioned table sales, which is populated with data for the first quarter of the year 2000. There are eight subpartitions stored in tablespace tbs5. The subpartitions cannot be set explicitly to use table compression. Subpartitions inherit the compression attribute from the partition level and are stored in a compressed form in this example:
ALTER TABLE sales ADD PARTITION q1_2000
VALUES LESS THAN (2000, 04, 01) COMPRESS
SUBPARTITIONS 8 STORE IN tbs5;
You use the MODIFY PARTITION ... ADD SUBPARTITION clause of the ALTER TABLE statement to add a hash subpartition to a [range | list | interval]-hash partitioned table. The newly added subpartition is populated with rows rehashed from other subpartitions of the same partition as determined by the hash function. For an interval-hash partitioned table, you can only add subpartitions to range or interval partitions that have been materialized.
In the following example, a new hash subpartition us_loc5, stored in tablespace us1, is added to range partition locations_us in table diving.
ALTER TABLE diving MODIFY PARTITION locations_us
ADD SUBPARTITION us_locs5 TABLESPACE us1;
Index subpartitions corresponding to the added and rehashed subpartitions must be rebuilt unless you specify UPDATE INDEXES.
Partitions can be added at both the partition level and at the list subpartition level.
Adding a new partition to a [range | list | interval]-list partitioned table is as described previously. The database automatically creates interval partitions as data for a specific interval is inserted. You can specify SUBPARTITION clauses for naming and providing value lists for the subpartitions. If no SUBPARTITION clauses are specified, then the partition inherits the subpartition template. If there is no subpartition template, then a single default subpartition is created.
The statement in Example 4-28 adds a new partition to the quarterly_regional_sales table that is partitioned by the range-list method. Some new physical attributes are specified for this new partition while table-level defaults are inherited for those that are not specified.
Example 4-28 Adding partitions to a range-list partitioned table
ALTER TABLE quarterly_regional_sales
ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING
(
SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
);
You use the MODIFY PARTITION ... ADD SUBPARTITION clause of the ALTER TABLE statement to add a list subpartition to a [range | list | interval]-list partitioned table. For an interval-list partitioned table, you can only add subpartitions to range or interval partitions that have been materialized.
The following statement adds a new subpartition to the existing set of subpartitions in the range-list partitioned table quarterly_regional_sales. The new subpartition is created in tablespace ts2.
ALTER TABLE quarterly_regional_sales
MODIFY PARTITION q1_1999
ADD SUBPARTITION q1_1999_south
VALUES ('AR','MS','AL') tablespace ts2;
Partitions can be added at both the partition level and at the range subpartition level.
Adding a new partition to a [range | list | interval]-range partitioned table is as described previously. The database automatically creates interval partitions for an interval-range partitioned table when data is inserted in a specific interval. You can specify a SUBPARTITION clause for naming and providing ranges for specific subpartitions. If no SUBPARTITION clause is specified, then the partition inherits the subpartition template specified at the table level. If there is no subpartition template, then a single subpartition with a maximum value of MAXVALUE is created.
Example 4-29 adds a range partition p_2007_jan to the range-range partitioned table shipments, which is populated with data for the shipments ordered in January 2007. There are three subpartitions. Subpartitions inherit the compression attribute from the partition level and are stored in a compressed form in this example:
Example 4-29 Adding partitions to a range-range partitioned table
ALTER TABLE shipments
ADD PARTITION p_2007_jan
VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy')) COMPRESS
( SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE('15-FEB-2007','dd-MON-yyyy'))
, SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE('01-MAR-2007','dd-MON-yyyy'))
, SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy'))
) ;
You use the MODIFY PARTITION ... ADD SUBPARTITION clause of the ALTER TABLE statement to add a range subpartition to a [range | list | interval]-range partitioned table. For an interval-range partitioned table, you can only add partitions to range or interval partitions that have been materialized.
The following example adds a range subpartition to the shipments table that contains all values with an order_date in January 2007 and a delivery_date on or after April 1, 2007.
ALTER TABLE shipments
MODIFY PARTITION p_2007_jan
ADD SUBPARTITION p07_jan_vl VALUES LESS THAN (MAXVALUE) ;
A partition or subpartition can be added to a parent table in a reference partition definition just as partitions and subpartitions can be added to a range, hash, list, or composite partitioned table. The add operation automatically cascades to any descendant reference partitioned tables. The DEPENDENT TABLES clause can set specific properties for dependent tables when you add partitions or subpartitions to a master table.
You cannot explicitly add a partition to a local index. Instead, a new partition is added to a local index only when you add a partition to the underlying table. Specifically, when there is a local index defined on a table and you issue the ALTER TABLE statement to add a partition, a matching partition is also added to the local index. The database assigns names and default physical storage attributes to the new index partitions, but you can rename or alter them after the ADD PARTITION operation is complete.
You can effectively specify a new tablespace for an index partition in an ADD PARTITION operation by first modifying the default attributes for the index. For example, assume that a local index, q1_sales_by_region_locix, was created for list partitioned table q1_sales_by_region. If before adding the new partition q1_nonmainland, as shown in "Adding a Partition to a List-Partitioned Table", you had issued the following statement, then the corresponding index partition would be created in tablespace tbs_4.
ALTER INDEX q1_sales_by_region_locix MODIFY DEFAULT ATTRIBUTES TABLESPACE tbs_4;
Otherwise, it would be necessary for you to use the following statement to move the index partition to tbs_4 after adding it:
ALTER INDEX q1_sales_by_region_locix REBUILD PARTITION q1_nonmainland TABLESPACE tbs_4;
You can add a partition to a hash-partitioned global index using the ADD PARTITION syntax of ALTER INDEX. The database adds hash partitions and populates them with index entries rehashed from an existing hash partition of the index, as determined by the hash function. The following statement adds a partition to the index hgidx shown in "Creating a Hash-Partitioned Global Index":
ALTER INDEX hgidx ADD PARTITION p5;
You cannot add a partition to a range-partitioned global index, because the highest partition always has a partition bound of MAXVALUE. To add a new highest partition, use the ALTER INDEX ... SPLIT PARTITION statement.
Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table or index, or the number of subpartitions in a *-hash partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by the database, and is dropped after its contents have been redistributed. If you coalesce a hash partition or subpartition in the parent table of a reference-partitioned table definition, then the reference-partitioned table automatically inherits the new partitioning definition.
Index partitions may be marked UNUSABLE as explained in the following table:
| Table Type | Index Behavior |
|---|---|
| Regular (Heap) | Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
|
| Index-organized |
|
The ALTER TABLE ... COALESCE PARTITION statement is used to coalesce a partition in a hash-partitioned table. The following statement reduces by one the number of partitions in a table by coalescing a partition.
ALTER TABLE ouu1
COALESCE PARTITION;
The following statement distributes the contents of a subpartition of partition us_locations into one or more remaining subpartitions (determined by the hash function) of the same partition. Note that for an interval-partitioned table, you can only coalesce hash subpartitions of materialized range or interval partitions. Basically, this operation is the inverse of the MODIFY PARTITION ... ADD SUBPARTITION clause discussed in "Adding a Subpartition to a *-Hash Partitioned Table".
ALTER TABLE diving MODIFY PARTITION us_locations
COALESCE SUBPARTITION;
You can instruct the database to reduce by one the number of index partitions in a hash-partitioned global index using the COALESCE PARTITION clause of ALTER INDEX. The database selects the partition to coalesce based on the requirements of the hash partition. The following statement reduces by one the number of partitions in the hgidx index, created in "Creating a Hash-Partitioned Global Index":
ALTER INDEX hgidx COALESCE PARTITION;
You can drop partitions from range, interval, list, or composite *-[range | list] partitioned tables. For interval partitioned tables, you can only drop range or interval partitions that have been materialized. For hash-partitioned tables, or hash subpartitions of composite *-hash partitioned tables, you must perform a coalesce operation instead.
You cannot drop a partition from a reference-partitioned table. Instead, a drop operation on a parent table cascades to all descendant tables.
Use one of the following statements to drop a table partition or subpartition:
ALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a composite *-[range | list] partitioned table
To preserve the data in the partition, then use the MERGE PARTITION statement instead of the DROP PARTITION statement.
If local indexes are defined for the table, then this statement also drops the matching partition or subpartitions from the local index. All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE unless either of the following is true:
You specify UPDATE INDEXES (Cannot be specified for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)
The partition being dropped or its subpartitions are empty
|
Note:
|
The following sections contain some scenarios for dropping table partitions.
If the partition contains data and one or more global indexes are defined on the table, then use one of the following methods to drop the table partition.
Method 1
Leave the global indexes in place during the ALTER TABLE ... DROP PARTITION statement. Afterward, you must rebuild any global indexes (whether partitioned or not) because the index (or index partitions) has been marked UNUSABLE. The following statements provide an example of dropping partition dec98 from the sales table, then rebuilding its global nonpartitioned index.
ALTER TABLE sales DROP PARTITION dec98; ALTER INDEX sales_area_ix REBUILD;
If index sales_area_ix were a range-partitioned global index, then all partitions of the index would require rebuilding. Further, it is not possible to rebuild all partitions of an index in one statement. You must issue a separate REBUILD statement for each partition in the index. The following statements rebuild the index partitions jan99_ix, feb99_ix, mar99_ix, ..., dec99_ix.
ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix; ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix; ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix; ... ALTER INDEX sales_area_ix REBUILD PARTITION dec99_ix;
This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table.
Method 2
Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... DROP PARTITION statement. The DELETE statement updates the global indexes.
For example, to drop the first partition, issue the following statements:
DELETE FROM sales partition (dec98); ALTER TABLE sales DROP PARTITION dec98;
This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.
Method 3
Specify UPDATE INDEXES in the ALTER TABLE statement. Doing so causes the global index to be updated at the time the partition is dropped.
ALTER TABLE sales DROP PARTITION dec98
UPDATE INDEXES;
If a partition contains data and the table has referential integrity constraints, choose either of the following methods to drop the table partition. This table has a local index only, so it is not necessary to rebuild any indexes.
Method 1
If there is no data referencing the data in the partition to drop, then you can disable the integrity constraints on the referencing tables, issue the ALTER TABLE ... DROP PARTITION statement, then re-enable the integrity constraints.
This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table. If there is still data referencing the data in the partition to be dropped, then ensure the removal of all the referencing data so that you can re-enable the referential integrity constraints.
Method 2
If there is data in the referencing tables, then you can issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... DROP PARTITION statement. The DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo logs. The delete can succeed if you created the constraints with the ON DELETE CASCADE option, deleting all rows from referencing tables as well.
DELETE FROM sales partition (dec94); ALTER TABLE sales DROP PARTITION dec94;
This method is most appropriate for small tables or for large tables when the partition being dropped contains a small percentage of the total data in the table.
You can drop interval partitions in an interval-partitioned table. This operation drops the data for the interval only and leaves the interval definition in tact. If data is inserted in the interval just dropped, then the database again creates an interval partition.
You can also drop range partitions in an interval-partitioned table. The rules for dropping a range partition in an interval-partitioned table follow the rules for dropping a range partition in a range-partitioned table. If you drop a range partition in the middle of a set of range partitions, then the lower boundary for the next range partition shifts to the lower boundary of the range partition you just dropped. You cannot drop the highest range partition in the range-partitioned section of an interval-partitioned table.
The following example drops the September 2007 interval partition from the sales table. There are only local indexes so no indexes are invalidated.
ALTER TABLE sales DROP PARTITION FOR(TO_DATE('01-SEP-2007','dd-MON-yyyy'));
You cannot explicitly drop a partition of a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.
If a global index partition is empty, then you can explicitly drop it by issuing the ALTER INDEX ... DROP PARTITION statement. But, if a global index partition contains data, then dropping the partition causes the next highest partition to be marked UNUSABLE. For example, you would like to drop the index partition P1, and P2 is the next highest partition. You must issue the following statements:
ALTER INDEX npr DROP PARTITION P1; ALTER INDEX npr REBUILD PARTITION P2;
|
Note: You cannot drop the highest partition in a global index. |
You can convert a partition (or subpartition) into a nonpartitioned table, and a nonpartitioned table into a partition (or subpartition) of a partitioned table by exchanging their data segments. You can also convert a hash-partitioned table into a partition of a composite *-hash partitioned table, or convert the partition of a composite *-hash partitioned table into a hash-partitioned table. Similarly, you can convert a [range | list]-partitioned table into a partition of a composite *-[range | list] partitioned table, or convert a partition of the composite *-[range | list] partitioned table into a [range | list]-partitioned table.
Exchanging table partitions is most useful when you have an application using nonpartitioned tables to convert to partitions of a partitioned table. For example, in data warehousing environments, exchanging partitions facilitates high-speed data loading of new, incremental data into an existing partitioned table. Generically, OLTP and data warehousing environments benefit from exchanging old data partitions out of a partitioned table. The data is purged from the partitioned table without actually being deleted and can be archived separately afterward.
When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged (INCLUDING INDEXES clause), and if rows are to be validated for proper mapping (WITH VALIDATION clause).
|
Note: When you specifyWITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation is performed as if WITH VALIDATION were specified to maintain the integrity of the constraints.
To avoid the overhead of this validation activity, issue the following statement for each constraint before doing the exchange partition operation: ALTER TABLE table_name DISABLE CONSTRAINT constraint_name KEEP INDEX Then, enable the constraints after the exchange. If you specify |
Unless you specify UPDATE INDEXES, the database marks UNUSABLE the global indexes or all global index partitions on the table whose partition is being exchanged. Global indexes or global index partitions on the table being exchanged remain invalidated. (You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)
For more information, refer to "Viewing Information About Partitioned Tables and Indexes".
To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned.
ALTER TABLE stocks
EXCHANGE PARTITION p3 WITH TABLE stock_table_3;
You can exchange interval partitions in an interval-partitioned table. However, you must ensure that the interval partition has been created before you can exchange the partition. You can let the database create the partition by locking the interval partition.
The following example shows a partition exchange for the interval_sales table, interval-partitioned using monthly partitions as of January 1, 2004. This example shows how to add data for June 2007 to the table using partition exchange load. Assume there are only local indexes on the interval_sales table, and equivalent indexes have been created on the interval_sales_june_2007 table.
LOCK TABLE interval_sales
PARTITION FOR (TO_DATE('01-JUN-2007','dd-MON-yyyy'))
IN SHARE MODE;
ALTER TABLE interval_sales
EXCHANGE PARTITION FOR (TO_DATE('01-JUN-2007','dd-MON-yyyy'))
WITH TABLE interval_sales_jun_2007
INCLUDING INDEXES;
Note the use of the FOR syntax to identify a partition that was system-generated. The partition name can be used by querying the *_TAB_PARTITIONS data dictionary view to find out the system-generated partition name.
You can exchange partitions in a reference-partitioned table, but you must ensure that the data that you reference is available in the respective partition in the parent table.
Example 4-30 shows a partition exchange load scenario for the range-partitioned orders table, and the reference partitioned order_items table. Note that the data in the order_items_dec_2006 table only contains order item data for orders with an order_date in December 2006.
Example 4-30 Exchanging a partition for a range-partitioned table
ALTER TABLE orders EXCHANGE PARTITION p_2006_dec WITH TABLE orders_dec_2006 UPDATE GLOBAL INDEXES; ALTER TABLE order_items_dec_2006 ADD CONSTRAINT order_items_dec_2006_fk FOREIGN KEY (order_id) REFERENCES orders(order_id) ; ALTER TABLE order_items EXCHANGE PARTITION p_2006_dec WITH TABLE order_items_dec_2006;
Note that you must use the UPDATE GLOBAL INDEXES or UPDATE INDEXES on the exchange partition of the parent table in order for the primary key index to remain usable. Note also that you must create or enable the foreign key constraint on the order_items_dec_2006 table in order for the partition exchange on the reference-partitioned table to succeed.
You can exchange partitions in the presence of virtual columns. In order for a partition exchange on a partitioned table with virtual columns to succeed, you must create a table that matches the definition of all non-virtual columns in a single partition of the partitioned table. You do not need to include the virtual column definitions, unless constraints or indexes have been defined on the virtual column.
In this case, you must include the virtual column definition to match the partitioned table's constraint and index definitions. This scenario also applies to virtual column-based partitioned tables.
In this example, you are exchanging a whole hash-partitioned table, with all of its partitions, with the partition of a *-hash partitioned table and all of its hash subpartitions. The following example illustrates this concept for a range-hash partitioned table.
First, create a hash-partitioned table:
CREATE TABLE t1 (i NUMBER, j NUMBER)
PARTITION BY HASH(i)
(PARTITION p1, PARTITION p2);
Populate the table, then create a range-hash partitioned table as follows:
CREATE TABLE t2 (i NUMBER, j NUMBER)
PARTITION BY RANGE(j)
SUBPARTITION BY HASH(i)
(PARTITION p1 VALUES LESS THAN (10)
SUBPARTITION t2_pls1
SUBPARTITION t2_pls2,
PARTITION p2 VALUES LESS THAN (20)
SUBPARTITION t2_p2s1
SUBPARTITION t2_p2s2));
It is important that the partitioning key in table t1 equals the subpartitioning key in table t2.
To migrate the data in t1 to t2, and validate the rows, use the following statement:
ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1
WITH VALIDATION;
Use the ALTER TABLE ... EXCHANGE SUBPARTITION statement to convert a hash subpartition of a *-hash partitioned table into a nonpartitioned table, or the reverse. The following example converts the subpartition q3_1999_s1 of table sales into the nonpartitioned table q3_1999. Local index partitions are exchanged with corresponding indexes on q3_1999.
ALTER TABLE sales EXCHANGE SUBPARTITION q3_1999_s1
WITH TABLE q3_1999 INCLUDING INDEXES;
The semantics of the ALTER TABLE ... EXCHANGE PARTITION statement are the same as described previously in "Exchanging a Hash-Partitioned Table with a *-Hash Partition". The following example shows an exchange partition scenario for a list-list partitioned table.
CREATE TABLE customers_apac
( id NUMBER
, name VARCHAR2(50)
, email VARCHAR2(100)
, region VARCHAR2(4)
, credit_rating VARCHAR2(1)
)
PARTITION BY LIST (credit_rating)
( PARTITION poor VALUES ('P')
, PARTITION mediocre VALUES ('C')
, PARTITION good VALUES ('G')
, PARTITION excellent VALUES ('E')
);
Populate the table with APAC customers. Then create a list-list partitioned table:
CREATE TABLE customers
( id NUMBER
, name VARCHAR2(50)
, email VARCHAR2(100)
, region VARCHAR2(4)
, credit_rating VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY LIST (credit_rating)
SUBPARTITION TEMPLATE
( SUBPARTITION poor VALUES ('P')
, SUBPARTITION mediocre VALUES ('C')
, SUBPARTITION good VALUES ('G')
, SUBPARTITION excellent VALUES ('E')
)
(PARTITION americas VALUES ('AMER')
, PARTITION emea VALUES ('EMEA')
, PARTITION apac VALUES ('APAC')
);
It is important that the partitioning key in the customers_apac table matches the subpartitioning key in the customers table.
Next, exchange the apac partition.
ALTER TABLE customers EXCHANGE PARTITION apac WITH TABLE customers_apac WITH VALIDATION;
The semantics of the ALTER TABLE ... EXCHANGE SUBPARTITION are the same as described previously in "Exchanging a Subpartition of a *-Hash Partitioned Table".
The semantics of the ALTER TABLE ... EXCHANGE PARTITION statement are the same as described previously in "Exchanging a Hash-Partitioned Table with a *-Hash Partition". The example below shows the orders table, which is interval partitioned by order_date, and subpartitioned by range on order_total. The example shows how to exchange a single monthly interval with a range-partitioned table.
CREATE TABLE orders_mar_2007 ( id NUMBER , cust_id NUMBER , order_date DATE , order_total NUMBER ) PARTITION BY RANGE (order_total) ( PARTITION p_small VALUES LESS THAN (1000) , PARTITION p_medium VALUES LESS THAN (10000) , PARTITION p_large VALUES LESS THAN (100000) , PARTITION p_extraordinary VALUES LESS THAN (MAXVALUE) );
Populate the table with orders for March 2007. Then create an interval-range partitioned table:
CREATE TABLE orders
( id NUMBER
, cust_id NUMBER
, order_date DATE
, order_total NUMBER
)
PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE (order_total)
SUBPARTITION TEMPLATE
( SUBPARTITION p_small VALUES LESS THAN (1000)
, SUBPARTITION p_medium VALUES LESS THAN (10000)
, SUBPARTITION p_large VALUES LESS THAN (100000)
, SUBPARTITION p_extraordinary VALUES LESS THAN (MAXVALUE)
)
(PARTITION p_before_2007 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-
MON-yyyy')));
It is important that the partitioning key in the orders_mar_2007 table matches the subpartitioning key in the orders table.
Next, exchange the partition. Note that because an interval partition is to be exchanged, the partition is first locked to ensure that the partition is created.
LOCK TABLE orders PARTITION FOR (TO_DATE('01-MAR-2007','dd-MON-yyyy'))
IN SHARE MODE;
ALTER TABLE orders
EXCHANGE PARTITION
FOR (TO_DATE('01-MAR-2007','dd-MON-yyyy'))
WITH TABLE orders_mar_2007
WITH VALIDATION;
The semantics of the ALTER TABLE ... EXCHANGE SUBPARTITION are the same as described previously in "Exchanging a Subpartition of a *-Hash Partitioned Table".
Use the ALTER TABLE ... MERGE PARTITION statement to merge the contents of two partitions into one partition. The two original partitions are dropped, as are any corresponding local indexes. You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite *-hash partitioned table.
You cannot merge partitions for a reference-partitioned table. Instead, a merge operation on a parent table cascades to all descendant tables. However, you can use the DEPENDENT TABLES clause to set specific properties for dependent tables when you issue the merge operation on the master table to merge partitions or subpartitions.
If the involved partitions or subpartitions contain data, then indexes may be marked UNUSABLE as explained in the following table:
| Table Type | Index Behavior |
|---|---|
| Regular (Heap) | Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
|
| Index-organized |
|
You are allowed to merge the contents of two adjacent range partitions into one partition. Nonadjacent range partitions cannot be merged. The resulting partition inherits the higher upper bound of the two merged partitions.
One reason for merging range partitions is to keep historical data online in larger partitions. For example, you can have daily partitions, with the oldest partition rolled up into weekly partitions, which can then be rolled up into monthly partitions, and so on.
Example 4-31 shows an example of merging range partitions.
Example 4-31 Merging range partitions
-- First, create a partitioned table with four partitions, each on its own
-- tablespace partitioned by range on the data column
--
CREATE TABLE four_seasons
(
one DATE,
two VARCHAR2(60),
three NUMBER
)
PARTITION BY RANGE ( one )
(
PARTITION quarter_one
VALUES LESS THAN ( TO_DATE('01-apr-1998','dd-mon- yyyy'))
TABLESPACE quarter_one,
PARTITION quarter_two
VALUES LESS THAN ( TO_DATE('01-jul-1998','dd-mon-yyyy'))
TABLESPACE quarter_two,
PARTITION quarter_three
VALUES LESS THAN ( TO_DATE('01-oct-1998','dd-mon-yyyy'))
TABLESPACE quarter_three,
PARTITION quarter_four
VALUES LESS THAN ( TO_DATE('01-jan-1999','dd-mon-yyyy'))
TABLESPACE quarter_four
);
--
-- Create local PREFIXED index on Four_Seasons
-- Prefixed because the leftmost columns of the index match the
-- Partitioning key
--
CREATE INDEX i_four_seasons_l ON four_seasons ( one,two )
LOCAL (
PARTITION i_quarter_one TABLESPACE i_quarter_one,
PARTITION i_quarter_two TABLESPACE i_quarter_two,
PARTITION i_quarter_three TABLESPACE i_quarter_three,
PARTITION i_quarter_four TABLESPACE i_quarter_four
);
-- Next, merge the first two partitions
ALTER TABLE four_seasons
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two
UPDATE INDEXES;
If you omit the UPDATE INDEXES clause from the preceding statement, then you must rebuild the local index for the affected partition.
-- Rebuild index for quarter_two, which has been marked unusable -- because it has not had all of the data from Q1 added to it. -- Rebuilding the index corrects this. -- ALTER TABLE four_seasons MODIFY PARTITION quarter_two REBUILD UNUSABLE LOCAL INDEXES;
The contents of two adjacent interval partitions can be merged into one partition. Nonadjacent interval partitions cannot be merged. The first interval partition can also be merged with the highest range partition. The resulting partition inherits the higher upper bound of the two merged partitions.
Merging interval partitions always results in the transition point being moved to the higher upper bound of the two merged partitions. This result is that the range section of the interval-partitioned table is extended to the upper bound of the two merged partitions. Any materialized interval partitions with boundaries lower than the newly merged partition are automatically converted into range partitions, with their upper boundaries defined by the upper boundaries of their intervals.
For example, consider the following interval-partitioned table transactions:
CREATE TABLE transactions
( id NUMBER
, transaction_date DATE
, value NUMBER
)
PARTITION BY RANGE (transaction_date)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
( PARTITION p_before_2007 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')));
Insert data into the interval section of the table. This creates the interval partitions for these days. Note that January 15, 2007 and January 16, 2007 are stored in adjacent interval partitions.
INSERT INTO transactions VALUES (1,TO_DATE('15-JAN-2007','dd-MON-yyyy'),100);
INSERT INTO transactions VALUES (2,TO_DATE('16-JAN-2007','dd-MON-yyyy'),600);
INSERT INTO transactions VALUES (3,TO_DATE('30-JAN-2007','dd-MON-yyyy'),200);
Next, merge the two adjacent interval partitions. The new partition again has a system-generated name.
ALTER TABLE transactions
MERGE PARTITIONS FOR(TO_DATE('15-JAN-2007','dd-MON-yyyy'))
, FOR(TO_DATE('16-JAN-2007','dd-MON-yyyy'));
The transition point for the transactions table has now moved to January 17, 2007. The range section of the interval-partitioned table contains two range partitions: values less than January 1, 2007 and values less than January 17, 2007. Values greater than January 17, 2007 fall in the interval portion of the interval-partitioned table.
When you merge list partitions, the partitions being merged can be any two partitions. They do not need to be adjacent, as for range partitions, because list partitioning does not assume any order for partitions. The resulting partition consists of all of the data from the original two partitions. If you merge a default list partition with any other partition, then the resulting partition is the default partition.
The following statement merges two partitions of a table partitioned using the list method into a partition that inherits all of its attributes from the table-level default attributes. MAXEXTENTS is specified in the statement.
ALTER TABLE q1_sales_by_region
MERGE PARTITIONS q1_northcentral, q1_southcentral
INTO PARTITION q1_central
STORAGE(MAXEXTENTS 20);
The value lists for the two original partitions were specified as:
PARTITION q1_northcentral VALUES ('SD','WI')
PARTITION q1_southcentral VALUES ('OK','TX')
The resulting sales_west partition value list comprises the set that represents the union of these two partition value lists, or specifically:
('SD','WI','OK','TX')
When you merge *-hash partitions, the subpartitions are rehashed into the number of subpartitions specified by SUBPARTITIONS n or the SUBPARTITION clause. If neither is included, table-level defaults are used.
Note that the inheritance of properties is different when a *-hash partition is split (discussed in "Splitting a *-Hash Partition"), as opposed to when two *-hash partitions are merged. When a partition is split, the new partitions can inherit properties from the original partition because there is only one parent. However, when partitions are merged, properties must be inherited from the table level.
For interval-hash partitioned tables, you can only merge two adjacent interval partitions, or the highest range partition with the first interval partition. As described in "Merging Interval Partitions", the transition point moves when you merge intervals in an interval-hash partitioned table.
The following example merges two range-hash partitions:
ALTER TABLE all_seasons MERGE PARTITIONS quarter_1, quarter_2 INTO PARTITION quarter_2 SUBPARTITIONS 8;
Partitions can be merged at the partition level and subpartitions can be merged at the list subpartition level.
Merging partitions in a *-list partitioned table is as described previously in "Merging Range Partitions". However, when you merge two *-list partitions, the resulting new partition inherits the subpartition descriptions from the subpartition template, if a template exists. If no subpartition template exists, then a single default subpartition is created for the new partition.
For interval-list partitioned tables, you can only merge two adjacent interval partitions, or the highest range partition with the first interval partition. As described in "Merging Interval Partitions", the transition point moves when you merge intervals in an interval-list partitioned table.
The following statement merges two partitions in the range-list partitioned stripe_regional_sales table. A subpartition template exists for the table.
ALTER TABLE stripe_regional_sales
MERGE PARTITIONS q1_1999, q2_1999 INTO PARTITION q1_q2_1999
STORAGE(MAXEXTENTS 20);
Some new physical attributes are specified for this new partition while table-level defaults are inherited for those that are not specified. The new resulting partition q1_q2_1999 inherits the high-value bound of the partition q2_1999 and the subpartition value-list descriptions from the subpartition template description of the table.
The data in the resulting partitions consists of data from both the partitions. However, there may be cases where the database returns an error. This can occur because data may map out of the new partition when both of the following conditions exist:
Some literal values of the merged subpartitions were not included in the subpartition template.
The subpartition template does not contain a default partition definition.
This error condition can be eliminated by always specifying a default partition in the default subpartition template.
You can merge the contents of any two arbitrary list subpartitions belonging to the same partition. The resulting subpartition value-list descriptor includes all of the literal values in the value lists for the partitions being merged.
The following statement merges two subpartitions of a table partitioned using range-list method into a new subpartition located in tablespace ts4:
ALTER TABLE quarterly_regional_sales
MERGE SUBPARTITIONS q1_1999_northwest, q1_1999_southwest
INTO SUBPARTITION q1_1999_west
TABLESPACE ts4;
The value lists for the original two partitions were:
Subpartition q1_1999_northwest was described as ('WA','OR')
Subpartition q1_1999_southwest was described as ('AZ','NM','UT')
The resulting subpartition value list comprises the set that represents the union of these two subpartition value lists:
Subpartition q1_1999_west has a value list described as ('WA','OR','AZ','NM','UT')
The tablespace in which the resulting subpartition is located and the subpartition attributes are determined by the partition-level default attributes, except for those specified explicitly. If any of the existing subpartition names are being reused, then the new subpartition inherits the subpartition attributes of the subpartition whose name is being reused.
Partitions can be merged at the partition level and subpartitions can be merged at the range subpartition level.
Merging partitions in a *-range partitioned table is as described previously in "Merging Range Partitions". However, when you merge two *-range partitions, the resulting new partition inherits the subpartition descriptions from the subpartition template, if one exists. If no subpartition template exists, then a single subpartition with an upper boundary MAXVALUE is created for the new partition.
For interval-range partitioned tables, you can only merge two adjacent interval partitions, or the highest range partition with the first interval partition. As described in "Merging Interval Partitions", the transition point moves when you merge intervals in an interval-range partitioned table.
The following statement merges two partitions in the monthly interval-range partitioned orders table. A subpartition template exists for the table.
ALTER TABLE orders
MERGE PARTITIONS FOR(TO_DATE('01-MAR-2007','dd-MON-yyyy')),
FOR(TO_DATE('01-APR-2007','dd-MON-yyyy'))
INTO PARTITION p_pre_may_2007;
If the March 2007 and April 2007 partitions were still in the interval section of the interval-range partitioned table, then the merge operation would move the transition point to May 1, 2007.
The subpartitions for partition p_pre_may_2007 inherit their properties from the subpartition template. The data in the resulting partitions consists of data from both the partitions. However, there may be cases where the database returns an error. This can occur because data may map out of the new partition when both of the following conditions are met:
Some range values of the merged subpartitions were not included in the subpartition template.
The subpartition template does not have a subpartition definition with a MAXVALUE upper boundary.
The error condition can be eliminated by always specifying a subpartition with an upper boundary of MAXVALUE in the subpartition template.
You can modify the default attributes of a table, or for a partition of a composite partitioned table. When you modify default attributes, the new attributes affect only future partitions, or subpartitions, that are created. The default values can still be specifically overridden when creating a new partition or subpartition. You can modify the default attributes of a reference-partitioned table.
You can modify the default attributes that are inherited for range, hash, list, interval, or reference partitions using the MODIFY DEFAULT ATTRIBUTES clause of ALTER TABLE.
For hash-partitioned tables, only the TABLESPACE attribute can be modified.
To modify the default attributes inherited when creating subpartitions, use the ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES FOR PARTITION. The following statement modifies the TABLESPACE in which future subpartitions of partition p1 in range-hash partitioned table emp reside.
ALTER TABLE emp
MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE ts1;
Because all subpartitions of a range-hash partitioned table must share the same attributes, except TABLESPACE, it is the only attribute that can be changed.
You cannot modify default attributes of interval partitions that have not yet been created. To change the way in which future subpartitions in an interval-partitioned table are created, you must modify the subpartition template.
In a similar fashion to table partitions, you can alter the default attributes that are inherited by partitions of a range-partitioned global index, or local index partitions of partitioned tables. For this you use the ALTER INDEX ... MODIFY DEFAULT ATTRIBUTES statement. Use the ALTER INDEX ... MODIFY DEFAULT ATTRIBUTES FOR PARTITION statement if you are altering default attributes to be inherited by subpartitions of a composite partitioned table.
It is possible to modify attributes of an existing partition of a table or index.
You cannot change the TABLESPACE attribute. Use ALTER TABLE ... MOVE PARTITION/SUBPARTITION to move a partition or subpartition to a new tablespace.
Use the ALTER TABLE ... MODIFY PARTITION statement to modify existing attributes of a range partition or list partition. You can modify segment attributes (except TABLESPACE), or you can allocate and deallocate extents, mark local index partitions UNUSABLE, or rebuild local indexes that have been marked UNUSABLE.
If this is a range partition of a *-hash partitioned table, then note the following:
If you allocate or deallocate an extent, this action is performed for every subpartition of the specified partition.
Likewise, changing any other attributes results in corresponding changes to those attributes of all the subpartitions for that partition. The partition level default attributes are changed as well. To avoid changing attributes of existing subpartitions, use the FOR PARTITION clause of the MODIFY DEFAULT ATTRIBUTES statement.
The following are some examples of modifying the real attributes of a partition.
This example modifies the MAXEXTENTS storage attribute for the range partition sales_q1 of table sales:
ALTER TABLE sales MODIFY PARTITION sales_q1
STORAGE (MAXEXTENTS 10);
All of the local index subpartitions of partition ts1 in range-hash partitioned table scubagear are marked UNUSABLE in the following example:
ALTER TABLE scubagear MODIFY PARTITION ts1 UNUSABLE LOCAL INDEXES;
For an interval-partitioned table you can only modify real attributes of range partitions or interval partitions that have been created.
You also use the ALTER TABLE ... MODIFY PARTITION statement to modify attributes of a hash partition. However, because the physical attributes of individual hash partitions must all be the same (except for TABLESPACE), you are restricted to:
Allocating a new extent
Deallocating an unused extent
Rebuilding local index subpartitions that are marked UNUSABLE
The following example rebuilds any unusable local index partitions associated with hash partition P1 of table dept:
ALTER TABLE dept MODIFY PARTITION p1
REBUILD UNUSABLE LOCAL INDEXES;
With the MODIFY SUBPARTITION clause of ALTER TABLE you can perform the same actions as listed previously for partitions, but at the specific composite partitioned table subpartition level. For example:
ALTER TABLE emp MODIFY SUBPARTITION p3_s1
REBUILD UNUSABLE LOCAL INDEXES;
The MODIFY PARTITION clause of ALTER INDEX lets you modify the real attributes of an index partition or its subpartitions. The rules are very similar to those for table partitions, but unlike the MODIFY PARTITION clause for ALTER INDEX, there is no subclause to rebuild an unusable index partition, but there is a subclause to coalesce an index partition or its subpartitions. In this context, coalesce means to merge index blocks where possible to free them for reuse.
You can also allocate or deallocate storage for a subpartition of a local index, or mark it UNUSABLE, using the MODIFY PARTITION clause.
List partitioning enables you to optionally add literal values from the defining value list.
Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global indexes, or global or local index partitions, remain usable.
The following statement adds a new set of state codes ('OK', 'KS') to an existing partition list.
ALTER TABLE sales_by_region
MODIFY PARTITION region_south
ADD VALUES ('OK', 'KS');
The existence of a default partition can have a performance impact when adding values to other partitions. This is because to add values to a list partition, the database must check that the values being added do not exist in the default partition. If any of the values do exist in the default partition, then an error is displayed.
|
Note: The database runs a query to check for the existence of rows in the default partition that correspond to the literal values being added. Therefore, it is advisable to create a local prefixed index on the table. This speeds up the execution of the query and the overall operation. |
You cannot add values to a default list partition.
This operation is essentially the same as described for "Modifying List Partitions: Adding Values", however, you use a MODIFY SUBPARTITION clause instead of the MODIFY PARTITION clause. For example, to extend the range of literal values in the value list for subpartition q1_1999_southeast, use the following statement:
ALTER TABLE quarterly_regional_sales
MODIFY SUBPARTITION q1_1999_southeast
ADD VALUES ('KS');
Literal values being added must not have been included in any other subpartition value list within the owning partition. However, they can be duplicates of literal values in the subpartition value lists of other partitions within the table.
For an interval-list composite partitioned table, you can only add values to subpartitions of range partitions or interval partitions that have been created. To add values to subpartitions of interval partitions that have not yet been created, you must modify the subpartition template.
List partitioning enables you to optionally drop literal values from the defining value list.
Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that corresponds to the set of values being dropped. If any such rows are found then the database returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.
|
Note: You cannot drop all literal values from the value list describing the partition. You must use theALTER TABLE ... DROP PARTITION statement instead. |
The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.
The following statement drops a set of state codes ('OK' and 'KS') from an existing partition value list.
ALTER TABLE sales_by_region
MODIFY PARTITION region_south
DROP VALUES ('OK', 'KS');
|
Note: The database runs a query to check for the existence of rows in the partition that correspond to the literal values being dropped. Therefore, it is advisable to create a local prefixed index on the table. This speeds up the query and the overall operation. |
You cannot drop values from a default list partition.
This operation is essentially the same as described for "Modifying List Partitions: Dropping Values", however, you use a MODIFY SUBPARTITION clause instead of the MODIFY PARTITION clause. For example, to remove a set of literal values in the value list for subpartition q1_1999_southeast, use the following statement:
ALTER TABLE quarterly_regional_sales
MODIFY SUBPARTITION q1_1999_southeast
DROP VALUES ('KS');
For an interval-list composite partitioned table, you can only drop values from subpartitions of range partitions or interval partitions that have been created. To drop values from subpartitions of interval partitions that have not yet been created, you must modify the subpartition template.
You can modify a subpartition template of a composite partitioned table by replacing it with a new subpartition template. Any subsequent operations that use the subpartition template (such as ADD PARTITION or MERGE PARTITIONS) now use the new subpartition template. Existing subpartitions remain unchanged.
If you modify a subpartition template of an interval-* composite partitioned table, then interval partitions that have not yet been created use the new subpartition template.
Use the ALTER TABLE ... SET SUBPARTITION TEMPLATE statement to specify a new subpartition template. For example:
ALTER TABLE emp_sub_template
SET SUBPARTITION TEMPLATE
(SUBPARTITION e TABLESPACE ts1,
SUBPARTITION f TABLESPACE ts2,
SUBPARTITION g TABLESPACE ts3,
SUBPARTITION h TABLESPACE ts4
);
You can drop a subpartition template by specifying an empty list:
ALTER TABLE emp_sub_template SET SUBPARTITION TEMPLATE ( );
Use the MOVE PARTITION clause of the ALTER TABLE statement to:
Re-cluster data and reduce fragmentation
Move a partition to another tablespace
Modify create-time attributes
Store the data in compressed format using table compression
Typically, you can change the physical storage attributes of a partition in a single step using an ALTER TABLE/INDEX ... MODIFY PARTITION statement. However, there are some physical attributes, such as TABLESPACE, that you cannot modify using MODIFY PARTITION. In these cases, use the MOVE PARTITION clause. Modifying some other attributes, such as table compression, affects only future storage, but not existing data.
|
Note: ALTER TABLE...MOVE does not permit DML on the partition while the command is running. To move a partition and leave it available for DML, see "Redefining Partitions Online". |
If the partition being moved contains any data, then indexes may be marked UNUSABLE according to the following table:
| Table Type | Index Behavior |
|---|---|
| Regular (Heap) | Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
|
| Index-organized | Any local or global indexes defined for the partition being moved remain usable because they are primary-key based logical rowids. However, the guess information for these rowids becomes incorrect. |
Use the MOVE PARTITION clause to move a partition. For example, to move the most active partition to a tablespace that resides on its own set of disks (to balance I/O), not log the action, and compress the data, issue the following statement:
ALTER TABLE parts MOVE PARTITION depot2
TABLESPACE ts094 NOLOGGING COMPRESS;
This statement always drops the old partition segment and creates a new segment, even if you do not specify a new tablespace.
If you are moving a partition of a partitioned index-organized table, then you can specify the MAPPING TABLE clause as part of the MOVE PARTITION clause, and the mapping table partition are moved to the new location along with the table partition.
For an interval or interval-* partitioned table, you can only move range partitions or interval partitions that have been created. A partition move operation does not move the transition point in an interval or interval-* partitioned table.
You can move a partition in a reference-partitioned table independent of the partition in the master table.
The following statement shows how to move data in a subpartition of a table. In this example, a PARALLEL clause has also been specified.
ALTER TABLE scuba_gear MOVE SUBPARTITION bcd_types
TABLESPACE tbs23 PARALLEL (DEGREE 2);
You can move a subpartition in a reference-partitioned table independent of the subpartition in the master table.
The ALTER TABLE ... MOVE PARTITION statement for regular tables, marks all partitions of a global index UNUSABLE. You can rebuild the entire index by rebuilding each partition individually using the ALTER INDEX ... REBUILD PARTITION statement. You can perform these rebuilds concurrently.
You can also simply drop the index and re-create it.
Oracle Database provides a mechanism to move a partition or to make other changes to the partition's physical structure without significantly affecting the availability of the partition for DML. This mechanism is called online table redefinition.
For information about redefining a single partition of a table, see Oracle Database Administrator's Guide.
You can use online redefinition to copy nonpartitioned Collection Tables to partitioned Collection Tables and Oracle Database inserts rows into the appropriate partitions in the Collection Table. Example 4-32 illustrates how this is done for nested tables inside an Objects column; a similar example works for Ordered Collection Type Tables inside an XMLType table or column. Note that during the copy_table_dependents operation, you specify 0 or false for copying the indexes and constraints, because you want to keep the indexes and constraints of the newly defined collection table. However, it is important to note that the Collection Tables and its partitions have the same names as that of the interim table (print_media2 in Example 4-32). You must take explicit steps to preserve the Collection Table names.
Example 4-32 Redefining partitions with collection tables
CONNECT / AS SYSDBA
DROP USER eqnt CASCADE;
CREATE USER eqnt IDENTIFIED BY eqnt;
GRANT CONNECT, RESOURCE TO eqnt;
-- Grant privleges required for online redefinition.
GRANT EXECUTE ON DBMS_REDEFINITION TO eqnt;
GRANT ALTER ANY TABLE TO eqnt;
GRANT DROP ANY TABLE TO eqnt;
GRANT LOCK ANY TABLE TO eqnt;
GRANT CREATE ANY TABLE TO eqnt;
GRANT SELECT ANY TABLE TO eqnt;
-- Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO eqnt;
GRANT CREATE ANY INDEX TO eqnt;
CONNECT eqnt/eqnt
CREATE TYPE textdoc_typ AS OBJECT ( document_typ VARCHAR2(32));
/
CREATE TYPE textdoc_tab AS TABLE OF textdoc_typ;
/
-- (old) non partitioned nested table
CREATE TABLE print_media
( product_id NUMBER(6) primary key
, ad_textdocs_ntab textdoc_tab
)
NESTED TABLE ad_textdocs_ntab STORE AS equi_nestedtab
( (document_typ NOT NULL)
STORAGE (INITIAL 8M)
)
PARTITION BY RANGE (product_id)
(
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20)
);
-- Insert into base table
INSERT INTO print_media VALUES (1,
textdoc_tab(textdoc_typ('xx'), textdoc_typ('yy')));
INSERT INTO print_media VALUES (11,
textdoc_tab(textdoc_typ('aa'), textdoc_typ('bb')));
COMMIT;
-- Insert into nested table
INSERT INTO TABLE
(SELECT p.ad_textdocs_ntab FROM print_media p WHERE p.product_id = 11)
VALUES ('cc');
SELECT * FROM print_media;
PRODUCT_ID AD_TEXTDOCS_NTAB(DOCUMENT_TYP)
---------- ------------------------------
1 TEXTDOC_TAB(TEXTDOC_TYP('xx'), TEXTDOC_TYP('yy'))
11 TEXTDOC_TAB(TEXTDOC_TYP('aa'), TEXTDOC_TYP('bb'), TEXTDOC_TYP('cc'))
-- Creating partitioned Interim Table
CREATE TABLE print_media2
( product_id NUMBER(6)
, ad_textdocs_ntab textdoc_tab
)
NESTED TABLE ad_textdocs_ntab STORE AS equi_nestedtab2
( (document_typ NOT NULL)
STORAGE (INITIAL 8M)
)
PARTITION BY RANGE (product_id)
(
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20)
);
EXEC dbms_redefinition.start_redef_table('eqnt', 'print_media', 'print_media2');
DECLARE
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('eqnt', 'print_media', 'print_media2',
0, true, false, true, false,
error_count);
dbms_output.put_line('errors := ' || to_char(error_count));
END;
/
EXEC dbms_redefinition.finish_redef_table('eqnt', 'print_media', 'print_media2');
-- Drop the interim table
DROP TABLE print_media2;
-- print_media has partitioned nested table here
SELECT * FROM print_media PARTITION (p1);
PRODUCT_ID AD_TEXTDOCS_NTAB(DOCUMENT_TYP)
---------- ------------------------------
1 TEXTDOC_TAB(TEXTDOC_TYP('xx'), TEXTDOC_TYP('yy'))
SELECT * FROM print_media PARTITION (p2);
PRODUCT_ID AD_TEXTDOCS_NTAB(DOCUMENT_TYP)
---------- ------------------------------
11 TEXTDOC_TAB(TEXTDOC_TYP('aa'), TEXTDOC_TYP('bb'), TEXTDOC_TYP('cc'))
Some reasons for rebuilding index partitions include:
To recover space and improve performance
To repair a damaged index partition caused by media failure
To rebuild a local index partition after loading the underlying table partition with SQL*Loader or an import utility
To rebuild index partitions that have been marked UNUSABLE
To enable key compression for B-tree indexes
The following sections discuss options for rebuilding index partitions and subpartitions.
You can rebuild global index partitions in two ways:
Rebuild each partition by issuing the ALTER INDEX ... REBUILD PARTITION statement (you can run the rebuilds concurrently).
Drop the entire global index and re-create it. This method is more efficient because the table is scanned only one time.
For most maintenance operations on partitioned tables with indexes, you can optionally avoid the need to rebuild the index by specifying UPDATE INDEXES on your DDL statement.
Rebuild local indexes using either ALTER INDEX or ALTER TABLE as follows:
ALTER INDEX ... REBUILD PARTITION/SUBPARTITION
This statement rebuilds an index partition or subpartition unconditionally.
ALTER TABLE ... MODIFY PARTITION/SUBPARTITION ... REBUILD UNUSABLE LOCAL INDEXES
This statement finds all of the unusable indexes for the given table partition or subpartition and rebuilds them. It only rebuilds an index partition if it has been marked UNUSABLE.
The ALTER INDEX ... REBUILD PARTITION statement rebuilds one partition of an index. It cannot be used for composite-partitioned tables. Only real physical segments can be rebuilt with this command. When you re-create the index, you can also choose to move the partition to a new tablespace or change attributes.
For composite-partitioned tables, use ALTER INDEX ... REBUILD SUBPARTITION to rebuild a subpartition of an index. You can move the subpartition to another tablespace or specify a parallel clause. The following statement rebuilds a subpartition of a local index on a table and moves the index subpartition to another tablespace.
ALTER INDEX scuba REBUILD SUBPARTITION bcd_types TABLESPACE tbs23 PARALLEL (DEGREE 2);
The REBUILD UNUSABLE LOCAL INDEXES clause of ALTER TABLE ... MODIFY PARTITION does not allow you to specify any new attributes for the rebuilt index partition. The following example finds and rebuilds any unusable local index partitions for table scubagear, partition p1.
ALTER TABLE scubagear MODIFY PARTITION p1 REBUILD UNUSABLE LOCAL INDEXES;
There is a corresponding ALTER TABLE ... MODIFY SUBPARTITION clause for rebuilding unusable local index subpartitions.
It is possible to rename partitions and subpartitions of both tables and indexes. One reason for renaming a partition might be to assign a meaningful name, as opposed to a default system name that was assigned to the partition in another maintenance operation.
All partitioning methods support the FOR(value) method to identify a partition. You can use this method to rename a system-generated partition name into a more meaningful name. This is particularly useful in interval or interval-* partitioned tables.
You can independently rename partitions and subpartitions for reference-partitioned master and child tables. The rename operation on the master table is not cascaded to descendant tables.
Rename a range, hash, or list partition, using the ALTER TABLE ... RENAME PARTITION statement. For example:
ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks;
Likewise, you can assign new names to subpartitions of a table. In this case you would use the ALTER TABLE ... RENAME SUBPARTITION syntax.
Index partitions and subpartitions can be renamed in similar fashion, but the ALTER INDEX syntax is used.
Use the ALTER INDEX ... RENAME PARTITION statement to rename an index partition.
The ALTER INDEX statement does not support the use of FOR(value) to identify a partition. You must use the original partition name in the rename operation.
The SPLIT PARTITION clause of the ALTER TABLE or ALTER INDEX statement is used to redistribute the contents of a partition into two new partitions. Consider doing this when a partition becomes too large and causes backup, recovery, or maintenance operations to take a long time to complete or it is felt that there is simply too much data in the partition. You can also use the SPLIT PARTITION clause to redistribute the I/O load. This clause cannot be used for hash partitions or subpartitions.
If the partition you are splitting contains any data, then indexes may be marked UNUSABLE as explained in the following table:
| Table Type | Index Behavior |
|---|---|
| Regular (Heap) | Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
|
| Index-organized |
|
You cannot split partitions or subpartitions in a reference-partitioned table. When you split partitions or subpartitions in the parent table then the split is cascaded to all descendant tables. However, you can use the DEPENDENT TABLES clause to set specific properties for dependent tables when you issue the SPLIT statement on the master table to split partitions or subpartitions.
You split a range partition using the ALTER TABLE ... SPLIT PARTITION statement. You specify a value of the partitioning key column within the range of the partition at which to split the partition. The first of the resulting two new partitions includes all rows in the original partition whose partitioning key column values map lower than the specified value. The second partition contains all rows whose partitioning key column values map greater than or equal to the specified value.
You can optionally specify new attributes for the two partitions resulting from the split. If there are local indexes defined on the table, this statement also splits the matching partition in each local index.
In the following example fee_katy is a partition in the table vet_cats, which has a local index, jaf1. There is also a global index, vet on the table. vet contains two partitions, vet_parta, and vet_partb.
To split the partition fee_katy, and rebuild the index partitions, issue the following statements:
ALTER TABLE vet_cats SPLIT PARTITION
fee_katy at (100) INTO ( PARTITION
fee_katy1, PARTITION fee_katy2);
ALTER INDEX JAF1 REBUILD PARTITION fee_katy1;
ALTER INDEX JAF1 REBUILD PARTITION fee_katy2;
ALTER INDEX VET REBUILD PARTITION vet_parta;
ALTER INDEX VET REBUILD PARTITION vet_partb;
|
Note: If you do not specify new partition names, then the database assigns names of the formSYS_Pn. You can examine the data dictionary to locate the names assigned to the new local index partitions. You may want to rename them. Any attributes that you do not specify are inherited from the original partition. |
You split a list partition by using the ALTER TABLE ... SPLIT PARTITION statement. The SPLIT PARTITION clause enables you to specify a list of literal values that define a partition into which rows with corresponding partitioning key values are inserted. The remaining rows of the original partition are inserted into a second partition whose value list contains the remaining values from the original partition. You can optionally specify new attributes for the two partitions that result from the split.
The following statement splits the partition region_east into two partitions:
ALTER TABLE sales_by_region
SPLIT PARTITION region_east VALUES ('CT', 'MA', 'MD')
INTO
( PARTITION region_east_1
TABLESPACE tbs2,
PARTITION region_east_2
STORAGE (INITIAL 8M))
PARALLEL 5;
The literal value list for the original region_east partition was specified as:
PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
The two new partitions are:
region_east_1 with a literal value list of ('CT','MA','MD')
region_east_2 inheriting the remaining literal value list of ('NY','NH','ME','VA','PA','NJ')
The individual partitions have new physical attributes specified at the partition level. The operation is executed with parallelism of degree 5.
You can split a default list partition just like you split any other list partition. This is also the only means of adding a partition to a list-partitioned table that contains a default partition. When you split the default partition, you create a new partition defined by the values that you specify, and a second partition that remains the default partition.
The following example splits the default partition of sales_by_region, thereby creating a new partition:
ALTER TABLE sales_by_region
SPLIT PARTITION region_unknown VALUES ('MT', 'WY', 'ID')
INTO
( PARTITION region_wildwest,
PARTITION region_unknown);
You split a range or a materialized interval partition using the ALTER TABLE ... SPLIT PARTITION statement in an interval-partitioned table. Splitting a range partition in the interval-partitioned table is described in "Splitting a Partition of a Range-Partitioned Table".
To split a materialized interval partition, you specify a value of the partitioning key column within the interval partition at which to split the partition. The first of the resulting two new partitions includes all rows in the original partition whose partitioning key column values map lower than the specified value. The second partition contains all rows whose partitioning key column values map greater than or equal to the specified value. The split partition operation moves the transition point up to the higher boundary of the partition you just split, and all materialized interval partitions lower than the newly split partitions are implicitly converted into range partitions, with their upper boundaries defined by the upper boundaries of the intervals.
You can optionally specify new attributes for the two range partitions resulting from the split. If there are local indexes defined on the table, then this statement also splits the matching partition in each local index. You cannot split interval partitions that have not yet been created.
The following example shows splitting the May 2007 partition in the monthly interval partitioned table transactions.
ALTER TABLE transactions
SPLIT PARTITION FOR(TO_DATE('01-MAY-2007','dd-MON-yyyy'))
AT (TO_DATE('15-MAY-2007','dd-MON-yyyy'));
This is the opposite of merging *-hash partitions. When you split *-hash partitions, the new subpartitions are rehashed into either the number of subpartitions specified in a SUBPARTITIONS or SUBPARTITION clause. Or, if no such clause is included, the new partitions inherit the number of subpartitions (and tablespaces) from the partition being split.
Note that the inheritance of properties is different when a *-hash partition is split, versus when two *-hash partitions are merged. When a partition is split, the new partitions can inherit properties from the original partition because there is only one parent. However, when partitions are merged, properties must be inherited from table level defaults because there are two parents and the new partition cannot inherit from either at the expense of the other.
The following example splits a range-hash partition:
ALTER TABLE all_seasons SPLIT PARTITION quarter_1
AT (TO_DATE('16-dec-1997','dd-mon-yyyy'))
INTO (PARTITION q1_1997_1 SUBPARTITIONS 4 STORE IN (ts1,ts3),
PARTITION q1_1997_2);
The rules for splitting an interval-hash partitioned table follow the rules for splitting an interval-partitioned table. As described in "Splitting a Partition of an Interval-Partitioned Table", the transition point is changed to the higher boundary of the split partition.
Partitions can be split at both the partition level and at the list subpartition level.
Splitting a partition of a *-list partitioned table is similar to the description in "Splitting a Partition of a Range-Partitioned Table". No subpartition literal value list can be specified for either of the new partitions. The new partitions inherit the subpartition descriptions from the original partition being split.
The following example splits the q1_1999 partition of the quarterly_regional_sales table:
ALTER TABLE quarterly_regional_sales SPLIT PARTITION q1_1999
AT (TO_DATE('15-Feb-1999','dd-mon-yyyy'))
INTO ( PARTITION q1_1999_jan_feb
TABLESPACE ts1,
PARTITION q1_1999_feb_mar
STORAGE (INITIAL 8M) TABLESPACE ts2)
PARALLEL 5;
This operation splits the partition q1_1999 into two resulting partitions: q1_1999_jan_feb and q1_1999_feb_mar. Both partitions inherit their subpartition descriptions from the original partition. The individual partitions have new physical attributes, including tablespaces, specified at the partition level. These new attributes become the default attributes of the new partitions. This operation is run with parallelism of degree 5.
The ALTER TABLE ... SPLIT PARTITION statement provides no means of specifically naming subpartitions resulting from the split of a partition in a composite partitioned table. However, for those subpartitions in the parent partition with names of the form partition name_subpartition name, the database generates corresponding names in the newly created subpartitions using the new partition names. All other subpartitions are assigned system generated names of the form SYS_SUBPn. System generated names are also assigned for the subpartitions of any partition resulting from the split for which a name is not specified. Unnamed partitions are assigned a system generated partition name of the form SYS_Pn.
The following query displays the subpartition names resulting from the previous split partition operation on table quarterly_regional_sales. It also reflects the results of other operations performed on this table in preceding sections of this chapter since its creation in "Creating Composite Range-List Partitioned Tables".
SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='QUARTERLY_REGIONAL_SALES' ORDER BY PARTITION_NAME; PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME -------------------- ------------------------------ --------------- Q1_1999_FEB_MAR Q1_1999_FEB_MAR_WEST TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_NORTHEAST TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_SOUTHEAST TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_NORTHCENTRAL TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_SOUTHCENTRAL TS2 Q1_1999_FEB_MAR Q1_1999_FEB_MAR_SOUTH TS2 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_WEST TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_NORTHEAST TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_SOUTHEAST TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_NORTHCENTRAL TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_SOUTHCENTRAL TS1 Q1_1999_JAN_FEB Q1_1999_JAN_FEB_SOUTH TS1 Q1_2000 Q1_2000_NORTHWEST TS3 Q1_2000 Q1_2000_SOUTHWEST TS3 Q1_2000 Q1_2000_NORTHEAST TS3 Q1_2000 Q1_2000_SOUTHEAST TS3 Q1_2000 Q1_2000_NORTHCENTRAL TS3 Q1_2000 Q1_2000_SOUTHCENTRAL TS3 Q2_1999 Q2_1999_NORTHWEST TS4 Q2_1999 Q2_1999_SOUTHWEST TS4 Q2_1999 Q2_1999_NORTHEAST TS4 Q2_1999 Q2_1999_SOUTHEAST TS4 Q2_1999 Q2_1999_NORTHCENTRAL TS4 Q2_1999 Q2_1999_SOUTHCENTRAL 9HƷ TS4 Q3_1999 Q3_1999_NORTHWEST TS4 Q3_1999 Q3_1999_SOUTHWEST TS4 Q3_1999 Q3_1999_NORTHEAST TS4 Q3_1999 Q3_1999_SOUTHEAST TS4 Q3_1999 Q3_1999_NORTHCENTRAL TS4 Q3_1999 Q3_1999_SOUTHCENTRAL TS4 Q4_1999 Q4_1999_NORTHWEST TS4 Q4_1999 Q4_1999_SOUTHWEST TS4 Q4_1999 Q4_1999_NORTHEAST TS4 Q4_1999 Q4_1999_SOUTHEAST TS4 Q4_1999 Q4_1999_NORTHCENTRAL TS4 Q4_1999 Q4_1999_SOUTHCENTRAL TS4 36 rows selected.
Splitting a list subpartition of a *-list partitioned table is similar to the description in "Splitting a Partition of a List-Partitioned Table", but the syntax is that of SUBPARTITION rather than PARTITION. For example, the following statement splits a subpartition of the quarterly_regional_sales table:
ALTER TABLE quarterly_regional_sales SPLIT SUBPARTITION q2_1999_southwest
VALUES ('UT') INTO
( SUBPARTITION q2_1999_utah
TABLESPACE ts2,
SUBPARTITION q2_1999_southwest
TABLESPACE ts3
)
PARALLEL;
This operation splits the subpartition q2_1999_southwest into two subpartitions:
q2_1999_utah with literal value list of ('UT')
q2_1999_southwest which inherits the remaining literal value list of ('AZ','NM')
The individual subpartitions have new physical attributes that are inherited from the subpartition being split.
You can only split subpartitions in an interval-list partitioned table for range partitions or materialized interval partitions. To change subpartition values for future interval partitions, you must modify the subpartition template.
Splitting a partition of a *-range partitioned table is similar to the description in "Splitting a Partition of a Range-Partitioned Table". No subpartition range values can be specified for either of the new partitions. The new partitions inherit the subpartition descriptions from the original partition being split.
The following example splits the May 2007 interval partition of the interval-range partitioned orders table:
ALTER TABLE orders
SPLIT PARTITION FOR(TO_DATE('01-MAY-2007','dd-MON-yyyy'))
AT (TO_DATE('15-MAY-2007','dd-MON-yyyy'))
INTO (PARTITION p_fh_may07,PARTITION p_sh_may2007);
This operation splits the interval partition FOR('01-MAY-2007') into two resulting partitions: p_fh_may07 and p_sh_may_2007. Both partitions inherit their subpartition descriptions from the original partition. Any interval partitions before the June 2007 partition have been converted into range partitions, as described in "Merging Interval Partitions".
The ALTER TABLE ... SPLIT PARTITION statement provides no means of specifically naming subpartitions resulting from the split of a partition in a composite partitioned table. However, for those subpartitions in the parent partition with names of the form partition name_subpartition name, the database generates corresponding names in the newly created subpartitions using the new partition names. All other subpartitions are assigned system generated names of the form SYS_SUBPn. System generated names are also assigned for the subpartitions of any partition resulting from the split for which a name is not specified. Unnamed partitions are assigned a system generated partition name of the form SYS_Pn.
The following query displays the subpartition names and high values resulting from the previous split partition operation on table orders. It also reflects the results of other operations performed on this table in preceding sections of this chapter since its creation.
BREAK ON partition_name
SELECT partition_name, subpartition_name, high_value
FROM user_tab_subpartitions
WHERE table_name = 'ORCERS'
ORDER BY partition_name, subpartition_position;
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
------------------------- ------------------------------ ---------------
P_BEFORE_2007 P_BEFORE_2007_P_SMALL 1000
P_BEFORE_2007_P_MEDIUM 10000
P_BEFORE_2007_P_LARGE 100000
P_BEFORE_2007_P_EXTRAORDINARY MAXVALUE
P_FH_MAY07 SYS_SUBP2985 1000
SYS_SUBP2986 10000
SYS_SUBP2987 100000
SYS_SUBP2988 MAXVALUE
P_PRE_MAY_2007 P_PRE_MAY_2007_P_SMALL 1000
P_PRE_MAY_2007_P_MEDIUM 10000
P_PRE_MAY_2007_P_LARGE 100000
P_PRE_MAY_2007_P_EXTRAORDINARY MAXVALUE
P_SH_MAY2007 SYS_SUBP2989 1000
SYS_SUBP2990 10000
SYS_SUBP2991 100000
SYS_SUBP2992 MAXVALUE
Splitting a range subpartition of a *-range partitioned table is similar to the description in "Splitting a Partition of a Range-Partitioned Table", but the syntax is that of SUBPARTITION rather than PARTITION. For example, the following statement splits a subpartition of the orders table:
ALTER TABLE orders
SPLIT SUBPARTITION p_pre_may_2007_p_large AT (50000)
INTO (SUBPARTITION p_pre_may_2007_med_large TABLESPACE TS4
, SUBPARTITION p_pre_may_2007_large_large TABLESPACE TS5
);
This operation splits the subpartition p_pre_may_2007_p_large into two subpartitions:
p_pre_may_2007_med_large with values between 10000 and 50000
p_pre_may_2007_large_large with values between 50000 and 100000
The individual subpartitions have new physical attributes that are inherited from the subpartition being split.
You can only split subpartitions in an interval-range partitioned table for range partitions or materialized interval partitions. To change subpartition boundaries for future interval partitions, you must modify the subpartition template.
You cannot explicitly split a partition in a local index. A local index partition is split only when you split a partition in the underlying table. However, you can split a global index partition as is done in the following example:
ALTER INDEX quon1 SPLIT
PARTITION canada AT ( 100 ) INTO
PARTITION canada1 ..., PARTITION canada2 ...);
ALTER INDEX quon1 REBUILD PARTITION canada1;
ALTER INDEX quon1 REBUILD PARTITION canada2;
The index being split can contain index data, and the resulting partitions do not require rebuilding, unless the original partition was previously marked UNUSABLE.
Oracle Database implements a SPLIT PARTITION operation by creating two new partitions and redistributing the rows from the partition being split into the two new partitions. This is a time-consuming operation because it is necessary to scan all the rows of the partition being split and then insert them one-by-one into the new partitions. Further if you do not use the UPDATE INDEXES clause, then both local and global indexes also require rebuilding.
Sometimes after a split operation, one new partition contains all of the rows from the partition being split, while the other partition contains no rows. This is often the case when splitting the first partition of a table. The database can detect such situations and can optimize the split operation. This optimization results in a fast split operation that behaves like an add partition operation.
Specifically, the database can optimize and speed up SPLIT PARTITION operations if all of the following conditions are met:
One of the two resulting partitions must be empty.
The non-empty resulting partition must have storage characteristics identical to those of the partition being split. Specifically:
If the partition being split is composite, then the storage characteristics of each subpartition in the new non-empty resulting partition must be identical to those of the subpartitions of the partition being split.
If the partition being split contains a LOB column, then the storage characteristics of each LOB (sub)partition in the new non-empty resulting partition must be identical to those of the LOB (sub)partitions of the partition being split.
If a partition of an index-organized table with overflow is being split, then the storage characteristics of each overflow (sub)partition in the new nonempty resulting partition must be identical to those of the overflow (sub)partitions of the partition being split.
If a partition of an index-organized table with mapping table is being split, then the storage characteristics of each mapping table (sub)partition in the new nonempty resulting partition must be identical to those of the mapping table (sub)partitions of the partition being split.
If these conditions are met after the split, then all global indexes remain usable, even if you did not specify the UPDATE INDEXES clause. Local index (sub)partitions associated with both resulting partitions remain usable if they were usable before the split. Local index (sub)partitions corresponding to the non-empty resulting partition are identical to the local index (sub)partitions of the partition that was split. The same optimization holds for SPLIT SUBPARTITION operations.
Use the ALTER TABLE ... TRUNCATE PARTITION statement to remove all rows from a table partition. Truncating a partition is similar to dropping a partition, except that the partition is emptied of its data, but not physically dropped.
You cannot truncate an index partition. However, if local indexes are defined for the table, the ALTER TABLE ... TRUNCATE PARTITION statement truncates the matching partition in each local index. Unless you specify UPDATE INDEXES, any global indexes are marked UNUSABLE and must be rebuilt. (You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)
Use the ALTER TABLE ... TRUNCATE PARTITION statement to remove all rows from a table partition, with or without reclaiming space. Truncating a partition in an interval-partitioned table does not move the transition point. You can truncate partitions and subpartitions in a reference-partitioned table.
If the partition contains data and global indexes, use one of the following methods to truncate the table partition.
Method 1
Leave the global indexes in place during the ALTER TABLE ... TRUNCATE PARTITION statement. In this example, table sales has a global index sales_area_ix, which is rebuilt.
ALTER TABLE sales TRUNCATE PARTITION dec98; ALTER INDEX sales_area_ix REBUILD;
This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table.
Method 2
Run the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... TRUNCATE PARTITION statement. The DELETE statement updates the global indexes, and also fires triggers and generates redo and undo logs.
For example, to truncate the first partition, run the following statements:
DELETE FROM sales PARTITION (dec98); ALTER TABLE sales TRUNCATE PARTITION dec98;
This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.
Method 3
Specify UPDATE INDEXES in the ALTER TABLE statement. This causes the global index to be truncated at the time the partition is truncated.
ALTER TABLE sales TRUNCATE PARTITION dec98
UPDATE INDEXES;
If a partition contains data and has referential integrity constraints, then you cannot truncate the partition. If no other data is referencing any data in the partition to remove, then choose either of the following methods to truncate the table partition.
Method 1
Disable the integrity constraints, run the ALTER TABLE ... TRUNCATE PARTITION statement, then re-enable the integrity constraints. This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table. If there is still referencing data in other tables, then you must remove that data to be able to re-enable the integrity constraints.
Method 2
Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... TRUNCATE PARTITION statement. The DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo logs. Data in referencing tables is deleted if the foreign key constraints were created with the ON DELETE CASCADE option.
|
Note: You can substantially reduce the amount of logging by setting theNOLOGGING attribute (using ALTER TABLE ... MODIFY PARTITION ... NOLOGGING) for the partition before deleting all of its rows. |
DELETE FROM sales partition (dec94); ALTER TABLE sales TRUNCATE PARTITION dec94;
This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.
You use the ALTER TABLE ... TRUNCATE SUBPARTITION statement to remove all rows from a subpartition of a composite partitioned table. Corresponding local index subpartitions are also truncated.
The following statement shows how to truncate data in a subpartition of a table. In this example, the space occupied by the deleted rows is made available for use by other schema objects in the tablespace.
ALTER TABLE diving
TRUNCATE SUBPARTITION us_locations
DROP STORAGE;
Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a rich variety of partitioning strategies and extensions to address every business requirement. Because it is entirely transparent, partitioning can be applied to almost any application without the need for potentially expensive and time consuming application changes.
This chapter contains the following sections:
Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.
This section contains the following topics:
From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually. This gives an administrator considerable flexibility in managing partitioned objects. However, from the perspective of the application, a partitioned table is identical to a nonpartitioned table; no modifications are necessary when accessing a partitioned table using SQL queries and DML statements.
Figure 2-1 offers a graphical view of how partitioned tables differ from nonpartitioned tables.
|
Note: All partitions of a partitioned object must reside in tablespaces of a single block size. |
Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key consists of one or more columns that determine the partition where each row is stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition with the partitioning key.
Any table can be partitioned into a million separate partitions except those tables containing columns with LONG or LONG RAW data types. You can, however, use tables containing columns with CLOB or BLOB data types.
This sections contains the following topics:
|
Note: To reduce disk and memory usage (specifically, the buffer cache), you can store tables and partitions of a partitioned table in a compressed format inside the database. This often improves scaleup for read-only operations. Table compression can also speed up query execution. There is, however, a slight cost in CPU overhead. |
|
See Also: Oracle Database Concepts and Oracle Database Administrator's Guide for more information about table compression |
Here are some suggestions for when to partition a table:
Tables greater than 2 GB should always be considered as candidates for partitioning.
Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
When the contents of a table must be distributed across different types of storage devices.
Here are some suggestions for when to consider partitioning an index:
Avoid rebuilding the entire index when data is removed.
Perform maintenance on parts of the data without invalidating the entire index.
Reduce the effect of index skew caused by an index on a column with a monotonically increasing value.
Partitioned index-organized tables are very useful for providing improved performance, manageability, and availability for index-organized tables.
For partitioning an index-organized table:
Partition columns must be a subset of the primary key columns.
Secondary indexes can be partitioned (both locally and globally).
OVERFLOW data segments are always equipartitioned with the table partitions.
System partitioning enables application-controlled partitioning without having the database controlling the data placement. The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application. For example, an attempt to insert into a system partitioned table without the explicit specification of a partition fails.
System partitioning provides the well-known benefits of partitioning (scalability, availability, and manageability), but the partitioning and actual data placement are controlled by the application.
|
See Also: Oracle Database Data Cartridge Developer's Guide for more information about system partitioning |
Information Lifecycle Management (ILM) is concerned with managing data during its lifetime. Partitioning plays a key role in ILM because it enables groups of data (that is, partitions) to be distributed across different types of storage devices and managed individually.
For more information about Information Lifecycle Management, see Chapter 5, "Using Partitioning for Information Lifecycle Management".
Unstructured data (such as images and documents) which is stored in a LOB column in a database can also be partitioned. When a table is partitioned, all of the columns reside in the tablespace for that partition, except LOB columns, which can be stored in their own tablespace.
This technique is very useful when a table consists of large LOBs because they can be stored separately from the main data. This can be beneficial if the main data is being frequently updated but the LOB data is not. For example, an employee record may contain a photo which is unlikely to change frequently. However, the employee personnel details (such as address, department, manager, and so on) could change. This approach also means that less expensive storage can be used for storing the LOB data and more expensive, faster storage can be used for the employee record.
Partitioning when using XMLType and object tables and columns offers the standard advantages of partitioning, such as enabling tables and indexes to be subdivided into smaller pieces, thus enabling these database objects to be managed and accessed at a finer level of granularity.
When you partition an XMLType table or a table with an XMLType column using list, range, or hash partitioning, any ordered collection tables (OCTs) within the data are automatically partitioned accordingly, by default. This equipartitioning means that the partitioning of an OCT follows the partitioning scheme of its parent (base) table. There is a corresponding collection-table partition for each partition of the base table. A child element is stored in the collection-table partition that corresponds to the base-table partition of its parent element.
If you partition a table that has a nested table, then Oracle Database uses the partitioning scheme of the original base table as the basis for how the nested table is partitioned. This partitioning of one base table partition for each nested table partition is called equipartitioning. By default, nested tables are automatically partitioned when the base table is partitioned. Note, however, that composite partitioning is not supported for OCTs or nested tables.
For information about partitioning an XMLType table, refer to "Partitioning of Collections in XMLType and Objects".
Partitioning can provide tremendous benefit to a wide variety of applications by improving performance, manageability, and availability. It is not unusual for partitioning to greatly improve the performance of certain queries or maintenance operations. Moreover, partitioning can greatly simplify common administration tasks.
Partitioning also enables database designers and administrators to solve some difficult problems posed by cutting-edge applications. Partitioning is a key tool for building multi-terabyte systems or systems with extremely high availability requirements.
This section contains the following topics:
By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides multiple performance benefits. Partitioning features include:
Partition pruning is the simplest and also the most substantial means to improve performance using partitioning. Partition pruning can often improve query performance by several orders of magnitude. For example, suppose an application contains an Orders table containing a historical record of orders, and that this table has been partitioned by week. A query requesting orders for a single week would only access a single partition of the Orders table. If the Orders table had 2 years of historical data, then this query would access one partition instead of 104 partitions. This query could potentially execute 100 times faster simply because of partition pruning.
Partition pruning works with all of Oracle performance features. Oracle uses partition pruning with any indexing or join technique, or parallel access method.
Partitioning can also improve the performance of multi-table joins by using a technique known as partition-wise joins. Partition-wise joins can be applied when two tables are being joined and both tables are partitioned on the join key, or when a reference partitioned table is joined with its parent table. Partition-wise joins break a large join into smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.
Partitioning enables you to partition tables and indexes into smaller, more manageable units, providing database administrators with the ability to pursue a divide and conquer approach to data management. With partitioning, maintenance operations can be focused on particular portions of tables. For example, you could back up a single partition of a table, rather than back up the entire table. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.
A typical usage of partitioning for manageability is to support a rolling window load process in a data warehouse. Suppose that you load new data into a table on a weekly basis. That table could be partitioned so that each partition contains one week of data. The load process is simply the addition of a new partition using a partition exchange load. Adding a single partition is much more efficient than modifying the entire table, because you do not need to modify any other partitions.
Partitioned database objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy. For example, if one partition of a partitioned table is unavailable, then all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against the available partitions for the table, and these database operations can run successfully, provided they do not need to access the unavailable partition.
The database administrator can specify that each partition be stored in a separate tablespace; the most common scenario is having these tablespaces stored on different storage tiers. Storing different partitions in different tablespaces enables you to do backup and recovery operations on each individual partition, independent of the other partitions in the table. Thus allowing the active parts of the database to be made available sooner so access to the system can continue, while the inactive data is still being restored. Moreover, partitioning can reduce scheduled downtime. The performance gains provided by partitioning may enable you to complete maintenance operations on large database objects in relatively small batch windows.
Oracle Partitioning offers three fundamental data distribution methods as basic partitioning strategies that control how data is placed into individual partitions:
Range
Hash
List
Using these data distribution methods, a table can either be partitioned as a single list or as a composite partitioned table:
Each partitioning strategy has different advantages and design considerations. Thus, each strategy is more appropriate for a particular situation.
A table is defined by specifying one of the following data distribution methodologies, using one or more columns as the partitioning key:
For example, consider a table with a column of type NUMBER as the partitioning key and two partitions less_than_five_hundred and less_than_one_thousand. The less_than_one_thousand partition contains rows where the following condition is true:
500 <= partitioning key < 1000
Figure 2-2 offers a graphical view of the basic partitioning strategies for a single-level partitioned table.
Figure 2-2 List, Range, and Hash Partitioning
Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. It is the most common type of partitioning and is often used with dates. For a table with a date column as the partitioning key, the January-2010 partition would contain rows with partitioning key values from 01-Jan-2010 to 31-Jan-2010.
Each partition has a VALUES LESS THAN clause, that specifies a non-inclusive upper bound for the partitions. Any values of the partitioning key equal to or higher than this literal are added to the next higher partition. All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause of the previous partition.
A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partitioning key, including the NULL value.
Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.
Hash partitioning is the ideal method for distributing data evenly across devices. Hash partitioning is also an easy-to-use alternative to range partitioning, especially when the data to be partitioned is not historical or has no obvious partitioning key.
|
Note: You cannot change the hashing algorithms used by partitioning. |
List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way. For a table with a region column as the partitioning key, the East Sales Region partition might contain values New York, Virginia, and Florida.
The DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate an error.
Composite partitioning is a combination of the basic data distribution methods; a table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition represent a logical subset of the data.
Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning. Figure 2-3 offers a graphical view of range-hash and range-list composite partitioning, as an example.
This section describes the following types of partitioning:
Composite range-range partitioning enables logical range partitioning along two dimensions; for example, partition by order_date and range subpartition by shipping_date.
Composite range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning.
Composite range-list partitioning partitions data using the range method, and within each partition, subpartitions it using the list method. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.
Composite list-range partitioning enables logical range subpartitioning within a given list partitioning strategy; for example, list partition by country_id and range subpartition by order_date.
Composite list-hash partitioning enables hash subpartitioning of a list-partitioned object; for example, to enable partition-wise joins.
In addition to the basic partitioning strategies, Oracle Database provides the following types of partitioning extensions:
The following extensions significantly enhance the manageability of partitioned tables:
Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database creates interval partitions for data with values that are beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.
For example, if you create an interval partitioned table with monthly intervals and you set the transition point at January 1, 2007, then the lower boundary for the January 2007 interval is January 1, 2007. The lower boundary for the July 2007 interval is July 1, 2007, regardless of whether the June 2007 partition was created.
When using interval partitioning, consider the following restrictions:
You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
Interval partitioning is not supported for index-organized tables.
You cannot create a domain index on an interval-partitioned table.
You can create single-level interval partitioned tables and the following composite partitioned tables:
Interval-range
Interval-hash
Interval-list
The following extensions extend the flexibility in defining partitioning keys:
Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints.
The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.
An example of reference partitioning is the Orders and LineItems tables related to each other by a referential constraint orderid_refconstraint. Namely, LineItems.order_id references Orders.order_id. The Orders table is range partitioned on order_date. Reference partitioning on orderid_refconstraint for LineItems leads to creation of the following partitioned table, which is equipartitioned on the Orders table, as shown in Figure 2-4 and Figure 2-5.
All basic partitioning strategies are available for reference partitioning. Interval partitioning cannot be used with reference partitioning.
In previous releases of Oracle Database, a table could only be partitioned if the partitioning key physically existed in the table. Virtual columns remove that restriction and enable the partitioning key to be defined by an expression, using one or more existing columns of a table. The expression is stored as metadata only.
Oracle Partitioning has been enhanced to enable a partitioning strategy to be defined on virtual columns. For example, a 10-digit account ID can include account branch information as the leading three digits. With the extension of virtual column based partitioning, an ACCOUNTS table containing an ACCOUNT_ID column can be extended with a virtual (derived) column ACCOUNT_BRANCH. ACCOUNT_BRANCH is derived from the first three digits of the ACCOUNT_ID column, which becomes the partitioning key for this table.
Virtual column-based partitioning is supported with all basic partitioning strategies, including reference partitioning, and interval and interval-* composite partitioning.
Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or decision support systems (DSS) applications. Also, whenever possible, try to use local indexes because they are easier to manage.
This section contains the following topics:
When deciding what kind of partitioned index to use, you should consider the following guidelines in this order:
If the table partitioning column is a subset of the index keys, then use a local index. If this is the case, then you are finished. If this is not the case, then continue to guideline 2.
If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.
If your priority is manageability, then use a local index. If this is the case, then you are finished. If this is not the case, continue to guideline 4.
If the application is an OLTP type and users need quick response times, then use a global index. If the application is a DSS type and users are more interested in throughput, then use a local index.
For more information about partitioned indexes and how to decide which type to use, refer to Chapter 6, "Using Partitioning in a Data Warehouse Environment" and Chapter 7, "Using Partitioning in an Online Transaction Processing Environment".
Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments. The reason for this is equipartitioning: each partition of a local index is associated with exactly one partition of the table. This functionality enables Oracle to automatically keep the index partitions synchronized with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.
Local partitioned indexes support more availability when there are partition or subpartition maintenance operations on the table. A type of index called a local nonprefixed index is very useful for historical databases. In this type of index, the partitioning is not on the left prefix of the index columns. For more information about prefixed indexes, refer to "Index Partitioning".
You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.
A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.
Figure 2-6 offers a graphical view of local partitioned indexes.
For more information about local partitioned indexes, refer to "Local Partitioned Indexes".
Oracle offers global range partitioned indexes and global hash partitioned indexes, discussed in the following topics:
Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method.
The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.
You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. To add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.
Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.
By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
ADD (HASH) COALESCE (HASH) DROP EXCHANGE MERGE MOVE SPLIT TRUNCATE
These indexes can be maintained by appending the clause UPDATE INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:
The index remains available and online throughout the operation. Hence no other applications are affected by this operation.
The index does not have to be rebuilt after the operation.
|
Note: This feature is supported only for heap-organized tables. |
Figure 2-7 offers a graphical view of global partitioned indexes.
For more information about global partitioned indexes, refer to "Global Partitioned Indexes".
Global nonpartitioned indexes behave just like a nonpartitioned index.
Figure 2-8 offers a graphical view of global nonpartitioned indexes.
You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.
Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.
Parallel execution enables the application of multiple CPU and I/O resources to the execution of a single database operation. It dramatically reduces response time for data-intensive operations on large databases typically associated with a decision support system (DSS) and data warehouses. You can also implement parallel execution on an online transaction processing (OLTP) system for batch processing or schema maintenance operations such as index creation. Parallel execution is sometimes called parallelism. Parallelism is the idea of breaking down a task so that, instead of one process doing all of the work in a query, many processes do part of the work at the same time. An example of this is when four processes combine to calculate the total sales for a year, each process handles one quarter of the year instead of a single process handling all four quarters by itself. The improvement in performance can be quite significant. Parallel execution improves processing for:
Queries requiring large table scans, joins, or partitioned index scans
Creation of large indexes
Creation of large tables (including materialized views)
Bulk insertions, updates, merges, and deletions
You can also use parallel execution to access object types within Oracle Database. For example, you can use parallel execution to access large objects (LOBs).
If the necessary parallel server processes are not available for parallel execution, a SQL statement is queued when the parallel degree policy is set to automatic. After the necessary resources become available, the SQL statement is dequeued and allowed to execute. The parallel statement queue operates as a first-in, first-out queue by default. If the query in front of the queue cannot be scheduled, none of the queries in the queue can be scheduled even if resources are available in the system to ensure that the query at the head of the queue has adequate resources. However, if you configure and set up a resource plan, then you can control the order in which parallel statements are dequeued and the number of parallel servers used by each workload or consumer group. For information, refer to "Managing Parallel Statement Queuing with Resource Manager".
This section contains the following topics:
Parallel execution benefits systems with all of the following characteristics:
Symmetric multiprocessors (SMPs), clusters, or massively parallel systems
Underutilized or intermittently used CPUs (for example, systems where CPU usage is typically less than 30%)
Sufficient memory to support additional memory-intensive processes, such as sorting, hashing, and I/O buffers
If your system lacks any of these characteristics, parallel execution might not significantly improve performance. In fact, parallel execution may reduce system performance on overutilized systems or systems with small I/O bandwidth.
The benefits of parallel execution can be seen in DSS and data warehouse environments. OLTP systems can also benefit from parallel execution during batch processing and during schema maintenance operations such as creation of indexes. The average simple DML or SELECT statements that characterize OLTP applications would not experience any benefit from being executed in parallel.
Parallel execution is not typically useful for:
Environments in which the typical query or transaction is very short (a few seconds or less).
This includes most online transaction systems. Parallel execution is not useful in these environments because there is a cost associated with coordinating the parallel execution servers; for short transactions, the cost of this coordination may outweigh the benefits of parallelism.
Environments in which the CPU, memory, or I/O resources are heavily utilized.
Parallel execution is designed to exploit additional available hardware resources; if no such resources are available, then parallel execution does not yield any benefits and indeed may be detrimental to performance.
Parallel execution is designed to effectively use multiple CPUs and disks to answer queries quickly. It is very I/O intensive by nature. To achieve optimal performance, each component in the hardware configuration must be sized to sustain the same level of throughput: from the CPUs and the Host Bus Adapters (HBAs) in the compute nodes, to the switches, and on into the I/O subsystem, including the storage controllers and the physical disks. If the system is an Oracle Real Application Clusters (Oracle RAC) system, then the interconnection also has to be sized appropriately. The weakest link is going to limit the performance and scalability of operations in a configuration.
It is recommended to measure the maximum I/O performance a hardware configuration can achieve without Oracle database. This measurement can be used as a baseline for the future system performance evaluations. Remember, it is not possible for parallel execution to achieve better I/O throughput than the underlying hardware can sustain. Oracle Database provides a free calibration tool called Orion, which is designed to measure the I/O performance of a system by simulating Oracle I/O workloads. A parallel execution typically performs large random I/Os.
You can use parallel execution for any of the following:
Access methods
Some examples are table scans, index fast full scans, and partitioned index range scans.
Join methods
Some examples are nested loop, sort merge, hash, and star transformation.
DDL statements
Some examples are CREATE TABLE AS SELECT, CREATE INDEX, REBUILD INDEX, REBUILD INDEX PARTITION, and MOVE/SPLIT/COALESCE PARTITION.
You can typically use parallel DDL where you use regular DDL. There are, however, some additional details to consider when designing your database. One important restriction is that parallel DDL cannot be used on tables with object or LOB columns.
All of these DDL operations can be performed in NOLOGGING mode for either parallel or serial execution.
The CREATE TABLE statement for an index-organized table can be run with parallel execution either with or without an AS SELECT clause.
Different parallelism is used for different operations. Parallel CREATE (partitioned) TABLE AS SELECT and parallel CREATE (partitioned) INDEX statements run with a degree of parallelism (DOP) equal to the number of partitions.
DML statements
Some examples are INSERT AS SELECT, UPDATE, DELETE, and MERGE operations.
Parallel DML (parallel insert, update, merge, and delete operations) uses parallel execution mechanisms to speed up or scale up large DML operations against large database tables and indexes. You can also use INSERT ... SELECT statements to insert rows into multiple tables as part of a single DML statement. You can ordinarily use parallel DML where you use regular DML.
Although data manipulation language usually includes queries, the term parallel DML refers only to inserts, updates, merges, and deletes done in parallel.
Parallel query
You can run queries and subqueries in parallel in SELECT statements, plus the query portions of DDL statements and DML statements (INSERT, UPDATE, DELETE, and MERGE).
Miscellaneous SQL operations
Some examples are GROUP BY, NOT IN, SELECT DISTINCT, UNION, UNION ALL, CUBE, and ROLLUP, plus aggregate and table functions.
SQL*Loader
You can use SQL*Loader in parallel execution where large amounts of data are routinely encountered. To speed up your load operations, you can use a parallel direct-path load as in the following example:
sqlldr CONTROL=LOAD1.CTL DIRECT=TRUE PARALLEL=TRUE sqlldr CONTROL=LOAD2.CTL DIRECT=TRUE PARALLEL=TRUE sqlldr CONTROL=LOAD3.CTL DIRECT=TRUE PARALLEL=TRUE
You provide your user Id and password on the command line. You can also use a parameter file to achieve the same result.
An important point to remember is that indexes are not maintained during a parallel load.
This section discusses the following topics:
You can set initialization parameters to determine resource limits. The parameters that establish resource limits are discussed in the following topics:
This parameter specifies whether a SQL statement executed in parallel is restricted to a single instance in an Oracle RAC environment. By setting this parameter to TRUE, you restrict the scope of the parallel server processed to the single Oracle RAC instance where the query coordinator is running.
The recommended value for the PARALLEL_FORCE_LOCAL parameter is FALSE.
PARALLEL_THREADS_PER_CPU x CPU_COUNT x concurrent_parallel_users x 5
In the formula, the value assigned to concurrent_parallel_users running at the default degree of parallelism on an instance is dependent on the memory management setting. If automatic memory management is disabled (manual mode), then the value of concurrent_parallel_users is 1. If PGA automatic memory management is enabled, then the value of concurrent_parallel_users is 2. If global memory management or SGA memory target is used in addition to PGA automatic memory management, then the value of concurrent_parallel_users is 4.
The preceding formula might not be sufficient for parallel queries on tables with higher degree of parallelism (DOP) attributes. For users who expect to run queries of higher DOP, set the value to the following:
2 x DOP x NUMBER_OF_CONCURRENT_USERS
For example, setting the value to 64 enables you to run four parallel queries simultaneously, if each query is using two slave sets with a DOP of 8 for each set.
When concurrent users have too many query server processes, memory contention (paging), I/O contention, or excessive context switching can occur. This contention can reduce system throughput to a level lower than if parallel execution were not used. Increase the PARALLEL_MAX_SERVERS value only if the system has sufficient memory and I/O bandwidth for the resulting load.
You can use performance monitoring tools of the operating system to determine how much memory, swap space and I/O bandwidth are free. Look at the run queue lengths for both your CPUs and disks, and the service time for I/O operations on the system. Verify that the system has sufficient swap space to add more processes. Limiting the total number of query server processes might restrict the number of concurrent users who can execute parallel operations, but system throughput tends to remain stable.
You can limit the amount of parallelism available to a given user by establishing a resource consumer group for the user. Do this to limit the number of sessions, concurrent logons, and the number of parallel processes that any one user or group of users can have.
Each query server process working on a parallel execution statement is logged on with a session ID. Each process counts against the user's limit of concurrent sessions. For example, to limit a user to 10 parallel execution processes, set the user's limit to 11. One process is for the parallel execution coordinator and the other 10 consist of two sets of query servers. This would allow one session for the parallel execution coordinator and 10 sessions for the parallel execution processes.
See Oracle Database Administrator's Guide for more information about managing resources with user profiles and Oracle Real Application Clusters Administration and Deployment Guide for more information about querying GV$ views.
This parameter enables users to wait for an acceptable DOP, depending on the application in use. The recommended value for the PARALLEL_MIN_PERCENT parameter is 0 (zero).
Setting this parameter to values other than 0 (zero) causes Oracle Database to return an error when the requested DOP cannot be satisfied by the system at a given time. For example, if you set PARALLEL_MIN_PERCENT to 50, which translates to 50 percent, and the DOP is reduced by 50 percent or greater because of the adaptive algorithm or because of a resource limitation, then Oracle Database returns ORA-12827. For example:
SELECT /*+ FULL(e) PARALLEL(e, 8) */ d.department_id, SUM(SALARY) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_id ORDER BY d.department_id;
Oracle Database responds with this message:
ORA-12827: insufficient parallel query slaves available
This parameter specifies the number of processes to be started in a single instance that are reserved for parallel operations. The syntax is:
PARALLEL_MIN_SERVERS=n
The n variable is the number of processes you want to start and reserve for parallel operations.
Setting PARALLEL_MIN_SERVERS balances the startup cost against memory usage. Processes started using PARALLEL_MIN_SERVERS do not exit until the database is shut down. This way, when a query is issued, the processes are likely to be available.
This parameter specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 10 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED. The syntax is:
PARALLEL_MIN_TIME_THRESHOLD = { AUTO | integer }
The default is AUTO.
This parameter specifies the number of parallel server processes allowed to run parallel statements before statement queuing is used. The default value is:
PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2
In the formula, the value assigned to concurrent_parallel_users running at the default degree of parallelism on an instance is dependent on the memory management setting. If automatic memory management is disabled (manual mode), then the value of concurrent_parallel_users is 1. If PGA automatic memory management is enabled, then the value of concurrent_parallel_users is 2. If global memory management or SGA memory target is used in addition to PGA automatic memory management, then the value of concurrent_parallel_users is 4.
When PARALLEL_DEGREE_POLICY is set to AUTO, statements that require parallel execution are queued if the number of parallel processes currently in use on the system equals or is greater than PARALLEL_SERVERS_TARGET. This is not the maximum number of parallel server processes allowed on a system (that is controlled by PARALLEL_MAX_SERVERS). However, PARALLEL_SERVERS_TARGET and parallel statement queuing is used to ensure that each statement that requires parallel execution is allocated the necessary parallel server resources and the system is not flooded with too many parallel server processes.
Parallel execution requires memory resources in addition to those required by serial SQL execution. Additional memory is used for communication and passing data between query server processes and the query coordinator.
Oracle Database allocates memory for query server processes from the shared pool. Tune the shared pool as follows:
Allow for other clients of the shared pool, such as shared cursors and stored procedures.
Remember that larger values improve performance in multiuser systems, but smaller values use less memory.
You can then monitor the number of buffers used by parallel execution and compare the shared pool PX msg pool to the current high water mark reported in output from the view V$PX_PROCESS_SYSSTAT.
|
Note: If you do not have enough memory available, error message 12853 occurs (insufficient memory for PX buffers: current stringK, max needed stringK). This is caused by having insufficient SGA memory available for PX buffers. You must reconfigure the SGA to have at least (MAX - CURRENT) bytes of additional memory. |
By default, Oracle Database allocates parallel execution buffers from the shared pool.
If Oracle Database displays the following error on startup, you should reduce the value for SHARED_POOL_SIZE low enough so your database starts:
ORA-27102: out of memory SVR4 Error: 12: Not enough space
After reducing the value of SHARED_POOL_SIZE, you might see the error:
ORA-04031: unable to allocate 16084 bytes of shared memory
("SHARED pool","unknown object","SHARED pool heap","PX msg pool")
If so, execute the following query to determine why Oracle Database could not allocate the 16,084 bytes:
SELECT NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL='SHARED POOL' GROUP BY ROLLUP (NAME);
Your output should resemble the following:
NAME SUM(BYTES)
-------------------------- ----------
PX msg pool 1474572
free memory 562132
2036704
If you specify SHARED_POOL_SIZE and the amount of memory you specify to reserve is bigger than the pool, Oracle Database does not allocate all the memory it can get. Instead, it leaves some space. When the query runs, Oracle Database tries to get what it needs. Oracle Database uses the 560 KB and needs another 16 KB when it fails. The error does not report the cumulative amount that is needed. The best way of determining how much more memory is needed is to use the formulas in "Adding Memory for Message Buffers".
To resolve the problem in the current example, increase the value for SHARED_POOL_SIZE. As shown in the sample output, the SHARED_POOL_SIZE is about 2 MB. Depending on the amount of memory available, you could increase the value of SHARED_POOL_SIZE to 4 MB and attempt to start your database. If Oracle Database continues to display an ORA-4031 message, gradually increase the value for SHARED_POOL_SIZE until startup is successful.
After you determine the initial setting for the shared pool, you must calculate additional memory requirements for message buffers and determine how much additional space you need for cursors.
You must increase the value for the SHARED_POOL_SIZE parameter to accommodate message buffers. The message buffers allow query server processes to communicate with each other.
Oracle Database uses a fixed number of buffers for each virtual connection between producer query servers and consumer query servers. Connections increase as the square of the DOP increases. For this reason, the maximum amount of memory used by parallel execution is bound by the highest DOP allowed on your system. You can control this value by using either the PARALLEL_MAX_SERVERS parameter or by using policies and profiles.
To calculate the amount of memory required, use one of the following formulas:
For SMP systems:
mem in bytes = (3 x size x users x groups x connections)
For Oracle Real Application Clusters and MPP systems:
mem in bytes = ((3 x local) + (2 x remote)) x (size x users x groups) / instances
Each instance uses the memory computed by the formula.
The terms are:
SIZE = PARALLEL_EXECUTION_MESSAGE_SIZE
USERS = the number of concurrent parallel execution users that you expect to have running with the optimal DOP
GROUPS = the number of query server process groups used for each query
A simple SQL statement requires only one group. However, if your queries involve subqueries which are processed in parallel, then Oracle Database uses an additional group of query server processes.
CONNECTIONS = (DOP2 + 2 x DOP)
If your system is a cluster or MPP, then you should account for the number of instances because this increases the DOP. In other words, using a DOP of 4 on a two-instance cluster results in a DOP of 8. A value of PARALLEL_MAX_SERVERS times the number of instances divided by four is a conservative estimate to use as a starting point.
LOCAL = CONNECTIONS/INSTANCES
REMOTE = CONNECTIONS - LOCAL
Add this amount to your original setting for the shared pool. However, before setting a value for either of these memory structures, you must also consider additional memory for cursors, as explained in the following section.
The formulas in this section are just starting points. Whether you are using automated or manual tuning, you should monitor usage on an on-going basis to ensure the size of memory is not too large or too small. To ensure the correct memory size, tune the shared pool using the following query:
SELECT POOL, NAME, SUM(BYTES) FROM V$SGASTAT WHERE POOL LIKE '%pool%' GROUP BY ROLLUP (POOL, NAME);
Your output should resemble the following:
POOL NAME SUM(BYTES)
----------- -------------------------- ----------
shared pool Checkpoint queue 38496
shared pool KGFF heap 1964
shared pool KGK heap 4372
shared pool KQLS heap 1134432
shared pool LRMPD SGA Table 23856
shared pool PLS non-lib hp 2096
shared pool PX subheap 186828
shared pool SYSTEM PARAMETERS 55756
shared pool State objects 3907808
shared pool character set memory 30260
shared pool db_block_buffers 200000
shared pool db_block_hash_buckets 33132
shared pool db_files 122984
shared pool db_handles 52416
shared pool dictionary cache 198216
shared pool dlm shared memory 5387924
shared pool event statistics per sess 264768
shared pool fixed allocation callback 1376
shared pool free memory 26329104
shared pool gc_* 64000
shared pool latch nowait fails or sle 34944
shared pool library cache 2176808
shared pool log_buffer 24576
shared pool log_checkpoint_timeout 24700
shared pool long op statistics array 30240
shared pool message pool freequeue 116232
shared pool miscellaneous 267624
shared pool processes 76896
shared pool session param values 41424
shared pool sessions 170016
shared pool sql area 9549116
shared pool table columns 148104
shared pool trace_buffers_per_process 1476320
shared pool transactions 18480
shared pool trigger inform 24684
shared pool 52248968
90641768
Evaluate the memory used as shown in your output, and alter the setting for SHARED_POOL_SIZE based on your processing needs.
To obtain more memory usage statistics, execute the following query:
SELECT * FROM V$PX_PROCESS_SYSSTAT WHERE STATISTIC LIKE 'Buffers%';
Your output should resemble the following:
STATISTIC VALUE ------------------- ----- Buffers Allocated 23225 Buffers Freed 23225 Buffers Current 0 Buffers HWM 3620
The amount of memory used appears in the Buffers Current and Buffers HWM statistics. Calculate a value in bytes by multiplying the number of buffers by the value for PARALLEL_EXECUTION_MESSAGE_SIZE. Compare the high water mark to the parallel execution message pool size to determine if you allocated too much memory. For example, in the first output, the value for large pool as shown in px msg pool is 38,092,812 or 38 MB. The Buffers HWM from the second output is 3,620, which when multiplied by a parallel execution message size of 4,096 is 14,827,520, or approximately 15 MB. In this case, the high water mark has reached approximately 40 percent of its capacity.
Before considering the following section, you should read the descriptions of the MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters in Oracle Database Performance Tuning Guide and Oracle Database Administrator's Guide for details. The PGA_AGGREGATE_TARGET initialization parameter need not be set as MEMORY_TARGET autotunes the SGA and PGA components.
The first group of parameters discussed in this section affects memory and resource consumption for all parallel operations, in particular, for parallel execution. These parameters are:
A second subset of parameters are discussed in "Parameters Affecting Resource Consumption for Parallel DML and Parallel DDL".
To control resource consumption, you should configure memory at two levels:
At the database level, so the system uses an appropriate amount of memory from the operating system.
At the operating system level for consistency.
On some platforms, you might need to set operating system parameters that control the total amount of virtual memory available, totalled across all processes.
A large percentage of the memory used in data warehousing operations (compared to OLTP) is more dynamic. This memory comes from Process Global Area (PGA), and both the size of process memory and the number of processes can vary greatly. Use the PGA_AGGREGATE_TARGET initialization parameter to control both the process memory and the number of processes in such cases. Explicitly setting PGA_AGGREGATE_TARGET along with MEMORY_TARGET ensures that autotuning still occurs but PGA_AGGREGATE_TARGET is not tuned below the specified value.
You can simplify and improve the way PGA memory is allocated by enabling automatic PGA memory management. In this mode, Oracle Database dynamically adjusts the size of the portion of the PGA memory dedicated to work areas, based on an overall PGA memory target explicitly set by the DBA. To enable automatic PGA memory management, you must set the initialization parameter PGA_AGGREGATE_TARGET. For new installations, PGA_AGGREGATE_TARGET and SGA_TARGET are set automatically by the database configuration assistant (DBCA), and MEMORY_TARGET is zero. That is, automatic memory management is disabled. You can enable it in Oracle Enterprise Manager on the Memory Parameters page. Therefore, automatic tuning of the aggregate PGA is enabled by default. However, the aggregate PGA does not grow unless you enable automatic memory management by setting MEMORY_TARGET to a nonzero value.
See Oracle Database Performance Tuning Guide for descriptions of how to use PGA_AGGREGATE_TARGET in different scenarios.
The PARALLEL_EXECUTION_MESSAGE_SIZE parameter specifies the size of the buffer used for parallel execution messages. The default value is operating system-specific, but is typically 16 K. This value should be adequate for most applications.
The parameters that affect parallel DML and parallel DDL resource consumption are:
Parallel insert, update, and delete operations require more resources than serial DML operations. Similarly, PARALLEL CREATE TABLE ... AS SELECT and PARALLEL CREATE INDEX can require more resources. For this reason, you may need to increase the value of several additional initialization parameters. These parameters do not affect resources for queries.
For parallel DML and DDL, each query server process starts a transaction. The parallel execution coordinator uses the two-phase commit protocol to commit transactions; therefore, the number of transactions being processed increases by the DOP. Consequently, you might need to increase the value of the TRANSACTIONS initialization parameter.
The TRANSACTIONS parameter specifies the maximum number of concurrent transactions. The default assumes no parallelism. For example, if you have a DOP of 20, you have 20 more new server transactions (or 40, if you have two server sets) and 1 coordinator transaction. In this case, you should increase TRANSACTIONS by 21 (or 41) if the transactions are running in the same instance. If you do not set this parameter, Oracle Database sets it to a value equal to 1.1 x SESSIONS. This discussion does not apply if you are using server-managed undo.
If a system fails when there are uncommitted parallel DML or DDL transactions, you can speed up transaction recovery during startup by using the FAST_START_PARALLEL_ROLLBACK parameter.
This parameter controls the DOP used when recovering terminated transactions. Terminated transactions are transactions that are active before a system failure. By default, the DOP is chosen to be at most two times the value of the CPU_COUNT parameter.
If the default DOP is insufficient, set the parameter to HIGH. This gives a maximum DOP of at most four times the value of the CPU_COUNT parameter. This feature is available by default.
This parameter specifies the maximum number of DML locks. Its value should equal the total number of locks on all tables referenced by all users. A parallel DML operation's lock requirement is very different from serial DML. Parallel DML holds many more locks, so you should increase the value of the DML_LOCKS parameter by equal amounts.
|
Note: Parallel DML operationU#s are not performed when the table lock of the target table is disabled. |
Table 8-4 shows the types of locks acquired by coordinator and parallel execution server processes for different types of parallel DML statements. Using this information, you can determine the value required for these parameters.
Table 8-4 Locks Acquired by Parallel DML Statements
| Type of Statement | Coordinator Process Acquires: | Each Parallel Execution Server Acquires: |
|---|---|---|
|
Parallel |
1 table lock SX 1 partition lock X for each pruned partition or subpartition |
1 table lock SX 1 partition lock 1 partition-wait lock S for each pruned partition or subpartition owned by the query server process |
|
Parallel row-migrating |
1 table lock SX 1 partition X lock for each pruned partition or subpartition 1 partition lock SX for all other partitions or subpartitions |
1 table lock SX 1 partition lock 1 partition-wait lock S for each pruned partition owned by the query server process 1 partition lock SX for all other partitions or subpartitions |
|
Parallel |
1 table lock SX Partition locks X for all partitions or subpartitions |
1 table lock SX 1 partition lock 1 partition-wait lock S for each partition or subpartition |
|
Parallel |
1 table lock SX 1 partition lock X for each specified partition or subpartition |
1 table lock SX 1 partition lock 1 partition-wait lock S for each specified partition or subpartition |
|
Parallel |
1 table lock X |
None |
|
Note: Table, partition, and partition-wait DML locks all appear as TM locks in theV$LOCK view. |
Consider a table with 600 partitions running with a DOP of 100. Assume all partitions are involved in a parallel UPDATE or DELETE statement with no row-migrations.
The coordinator acquires:
1 table lock SX
600 partition locks X
Total server processes acquire:
100 table locks SX
600 partition locks NULL
600 partition-wait locks S
The parameters that affect I/O are:
These parameters also affect the optimizer, which ensures optimal performance for parallel execution of I/O operations.
When you perform parallel update, merge, and delete operations, the buffer cache behavior is very similar to any OLTP system running a high volume of updates.
The recommended value for this parameter is 8 KB or 16 KB.
Set the database block size when you create the database. If you are creating a new database, use a large block size such as 8 KB or 16 KB.
This parameter determines how many database blocks are read with a single operating system READ call. In this release, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. The maximum I/O size value is platform-dependent and is 1 MB for most platforms. If you set DB_FILE_MULTIBLOCK_READ_COUNT to an excessively high value, your operating system lowers the value to the highest allowable level when you start your database.
The recommended value for both of these parameters is TRUE.
These parameters enable or disable the operating system's asynchronous I/O facility. They allow query server processes to overlap I/O requests with processing when performing table scans. If the operating system supports asynchronous I/O, leave these parameters at the default value of TRUE. Figure 8-6 illustrates how asynchronous read works.
Asynchronous operations are currently supported for parallel table scans, hash joins, sorts, and serial table scans. However, this feature can require operating system-specific configuration and may not be supported on all platforms.
This book contains an overview of very large database (VLDB) topics, with emphasis on partitioning as a key component of the VLDB strategy. Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. This Preface contains the following topics:
This document is intended for database administrators (DBAs) and developers who create, manage, and write applications for very large databases (VLDB).
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.
For more information, see the following documents in the Oracle Database documentation set:
The following text conventions are used in this document:
| Convention | Meaning |
|---|---|
| boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
| italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
Oracle Database processes a DROP TABLE statement for a partitioned table in the same way that it processes the statement for a nonpartitioned table. One exception is when you use the PURGE keyword.
To avoid running into resource constraints, the DROP TABLE...PURGE statement for a partitioned table drops the table in multiple transactions, where each transaction drops a subset of the partitions or subpartitions and then commits. The table becomes completely dropped at the conclusion of the final transaction. This behavior comes with some changes to the DROP TABLE statement that you should be aware of.
First, if the DROP TABLE...PURGE statement fails, then you can take corrective action, if any, and then reissue the statement. The statement resumes at the point where it failed.
Second, while the DROP TABLE...PURGE statement is in progress, the table is marked as unusable by setting the STATUS column to the value UNUSABLE in the following data dictionary views:
USER_TABLES, ALL_TABLES, DBA_TABLES
USER_PART_TABLES, ALL_PART_TABLES, DBA_PART_TABLES
USER_OBJECT_TABLES, ALL_OBJECT_TABLES, DBA_OBJECT_TABLES
You can list all UNUSABLE partitioned tables by querying the STATUS column of these views.
Queries against other data dictionary views pertaining to partitioning, such as DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS, exclude rows belonging to an UNUSABLE table. A complete list of these views is available in "Viewing Information About Partitioned Tables and Indexes".
After a table is marked UNUSABLE, the only statement that can be issued against it is another DROP TABLE...PURGE statement, and only if the previous DROP TABLE...PURGE statement failed. Any other statement issued against an UNUSABLE table results in an error. The table remains in the UNUSABLE state until the drop operation is complete.
|
See Also:
|
This chapter discusses the components in Oracle Database which can build an Information Lifecycle Management (ILM) strategy to manage and maintain data.
Although most organizations have long regarded their stores of data as one of their most valuable corporate assets, how this data was managed and maintained varies enormously from company to company. Originally, data was used to help achieve operational goals, run the business, and help identify the future direction and success of the company.
However, new government regulations and guidelines are a key driving force in how and why data is being retained. Regulations now require organizations to retain and control information for very long periods of time. Consequently, today there are two additional objectives that information technology (IT) managers are trying to satisfy: to store vast quantities of data for the lowest possible cost and to meet the new regulatory requirements for data retention and protection.
This chapter contains the following sections:
Information today comes in a wide variety of types, for example an e-mail message, a photograph, or an order in an Online Transaction Processing (OLTP) System. After you know the type of data and how it is used, you have an understanding of what its evolution and final destiny is likely to be.
One challenge facing each organization is to understand how its data evolves and grows, monitor how its usage changes over time, and decide how long it should survive, while adhering to all the rules and regulations that now apply to that data. Information Lifecycle Management (ILM) is designed to address these issues, with a combination of processes, policies, software, and hardware so that the appropriate technology can be used for each stage in the lifecycle of the data.
This section contains the following topics:
Oracle Database provides the ideal platform for implementing an ILM solution, because it offers:
Application Transparency
Application transparency is very important in ILM because it means that there is no need to customize applications and it also enables various changes to be made to the data without any effect on the applications that are using that data. Data can easily be moved at the different stages of its lifecycle and access to the data can be optimized with the database. Another important benefit is that application transparency offers the flexibility required to quickly adapt to any new regulatory requirements, again without any impact on the existing applications.
Fine-grained data
Oracle can view data at a very fine-grained level and group related data, whereas storage devices only see bytes and blocks.
Low-Cost Storage
With so much data to retain, using low cost storage is a key factor in implementing ILM. Because Oracle can take advantage of many types of storage devices, the maximum amount of data can be held for the lowest possible cost.
Enforceable Compliance Policies
When information is kept for compliance reasons, it is imperative to show to regulatory bodies that data is being retained and managed in accordance with the regulations. Within Oracle Database, it is possible to define security and audit policies, which enforce and log all access to data.
Information Lifecycle Management is concerned with all data in an organization. This includes not just structured data, such as orders in an OLTP system or a history of sales in a data warehouse, but also unstructured data, such as e-mail, documents, and images.
Although Oracle Database supports the storing of unstructured data with BLOBs and Oracle Fast Files, a sophisticated document management system is available in Oracle Content Database, when used with the Enterprise Edition. It includes role-based security to ensure that content is only accessed by authorized personnel and policies which describe what happens to the content during its lifetime.
If all of the information in your organization is contained in an Oracle database, then you can take advantage of the features and functionality provided by the database to manage and move the data as it evolves during its lifetime, without having to manage multiple types of data stores.
Today, many organizations must retain specific data for a specific time period. Failure to follow these regulations could result in organizations having to pay very heavy fines. Around the world various regulatory requirements, such as Sarbanes-Oxley, HIPAA, DOD5015.2-STD in the US and the European Data Privacy Directive in the European Union, are changing how organizations manage their data. These regulations specify what data must be retained, whether it can be changed, and for how long it must be retained, which could be for a period of 30 years or longer.
These regulations frequently demand that electronic data is secure from unauthorized access and changes, and that there is an audit trail of all changes to data and by whom. Oracle Database can retain huge quantities of data without impacting application performance. It also contains the features required to restrict access and prevent unauthorized changes to data, and can be further enhanced with Oracle Database Vault and Oracle Audit Vault. Oracle Database also provides cryptographic functions that can demonstrate that a highly privileged user has not intentionally modified data. Flashback Data Archive can show all the versions of a row during its lifetime.
Building an Information Lifecycle Management solution using Oracle Database is quite straightforward and can be completed by following these four simple steps, although Step 4 is optional if ILM is not being implemented for compliance:
To make effective use of Information Lifecycle Management, the first step is to look at all the data in your organization and determine:
What data is important, where is it stored, and what must be retained
How this data flows within the organization
What happens to this data over time and whether it is still required
The degree of data availability and protection that is needed
Data retention for legal and business requirements
After there is an understanding of how the data is used, the data can then be classified on this basis. The most common type of classification is by age or date, but other types are possible, such as by product or privacy. A hybrid classification could also be used, such as by privacy and age.
To treat the data classes differently, the data must be physically separated. When information is first created, the information is often frequently accessed, but then over time it may be referenced very infrequently. For instance, when a customer places an order, they regularly look at the order to see its status and whether the order has been shipped. After the order arrives, they may never reference that order again. This order would also be included in regular reports that are run to see what goods are being ordered, but, over time, would not figure in any of the reports and may only be referenced in the future if someone does a detailed analysis that involves this data. For example, orders could be classified by the Financial Quarters Q1, Q2, Q3, and Q4, and as Historical Orders.
The advantage of using this approach is that when the data is grouped at the row level by its class, which in this example would be the date of the order, all orders for Q1 can be managed as a self contained unit, where as the orders for Q2 would reside in a different class. This can be achieved by using partitioning. Because partitions are completely transparent to the application, the data is physically separated but the application still locates all the orders.
Partitioning involves physically placing data according to a data value, and a frequently used technique is to partition information by date. Figure 5-1 illustrates a scenario where the orders for Q1, Q2, Q3, and Q4 are stored in individual partitions and the orders for previous years are stored in other partitions.
Figure 5-1 Allocating Data Classes to a Partition
Oracle offers several different partitioning methods. Range partitioning is one frequently used partitioning method for ILM. Interval and reference partitioning are also particularly suited for use in an ILM environment.
There are multiple benefits to partitioning data. Partitioning provides an easy way to distribute the data across appropriate storage devices depending on its usage, while still keeping the data online and stored on the most cost-effective device. Because partitioning is completely transparent to anyone accessing the data, no application changes are required, thus partitioning can be implemented at any time. When new partitions are required, they are simply added using the ADD PARTITION clause or they can be created automatically if interval partitioning is being used.
Among other benefits, each partition can have its own local index. When the optimizer uses partition pruning, queries only access the relevant partitions instead of all partitions, thus improving query response times.
An analysis of your data is likely to reveal that initially, it is accessed and updated on a very frequent basis. As the age of the data increases, its access frequency diminishes to almost negligible, if any. Most organizations find themselves in the situation where many users are accessing current data while very few users are accessing older data, as illustrated in Figure 5-2. Data is considered to be: active, less active, historical, or ready to be archived.
With so much data being held, during its lifetime the data should be moved to different physical locations. Depending on where the data is in its lifecycle, it must be located on the most appropriate storage device.
Because Oracle Database can take advantage of many different storage options, the next step is to establish the required storage tiers. Although you can create as many storage tiers as you require, a suggested starting point are the following tiers:
High Performance
The high performance storage tier is where all the important and frequently accessed data would be stored, such as the partition holding our Q1 orders. This would use smaller, faster disks on high performance storage devices.
Low Cost
The low cost storage tier is where the less frequently accessed data is stored, such as the partitions holding the orders for Q2, Q3, and Q4. This tier would be built using large capacity disks, such as those found in modular storage arrays or low costs ATA disks, which offer the maximum amount of inexpensive storage.
Online Archive
The online archive storage tier is where all the data that is seldom accessed or modified would be stored. The storage tier is likely to be extremely large and to store the maximum quantity of data. Various techniques can compress the data. This tier could be located in the database or it could be in another database, which serves as a central archive database for all information within the enterprise. Stored on low cost storage devices, such as ATA drives, the data would still be online and available, for a cost that is only slightly higher than storing this information on tape, without the disadvantages that come with archiving data to tape. If the Online Archive storage tier is identified as read-only, then it would be impossible to change the data and subsequent backups would not be required after the initial database backup.
Offline Archive (optional)
The offline archive storage tier is an optional tier because it is only used when there is a requirement to remove data from the database and store it in some other format, such as XML on a tape.
Figure 5-2 illustrates how data is used over a time interval. Using this information, it can be determined that to retain all this information, several storage tiers are required to hold all of the data, which also has the benefit of significantly reducing total storage costs.
After the storage tiers have been created, the data classes identified in "Step 1: Define the Data Classes" are physically implemented inside the database using partitions. This approach provides an easy way to distribute the data across the appropriate storage devices depending on its usage, while still keeping the data online and readily available, and stored on the most cost-effective device.
Note that you can also use Oracle Automatic Storage Management (Oracle ASM) to manage the data across the storage tiers. Oracle ASM is a high-performance, ease-of-management storage solution for Oracle Database files. Oracle ASM is a volume manager and provides a file system designed exclusively for use by the database. To use Oracle ASM, you allocate partitioned disks for Oracle Database with preferences for striping and mirroring. Oracle ASM manages the disk space, distributing the I/O load across all available resources to optimize performance while removing the need for manual I/O tuning. For example, you can increase the size of the disk for the database or move parts of the database to new devices without having to shut down the database.
After the storage tiers have been defined, the data classes (partitions) identified in Step 1 can be assigned to the appropriate storage tiers. This provides an easy way to distribute the data across the appropriate storage devices depending on its usage, keeping the data online and available, and stored on the most cost-effective device. This is illustrated in Figure 5-3. Using this approach, no application changes are required because the data is still visible.
One benefit of implementing an ILM strategy is the cost savings that can result from using multiple tiered storage. Assume that there is 3 TB of data to store, comprising of 200 GB on High Performance, 800 GB on Low Cost, and 2 TB on Online Archive. Assume the cost per GB is $72 on the High Performance tier, $14 on the Low Cost tier, and $7 on the Online Archive tier.
Table 5-1 illustrates the possible cost savings using tiered storage, rather than storing all data on one class of storage. As you can see, the cost savings can be quite significant and, if the data is suitable for database compression, then even further cost savings are possible.
Table 5-1 Cost Savings Using Tiered Storage
| Storage Tier | Single Tier using High Performance Disks | Multiple Storage Tiers | Multiple Tiers with Database Compression |
|---|---|---|---|
|
High Performance (200 GB) |
$14,400 |
$14,400 |
$14,400 |
|
Low Cost (800 GB) |
$57,600 |
$11,200 |
$11,200 |
|
Online Archive (2 TB) |
$144,000 |
$14,000 |
$5,600 |
|
$216,000 |
$39,600 |
$31,200 |
The next step is to ensure that only authorized users have access to the data and to specify how to move the data during its lifetime. As the data ages, there are multiple techniques that can migrate the data between the storage tiers.
The security of your data is another very important part of Information Lifecycle Management because the access rights to the data may change during its lifetime. In addition, there may be regulatory requirements that place exacting demands on how the data can be accessed.
The data in an Oracle Database can be secured using database features, such as:
Database Security
Views
Virtual Private Database
Virtual Private Database (VPD) defines a very fine-grained level of access to the database. Security policies determine which rows may be viewed and the columns that are visible. Multiple policies can be defined so that different users and applications see different views of the same data. For example, the majority of users could see the information for Q1, Q2, Q3, and Q4, while only authorized users would be able to view the historical data.
A security policy is defined at the database level and is transparently applied to all database users. The benefit of this approach is that it provides a secure and controlled environment for accessing the data, which cannot be overridden and can be implemented without requiring any application changes. In addition, read-only tablespaces can be defined which ensures that the data does not change.
During its lifetime, data must be moved. This may occur for the following reasons:
For performance, only a limited number of orders are held on high performance disks
Data is no longer frequently accessed and is using valuable high performance storage, and must be moved to a low-cost storage device
Legal requirements demand that the information is always available for a given time interval, and it must be held safely for the lowest possible cost
There are multiple ways that data can be physically moved in Oracle Database to take advantage of the different storage tiers. For example, if the data is partitioned, then a partition containing the orders for Q2 could be moved online from the high performance storage tier to the low cost storage tier. Because the data is being moved within the database, it can be physically moved, without affecting the applications that require it or causing disruption to regular users.
Sometimes individual data items, rather than a group of data, must be moved. For example, suppose data was classified according to a level of privacy and a report, which had been secret, is now to be made available to the public. If the classification changed from secret to public and the data was partitioned on its privacy classification, then the row would automatically move to the partition containing public data.
Whenever data is moved from its original source, it is very important to ensure that the process selected adheres to any regulatory requirements, such as, the data cannot be altered, is secure from unauthorized access, easily readable, and stored in an approved location.
The last step in an Information Lifecycle Management solution is the creation of policies for compliance. When data is decentralized and fragmented, compliance policies have to be defined and enforced in every data location, which could easily result in a compliance policy being overlooked. However, using Oracle Database to provide a central location for storing data means that it is very easy to enforce compliance policies because they are all managed and enforced from one central location.
When defining compliance policies, consider the following areas:
Data Retention
Immutability
Privacy
Auditing
Expiration
The retention policy describes how the data is to be retained, how long it must be kept, and what happens after data life. An example of a retention policy is a record must be stored in its original form, no modifications are allowed, it must be kept for seven years, and then it may be deleted. Using Oracle Database security, it is possible to ensure that data remains unchanged and that only authorized processes can remove the data at the appropriate time. Retention policies can also be defined through a lifecycle definition in the ILM Assistant.
Immutability is concerned with proving to an external party that data is complete and has not been modified. Cryptographic or digital signatures can be generated by Oracle Database and retained either inside or outside of the database, to show that data has not been altered.
Oracle Database provides several ways to ensure data privacy. Access to data can be strictly controlled with security policies defined using Virtual Private Database (VPD). In addition, individual columns can be encrypted so that anyone looking at the raw data cannot see its contents.
Oracle Database can track all access and changes to data. These auditing capabilities can be defined either at the table level or through fine-grained auditing, which specifies the criteria for when an audit record is generated. Auditing can be further enhanced using Audit Vault.
There usually comes a point during the lifecycle of the data when it is no longer being regularly accessed and is considered eligible for archiving. Traditionally, the data would have been removed from the database and stored on tape, because it can store vast quantities of information for a very low cost. Today, it is no longer necessary to archive that data to tape, instead it can remain in the database, or transferred to a central online archive database. All this information would be stored using low-cost storage devices whose cost per gigabyte is very close to that of tape.
There are multiple benefits to keeping all of the data in a database for archival purposes. The most important benefit is that the data always be instantly available. Therefore, time is not wasted locating the tapes where the data was archived and determining whether the tape is readable and still in a format that can be loaded into the database.
If the data has been archived for many years, then development time may also be needed to write a program to reload the data into the database from the tape archive. This could prove to be expensive and time consuming, especially if the data is extremely old. If the data is retained in the database, then this is not a problem, because it is online, and in the latest database format.
Holding the historical data in the database no longer impacts the time required to backup the database and the size of the backup. When RMAN is used to back up the database, it only includes in the backup the data that has changed. Because historical data is less likely to change, after the data has been backed up, it is not backed up again.
Another important factor to consider is how the data is to be physically removed from the database, especially if it is to be transferred from a production system to a central database archive. Oracle provides the capability to move this data rapidly between databases by using transportable tablespaces or partitions, which moves the data as a complete unit.
When it is time to remove data from the database, the fastest way is to remove a set of data. This is achieved by keeping the data in its own partition. The partition can be dropped, which is a very fast operation. However, if this approach is not possible because data relationships must be maintained, then a conventional SQL delete statement must be issued. You should not underestimate the time required to issue the delete statement.
If there is a requirement to remove data from the database and there is a possibility that the data may need to be returned to the database in the future, then consider removing the data in a database format such as a transportable tablespace, or use the XML capability of Oracle Database to extract the information in an open format.
Consider an online archive of your data into an Oracle database for the following reasons:
The cost of disk is approaching that of tape, eliminate the time to find the tape that contains the data and the cost of restoring that data
Data remains online when needed, faster access to meet business requirements
Data online means immediate access, so cannot be fined by regulatory body for failing to produce data
Use the current application to access the data, no need to waste resources to build a new application
Oracle ILM Assistant provides a graphical user interface (GUI) for managing your ILM environment. Figure 5-4 shows the first screen of the ILM Assistant, which lists the outstanding tasks that should be performed.
The ILM Assistant provides the ability to create lifecycle definitions, which are assigned to tables in the database. Using this lifecycle definition, the ILM Assistant advises when it is time to move, archive, or delete data, as shown by the calendar. It also illustrates the storage requirements and cost savings associated with moving the data.
The ILM Assistant can manage only partitioned tables. For nonpartitioned tables, the ILM Assistant generates a script to show how the table could be partitioned, and it also provides the capability to simulate partitioning on a table to view the actions that would arise if the table were partitioned.
The ILM Assistant does not execute any commands for the tasks it recommends to be performed, such as migrating data to different storage tiers. Instead, it generates a script of the commands that must be executed.
To assist with managing compliance issues, the ILM Assistant shows all Virtual Private Databases (VPD) and Fine-Grained Audit (FGA) policies that have been defined on tables under ILM control. In addition, both Database and FGA audit records can be viewed and digital signatures generated and compared.
Oracle ILM Assistant requires that Oracle Application Express is installed in the database where the tables to be managed by the ILM Assistant reside.
The ILM Assistant provides capability in the following areas:
The Lifecycle Setup area of the ILM Assistant consists of the following tasks that must be performed to prepare for managing your data:
If this is the first time that you have used the ILM Assistant, then it is here where you specify exactly how the data is to be managed by the ILM Assistant. The following steps must be completed before the ILM Assistant can give advice on data placement, as illustrated in Figure 5-5.
Define the logical storage tiers
Define the lifecycle definitions
Select tables to be managed by the lifecycle definitions
Figure 5-5 ILM Assistant: Specifying How Data is Managed
Other options available within setup include the ability to:
View partition simulation
View a lifecycle summary of mapped tables and their logical storage tiers and lifecycle definitions
View storage costs
Define policy notes
Customize the ILM Assistant with preferences
A logical storage tier is a name given to a logical group of storage devices; typically all disks of the same type are identified by that name. For example, the group called High Performance could refer to all the high performance disks. Any number of logical storage tiers may be defined and the devices are identified by the assigned tablespaces, which reside upon them.
The cost per GB value must be a value greater than zero. The value is used by the ILM Assistant to project storage costs when data is mapped to the tier. It is recommended that you enter a value that represents a reasonably accurate cost of storing data on the tier. This would include the physical purchase price of a device. However, you might also want to consider other associated costs, such as maintenance and running costs.
Each storage tier has a set of assigned tablespaces that are labeled as a read/write preferred tablespace, read-only preferred tablespace, or a secondary tablespace. If read/write data can be migrated onto the tier, then the read/write preferred tablespace is required. If the storage tier accepts read-only data, then a read-only preferred tablespace must also be identified.
In addition to the preferred tablespaces, one or more secondary tablespaces may be assigned to the tier. Secondary tablespaces are typically located in the same location as the read/write preferred tablespace for the storage tier.
Because the ILM Assistant only supports a single preferred tablespace, any read/write data that must reside on the tier would generate a migration event to move the data to the read/write preferred tablespace. To avoid unnecessary data migration events, the ILM Assistant allows existing data to remain on a secondary tablespace for the storage tier.
A lifecycle definition describes how data migrates across the logical storage tiers during its lifetime. It consists of one or more lifecycle stages that select a logical storage tier, data attributes such as compression and read only, and a duration for data residing on that lifecycle stage.
A lifecycle definition is valid if it contains at least one lifecycle stage. There must be a final stage, which is either user specified or automatically generated by the ILM Assistant upon completion of the lifecycle definition process. For the final stage you must specify what happens to data at lifecycle end.
A lifecycle definition consists of multiple stages that describe what happens to data during its lifetime. Lifecycle stages are initially created in reverse time order (that is, working backward in time from the current date). Every stage must have a unique name; an optional description can be supplied.
If the stage is not the final stage, then you must specify how long the data is to remain on this stage and any stage attributes such as whether the data should be compressed or set to read only. Note that it is only possible to specify a read only stage if a preferred read only tablespace has been defined for the logical storage tier for this stage.
The current stage represents the present, but can span any length of time. A lifecycle can only have one current stage. The final stage is required as it describes what happens when data reaches its end of life. A lifecycle can only have one final stage and it is automatically created if you do not create one. Possible actions are:
Purge the data
Archive the data off-line
Allow the data to remain on-line
Stages that store data on-line also permit several attributes to be defined that affect the data. The supported attributes are:
Compress
Compress and Read-Only
Read-Only
Each stage consists of the following information:
Stage Type
A stage is classified as a current stage, final stage, or unclassified.
Stage Name
Displays the user-supplied name of the stage.
Stage Description
Displays the user-supplied stage description.
Action
Displays the action performed when data maps to the stage. Possible actions are:
Remain Online
Archive Offline
Purge
Tier Name
Displays the storage tier associated with the stage. For a stage that purges data or moves data offline, a tier is not specified.
Attributes
Displays the optional data attributes that are applied to data when it maps to the stage. Possible values are:
Compress
Compress and Read-Only
Read-Only
Stage Duration
Displays the length of time the data can remain mapped to the stage.
Stage Start Date
Displays the actual calendar date for the beginning of the stage. The date is computed based on the adjacent stages and the user-specified fiscal start date.
Stage End Date
Displays the actual calendar date for the end of the stage. The date is computed based on the adjacent stages and the user-specified fiscal start date.
The Lifecycle Tables area identifies those tables that may be managed by the ILM Assistant, and it is here where these tables are mapped to a lifecycle definition, as illustrated in Figure 5-6. A database may contain many tables, only some of which you want to consider as candidates for ILM. A table is automatically eligible if it is range partitioned on a date column. When the table is associated with a lifecycle definition, the ILM Assistant can manage its data. For tables having no partitioning, storage cost savings and storage tier migration can be modeled using a simulated partitioning strategy.
Figure 5-6 ILM Assistant: Lifecycle Tables
If the table is not yet partitioned, then you are directed to a Partition Simulation page where you can set up a full simulation. Similar to setting up a managed table, a simulation can be previewed and accepted on this page. Upon returning from the simulation page, the table is now eligible for full lifecycle management in simulation mode.
The difference between a managed table and a simulated table is that a managed table contains actual partitions while a simulated table only contains simulate (not real) partitioning data. All reports and event detection work with both types of lifecycle tables. However, any table upon which partitioning is being simulated is only seen as being partitioned from within the ILM Assistant. All other tools continue to see it as a nonpartitioned table.
Though the lifecycle tables view shows all accessible tables, the ILM Assistant may not be able to manage every table. In those cases, the table is marked as ineligible and a link is provided to explain the exception. Some examples of ineligible tables are:
Tables having no date column
Tables partitioned on non-date columns
Tables partitioned using a partition type other than range
Tables containing a LONG column
Index-organized tables
The display for Lifecycle Tables can be customized to show managed, simulated, candidate, and ineligible tables, and includes the following information:
Table Owner
The Oracle schema that owns the table
Table Name
The table that may allow ILM management
Storage Size
The current estimated size of the table. The value is scaled according to the Size Metric as specified within the Filter Options.
Data Reads
The current sum of all logical and physical reads for the table.
Data Writes
The current sum of all physical writes for the table.
Lifecycle Definition
If the ILM Assistant is managing the table, then the required lifecycle definition is displayed here.
Lifecycle Status
Provides the current status of the table. This indicates whether the table is eligible, is managed, or is simulated. For tables that are ineligible, the status link provides an explanation regarding its incompatibility with the ILM Assistant.
Table Partitioning
Provides a status of the table partitioning. A table can have partitioning implemented, simulated, or none.
Cost Savings
When the ILM Assistant is managing a table, a total cost-savings value is computed and displayed here.
Partition Map
Indicates that the current table partitioning scheme is compatible with the lifecycle definition. Clicking on the icon displays a detailed report of the table partitions.
For installations having many tables, the ILM Assistant provides a table list caching system to prevent long page waits and possible browser timeouts. The table list is a snapshot of all user tables on the system that should be periodically refreshed to maintain consistency within the ILM Assistant. Typically, the table list should be refreshed when application tables have been added, changed, or removed outside of the ILM Assistant, or when up-to-date table statistics are desired.
By default, a table list refresh operation attempts to scan for every table defined in the database. For large application environments, this can take a long time to complete. Typically, ILM Assistant management of tables is limited to a small number of tables. To avoid refreshing the table list with the entire set of tables found in the database, filtering may be used to narrow the number of tables to be scanned. For example, if the user was only interested in managing tables in the SH schema, the Table Owner Filter can be set to SH. To estimate the time it may take to do refresh, click Estimate Refresh Statistics. This returns the projected number of tables that match the filters and the time it takes to process the data.
Purging unused entries in the cache cleans up a cache that contains any entries that are not currently managed by the ILM Assistant. It does not affect any of the tables that currently match the filters.
As a guideline, the ILM Assistant can refresh the table list at a rate of 300 to 350 tables per minute. The operation may be interrupted from the Lifecycle Tables screen. An interrupt stops the refresh operation as if it has reached the normal end of the table scan. Because of the nature of the process, an interrupt can take up to 30 seconds to stop the actual scan operation.
The Partition Map column in the Lifecycle Tables Report indicates whether all the partitions in the table fit inside a stage and do not overlap stages. The Mapping Status indicates the quality of the partition-to-stage relationship. A green check mark indicates the partition resides completely within the stage without violating date boundaries. A warning icon indicates some type of mismatch. Possible exceptions for the stage mapping are:
Misaligned partitions
A partition can be misaligned when it cannot fit into an entire stage. This can happen if the lifecycle stage duration is smaller than the smallest partition range. To resolve this misalignment, either choose a better lifecycle definition to manage the table or adjust the stage duration by editing the lifecycle definition.
Tablespace is not associated with a logical storage tier
This is very common for new ILM Assistant users. To perform cost analysis, the ILM Assistant must associate all referenced tablespaces with a tier. Typically, the easiest correction is to edit a logical storage tier and add the missing tablespace as a secondary tablespace.
The ILM Assistant provides a comprehensive storage cost and savings report associated with the managed or simulated table, as illustrated in Figure 5-7.
Figure 5-7 ILM Assistant: Partitioning for Simulated Tables
The report is divided into two main areas. The top portion of the report is a rollup showing the totals for the managed or simulated tables. For managed tables, there are two subsections that show data for a non-ILM environment using a single storage tier and an ILM managed, multitier environment. For simulated tables, a third section shows an ILM managed, multitier environment that includes the estimated effects of compression.
The bottom section of the storage costs page is the detail section that breaks up the cost areas by logical storage tier:
Single-Tier Size
Displays the total size of the entities. For a lifecycle-based report, the value represents the sum of all table sizes that are assigned the current lifecycle definition. For managed tables, the size is the actual size as indicated by the database storage statistics. For simulated tables, the size is the projected size as calculated by the user-specified number of rows and average row length.
Single-Tier Cost
Displays the single-tier cost, which is calculated by multiplying the single-tier size of the current entities by the cost of storing the data on the most expensive tier within the lifecycle definition.
Cost per GB
Displays the user-specified cost when setting up the storage tier. The value is used to calculate the storage costs for partitions that are assigned to the tier.
Multitier Size
Displays the total size of the entities that reside on that tier. For lifecycles, it represents all table partitions that are associated with the current tier. For a table, it represents the sum of all partitions that are associated with the tier. The size does not include any projected compression.
Multitier Cost
Displays the cost, which is calculated by multiplying the cost per gigabyte for the current tier by the space occupied by the entities. For lifecycles, it represents all table partitions that are associated with the current tier. For a table, it represents the sum of all partitions that are associated with the tier.
Multitier Savings
Displays the savings, which is computed by subtracting the multitier cost from the calculated cost of storing the same data using the single-tier approach.
Percent Savings
Displays the ratio of multitier savings to the single-tier cost for the same data.
Multitier Compressed Size
Displays the total size of the entities that reside on that tier. For lifecycles, it represents all table partitions that are associated with the current tier. For a table, it represents the sum of all partitions that are associated with the tier. The size includes projected compression based on the estimated compression factor assigned by the user.
This report item is only present when viewing simulated table data.
Multitier Compressed Cost
Displays the cost, which is calculated by multiplying the cost per gigabyte for the current tier by the space occupied by the entities. For lifecycles, it represents all table partitions that are associated with the current tier. For a table, it represents the sum of all partitions that are associated with the tier. The size includes projected compression based on the estimated compression factor assigned by the user.
This report item is only present when viewing simulated table data.
Multitier Compressed Savings
Displays the savings, which is computed by subtracting the multitier compressed cost from the calculated cost of storing the same data using the single-tier approach.
This report item is only present when viewing simulated table data.
Percent Savings
Displays the ratio of multitier compressed savings to the single-tier cost for the same data.
This report item is only present when viewing simulated table data.
Lifecycle Stages Compressed
When setting up lifecycle stages, the user has the option of requiring the partitions to be compressed when assigned to the stage. This value shows the number of stages assigned to the storage tier that have the compressed attribute set.
Partitions Compression
Displays the number of partitions on the storage tier that are currently compressed.
Implementing partitioning is likely to be a major task for any organization and the ILM Assistant enables you to model the impact before actually reorganizing the data. To achieve this modeling, the ILM Assistant requires the following information in simulation mode:
Lifecycle Definition
Select a lifecycle definition that is used to manage the simulated table. The simulated partitions are derived from the lifecycle stages defined in the lifecycle. The ILM Assistant determines the optimal date range based on the stage duration information supplied.
Partitioning Column
Select a suitable date column as the partitioning key. If the current table has only one date column, then the column automatically is selected and displayed in read-only form.
Partition Date Interval
Displays the optimal partition range interval based on the selected lifecycle definition. The ILM Assistant computes an interval that guarantees that all generated partitions properly align with the lifecycle stages.
Number of Rows
Provide the number of rows in the current table. The default value is retrieved from the current tables database statistics. If the default value is unavailable, or you want to project future growth, you may enter any value greater than zero.
Average Row Length
Provide the average row length for the table. The default value is retrieved from the current tables database statistics. If the statistics are not valid, then the ILM Assistant queries the table and calculate a maximum row size. If the default value is unsuitable, or you want to project future growth, then you may enter any value greater than zero.
Estimated Compression Factor
Provide a compression factor. The compression factor is used exclusively by the ILM Assistant to estimate storage costs and savings. The factor is purely an estimate, but can give you savings potential. A value of one indicates no compression is projected. A value greater than one indicates a reduction in space using the formula reduction = 1 / factor. The default value is calculated by sampling a small percentage of the table for compression potential.
An additional option after previewing the simulation is Migration Script generation, as illustrated in Figure 5-7. This functionality enables the user to create a script that can convert the existing nonpartitioned table to a partitioned counterpart. It should be noted that the script contains a simple create operation and a command to migrate the existing data; however, parts of the script have been commented out to prevent accidental operation. A conversion of a table to a partitioned table should be carefully planned.
Preferences control various aspects of the ILM Assistant's behavior and display of data (for example, the default date format for most entered values and reports, or the default number of rows to display). The following preferences can be set:
Compression sample block count
Compression sample percent
Date format (Long form)
Date format (Short form)
Demonstration Mode
Specifies a factor that amplifies the actual table sizes. A value of one effectively disables the mode because multiplying a number by one does not change the original value.
Language preference
Lifecycle table view filter
Specifies the default selection to view when visiting the Lifecycle Tables page. Values can be combined to indicate multiple types of tables. For example, 3 indicates that both managed and simulated tables are to be shown. Possible values are:
The default value is 7, which excludes ineligible tables.
Maximum report rows to display
Maximum viewable tables
Refresh rate for progress monitoring
Report column maximum display length
Start page for lifecycle setup
Possible values are:
Logical Storage Tiers
Lifecycle Definitions
Lifecycle Tables
Storage size metric
Specifies the default size metric to be used when viewing storage size values. Possible values are:
The value is case sensitive.
Lifecycle Management is concerned with the tasks that must be performed to move data to the correct place in the Information Lifecycle. Information is available on the following:
The Lifecycle Events Calendar shows the calendar of previous, current, and (optionally,) future lifecycle events that must be performed to place data at the appropriate place in the information lifecycle, as illustrated in Figure 5-5. You can use the Previous Month with Events button to navigate to previous months containing lifecycle events.
To identify which data must be moved, click the Scan for Events button which asks whether to scan for all events up to the current day, or into the future. Additionally, you may choose to evaluate all tables or selected tables. The ILM Assistant then compares the current location of data with where it should be stored in accordance with the lifecycle definition and recommend the appropriate movement. It also advises if data should be compressed or set to read only as defined by the lifecycle definition. All the recommendations made by the ILM Assistant are applied to partitions only.
The Lifecycle Events report shows details about data migration events and provides a way to generate scripts to perform their actions. You can select some or all of the displayed events by clicking the check boxes in the first column. You must select events to generate scripts or to dismiss events. To generate a script on the selected events, click the Generate Script button. To dismiss the selected events to make them permanently disappear, click the Dismiss Selected Events button.
The event summary shows the following pieces of information:
Recommended Action
Indicates the type of event that was detected by the scan operation. Possible event types are:
MOVE PARTITION
Indicates that a partition should be moved from its current logical storage tier to a new logical storage tier. The movement is achieved by moving the partition from one tablespace to another.
COMPRESSION
Indicates that the partition should have data compression enabled.
READ-ONLY
Indicates that the partition should be set to read-only.
PURGE
Indicates that the partition should be physically deleted.
Partition Name
Describes the affected partition.
Current Tier
Describes the current location of the partition.
Recommended Tier
Describes the target storage tier for move operations.
Cost Savings
Indicates the potential storage cost savings if the event action were to be implemented.
Table Owner and Name
Describes the partition table owner and name.
Event Date
Indicates the date on which the action should be performed. For events that should have been resolved, a single keyword Past is shown; for events in the future, a calendar date is displayed.
Event Details
Provides a link to event details. This area describes lifecycle details that affected the scan operation.
When a partition requires several logical operations such as move and compress, the ILM Assistant displays the operations as separate events. However, in the script, the operations may be combined into a single SQL DDL statement.
The ILM Assistant currently does not have any archive capability. Therefore, selecting archive events generates a script that identifies which partitions should now be archived and lists them as comments.
Any authorized user can invoke event scanning through the Lifecycle Events Calendar. Over time, tracking the scan activity can be quite difficult, so a history is made available.
The history report shows the following pieces of information:
Scan Date
Submitted by User
Lowest Event Date
Highest Event Date
Table Owner and Name
Number of Events
Lifecycle Status
The Compliance & Security area shows everything that can enforce security and help maintain compliance with the numerous world-wide regulations. It provides an area to view:
Current status summarizes the status of all the various Compliance & Security features that are available. For example, it advises how many Virtual Private Database (VPD) policies have been defined, when a digital signature was last generated, and when a comparison of digital signatures was last performed.
Some regulations stipulate that it must be shown that data has not changed since it was entered into the database. One technique that can prove that data has not been altered is to generate a digital signature.
Oracle Database provides the capability to generate a digital signature for a SQL result set. This can be generated inside the ILM Assistant and is achieved by creating a named SQL result set which includes the query to describe the collection of records. The digital signature is generated and is initially saved in a text file.
To show that the data records in a query have not been altered, a digital signature can be presented for a previously defined SQL query, and re-generated on today's data and the signatures compared, to show that the data has not changed since the digital signature was originally generated.
The Privacy & Security area enables you to view:
A summary of privacy and security definitions for each ILM table
Virtual Private Database (VPD) policies
Security views on tables managed by the ILM Assistant
Reports on the access privileges granted to users for tables managed by the ILM Assistant
By default, the Lifecycle Table Summary is shown and VPD policies and user access information are available by selecting the appropriate links.
The Lifecycle Table Summary provides an overview for each table regarding which features are being used in terms of VPD policies and table grants issued.
Using standard database privileges, it is possible to limit access to a table to certain users. However, such access allows users to read all information in that table. VPD Policies provide a finer level of control on who can access information. Using a VPD Policy, it is possible to write sophisticated functions, which define exactly which data is visible to a user.
For example, a policy could say that certain users can only view the last 12 months of data, while other users can view all of the data. Another policy could say that the only data visible is in the state where the office is located. Therefore, VPD Policies are an extremely powerful tool in controlling access to information. Only VPD policies that have been defined on tables that are being managed by the ILM Assistant are shown on the VPD Policies report.
Some regulations require that an audit trail be maintained of all access and changes to data. In Oracle Database, several types of auditing are available: database and fine-grained. They each create their own audit records, which can be viewed as one consolidated report in the ILM Assistant that can be filtered on several criteria.
Within the auditing area on the ILM Assistant, it is possible to:
View the Current Audit Status
Manage Fine-Grained Audit Policies
View Audit Records
Standard Auditing within the Oracle Database logs all types of access to a table. However, there may be instances when it is desirable to only audit an event when a certain condition is met. For example, the value of the transaction being altered is greater than $10,000. This type of auditing is possible using Fine-Grained Audit policies where an audit condition can be specified and an optional function can be called for more sophisticated processing.
It is possible within the ILM Assistant to view both database and fine-grained audit records for tables mapped to Lifecycle Definitions in the ILM Assistant. An icon represents the type of audit record: database (indicated by a disc) or FGA. Use the Filter condition to filter the audit records that are displayed and click the report heading to sort the data on that column.
By default, the ILM Assistant only displays audit records for the current day. To see audit records for previous days, you must use the filter options to specify a date range of records to display.
Policy notes provide textual documentation of your data management policies or anything to document about managing data during its lifetime. Policy notes are informational only; they do not affect the tasks performed by the ILM Assistant. They can be used as a central place to describe your policies, as reminders, and as a way to prove that your policies are documented. They can also be used to document SLA (Service Level Agreements) and to document the compliance rules that you are trying to enforce.
Example 5-1 illustrates how to manually create storage tiers and partition a table across those storage tiers and then setup a VPD policy on that database to restrict access to the online archive tier data.
Example 5-1 Manually implementing an ILM system
REM Setup the tablespaces for the data
REM These tablespaces would be placed on a High Performance Tier
CREATE SMALLFILE TABLESPACE q1_orders DATAFILE 'q1_orders'
SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE q2_orders DATAFILE 'q2_orders'
SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE q3_orders DATAFILE 'q3_orders'
SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE q4_orders DATAFILE 'q4_orders'
SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
REM These tablespaces would be placed on a Low Cost Tier
CREATE SMALLFILE TABLESPACE "2006_ORDERS" DATAFILE '2006_orders'
SIZE 5M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE "2005_ORDERS" DATAFILE '2005_orders'
SIZE 5M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
REM These tablespaces would be placed on the Online Archive Tier
CREATE SMALLFILE TABLESPACE "2004_ORDERS" DATAFILE '2004_orders'
SIZE 5M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE old_orders DATAFILE 'old_orders'
SIZE 15M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
REM Now create the Partitioned Table
CREATE TABLE allorders (
prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL,
channel_id NUMBER NOT NULL,
promo_id NUMBER NOT NULL,
quantity_sold NUMBER(10,2) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL)
--
-- table wide physical specs
--
PCTFREE 5 NOLOGGING
--
-- partitions
--
PARTITION BY RANGE (time_id)
( partition allorders_pre_2004 VALUES LESS THAN
(TO_DATE('2004-01-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE old_orders,
partition allorders_2004 VALUES LESS THAN
(TO_DATE('2005-01-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE "2004_ORDERS",
partition allorders_2005 VALUES LESS THAN
(TO_DATE('2006-01-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE "2005_ORDERS",
partition allorders_2006 VALUES LESS THAN
(TO_DATE('2007-01-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE "2006_ORDERS",
partition allorders_q1_2007 VALUES LESS THAN
(TO_DATE('2007-04-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE q1_orders,
partition allorders_q2_2007 VALUES LESS THAN
(TO_DATE('2007-07-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE q2_orders,
partition allorders_q3_2007 VALUES LESS THAN
(TO_DATE('2007-10-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE q3_orders,
partition allorders_q4_2007 VALUES LESS THAN
(TO_DATE('2008-01-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE q4_orders);
ALTER TABLE allorders ENABLE ROW MOVEMENT;
REM Here is another example using INTERVAL partitioning
REM These tablespaces would be placed on a High Performance Tier
CREATE SMALLFILE TABLESPACE cc_this_month DATAFILE 'cc_this_month'
SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE SMALLFILE TABLESPACE cc_prev_month DATAFILE 'cc_prev_month'
SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
REM These tablespaces would be placed on a Low Cost Tier
CREATE SMALLFILE TABLESPACE cc_prev_12mth DATAFILE 'cc_prev_12'
SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
REM These tablespaces would be placed on the Online Archive Tier
CREATE SMALLFILE TABLESPACE cc_old_tran DATAFILE 'cc_old_tran'
SIZE 2M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
REM Credit Card Transactions where new partitions automatically are placed on the high performance tier
CREATE TABLE cc_tran (
cc_no VARCHAR2(16) NOT NULL,
tran_dt DATE NOT NULL,
entry_dt DATE NOT NULL,
ref_no NUMBER NOT NULL,
description VARCHAR2(30) NOT NULL,
tran_amt NUMBER(10,2) NOT NULL)
--
-- table wide physical specs
--
PCTFREE 5 NOLOGGING
--
-- partitions
--
PARTITION BY RANGE (tran_dt)
INTERVAL (NUMTOYMINTERVAL(1,'month') ) STORE IN (cc_this_month )
( partition very_old_cc_trans VALUES LESS THAN
(TO_DATE('1999-07-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE cc_old_tran ,
partition old_cc_trans VALUES LESS THAN
(TO_DATE('2006-07-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE cc_old_tran ,
partition last_12_mths VALUES LESS THAN
(TO_DATE('2007-06-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE cc_prev_12mth,
partition recent_cc_trans VALUES LESS THAN
(TO_DATE('2007-07-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE cc_prev_month,
partition new_cc_tran VALUES LESS THAN
(TO_DATE('2007-08-01 00:00:00'
,'SYYYY-MM-DD HH24:MI:SS'
,'NLS_CALENDAR=GREGORIAN'
)) TABLESPACE cc_this_month);
REM Create a Security Policy to allow user SH to see all credit card data,
REM PM only sees this years data,
REM and all other uses cannot see the credit card data
CREATE OR REPLACE FUNCTION ilm_seehist
(oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS con VARCHAR2 (200);
BEGIN
IF SYS_CONTEXT('USERENV','CLIENT_INFO') = 'SH'
THEN -- sees all data
con:= '1=1';
ELSIF SYS_CONTEXT('USERENV','CLIENT_INFO') = 'PM'
THEN -- sees only data for 2007
con := 'time_id > ''31-Dec-2006''';
ELSE
-- others nothing
con:= '1=2';
END IF;
RETURN (con);
END ilm_seehist;
/
REM Then the policy is added with the DBMS_RLS package as follows:
BEGIN
DBMS_RLS.ADD_POLICY ( object_schema=>'SYSTEM'
, object_name=>'cc_tran'
, policy_name=>'ilm_view_history_data'
, function_schema=>'SYSTEM'
, policy_function=>'ilm_seehist'
, sec_relevant_cols=>'tran_dt'
);
END;
/
|
See Also:
|
x_}HF7 |(%x݂5U- WYTy W`jXD[LX"x>hX-(^u=yLLg9F"ٝHD*4.)\"A#NnE5ĎUF4@ W]NgJ%dy4Y&~Wo:P:~Ws_Z^B
sg
KZ
Ԝ(Fhi2'x hC_j!Z.נI1iG(hZ[o
KԐj2,NfIT+R[,Qd!u;"!eީDJ],EnD&hڛĔ6̯TytipY;ÐJ\DGXÿo^쩮v .v鰔%zsU*4Lq\Hsv
4B?H곁)JS̊bRQ,"Bv\Kfslk6gLwOdP\& m&Y*s
+0VZ'^ʼr9!ρL9,w 'yxx\~r/0 'z}3;XdyHdB- tLa8sL- % r+ ǃpB#'~t `:8$| :xv-8`t1 FP g"P9A\',yxA0~ 49b
C>1csޢs<|:r; m
x1Y0/<^-ZȀ !)G>0%C= qp O7 h% #.挈:]eiS )H(. &`Q 0i vQ_`Rz1>`.rh?0G 'E9hQ=#1U43Qރ$'G_
A-5Dꉑ,BpVf&9]7@41;8d,hb!
jK(aBx
Oȃ
dC! ȴጠSQ w:* neA=slo8 IBY$bv xPmn3SHV0 D`VG=ƮUI#<7OBUKR`O#ohnPw0 U #r¢csh8y"yܹ
zy`ȖDbY2:`{ b` 8A`< px@ l-cmxJD s* Ռ| P8Cbwlv+nxPT v0Y0Y*/ m #Y>=lcJ|y} h5zdX bKjE4 `JZ˿L
~X~z6Wt
px(QݤlȀ 8,6y p oPkV MvV fwPeMs5H`
Aksx9 Ix`A 2l! "BcȐh@!
gޡ0OGw 'h(h{" *'[ BCV3@"~yl:_L85` X1 h5U)@%[X
l}4\N aLDCg lS}17*DhxVфx + p(B\23t8]x> ō BTzYt &t$π{IX ; 3 z]/4oM# ӛw7 ȞB3ZK Wo 6$@u$Y#p$W` 2 "0Ð;-k@v4# - BV UY" -t
UQp(夘 "{UPMF ŖPfk o9'!չ'}>
:z!y~Nu&(_ &Aʐ"9A|z)*z***)+z++:ѥHsN`H!:,J;-mHL1* Q كYā
ӓ A
]P{//& Q Y .lH% @p M 9lB%|2)l @dcU
gφ 2M;4ԫ NYj(G gU]@F tDu}7y7% d-DZQ~ROl ޝ{9PQIHMr`Ā*>=8>#2zC?E ?<{IB)< G]<[}DS:D"1x=O竿>??_?_? v 2Wl #(ATo r lH?(^p$|K[>Otg x`3a2C:L qD+эJ`N a HIqR}+
IcRA:i R Ji ٩J
Δ>-pS&u uLӦ.uLeUaT5$ӪOӱ42E7@ @ Bl{Շ_Y{Ak`ؾ"9]LQ .t!GG An "&,#>{[6G iA+# |zh`vE!4D#e-#lE$b4Z=tJ¶g큈_ù?m{ ,Dm};[ġ n%4JJ4"^X1@l{} 9J$+(Ќ@~p ƪ^3 <1 0j3S=$BDZ#k6جQj Z\8 X :W &A {Ba)5 ˎʞ
7-C꒕ S 2\ (6Du@Fԗ D]RB6 ߜ%ĄPDp xU^
XZ(xs9rJ!NsC`CM :C,Fo}4Ĥ9oA@T,nyGsoAθ
& Շ`2` PEC*C# P@
N {a@ {'!m bw\, ( <( 6hn( O j X,`%e9rC` 8c -1 (jhm[46j' X!@BވCW$yx!!C!栠@/TM
#Ae
p$6D!$ ⮊ TnK3ucX L2EP`/F}0 #[ ( D#Ϫc:~ -AE`LShX B@[{{*1
Eb(@Iڒ.{MG ;
3PVZD㔧l 0J@@K{, >UgxFg2~j6 ˀq , P4\>C% x_q\y Du;[흖Cu;Ƞ
T C`obP; ӌefeBrD< ^UGu`;LY N fTBaCL" v̻9/Aā C C@t8|2' N;8y/{ P,28/C 'O58hszeS7C3|v05A'1
|703 k'T<ۀ>`}Eo3
A7X$vK6$wLxG> "vC76C"BI( ٠Ёೱ U >P@$ -v[7a7v| (BxB8 B ;BxdP@>BLMT/
q øz,7v y <8 ~ AC.Ss?A\۾ P{47Ǯ '@tM Rd(Yɖ T\tс 9BaH#I4yeJ&_XYy2Q 1(z 9<~8kf7\D*1Q,8 ,09b36/q{\a X;F%lYȅ%=c$3XKl[dGS_\c,8EVp/!6Ȣj2e8;T@3e>EE|>\-}k AV"xLN"E0bq
_ܢ@BP!
TƈE&~]H;x f@%
B<O!<~!5B};RVG PM
;QY20ŁH%0Cb6l
ApB 1gAqy۰)}ղ>X4K]r
!>x$B lQb IO} 4FҘ(@#-/D /)Rd`ORx$!Kh)4eEMDAI+HG W'
}!p_zRo8ۿw x@n2lb3fr,B9_|9e7q(~"3 o;,9s9U9'b" 2,q {~L*5\Ѷ8 mMت^ 3'픺 K5ɍA]|ʬ T*,
4WkemN >ev (|5{9k >4{Ш)֧I0UM 6
cfg[53ZP+\n.4DJ yF
9+iҖNX/"d;=,ԧ:isC' 3A$7͉}{~#Ū rƻ1g{6Aj|ڋ=I,A H>Ӵ 0aT([@@P'Ȅ3gp