Release 2 (8.1.6)
Part Number A76956-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Administrator's Guide Release 2 (8.1.6) Part Number A76956-01 |
|
This chapter describes various aspects of managing partitioned tables and indexes, and includes the following sections:
Today's enterprises frequently run mission critical databases containing upwards of several hundred gigabytes and, in many cases, 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.
One way to meet VLDB demands is to create and use partitioned tables and indexes. Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Indexes, may be partitioned in similar fashion. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.
If you are using parallel execution, partitions provide another means of parallelization. Operations on partitioned tables and indexes are performed in parallel by assigning different parallel execution servers to different partitions of the table or index.
Partitions and subpartitions of a table or index all share the same logical attributes. For example, all partitions (or subpartitions) in a table share the same column and constraint definitions, and all partitions (or subpartitions) of an index share the same index options. They can, however, have different physical attributes (such as TABLESPACE).
Although you are not required to keep each table or index partition (or subpartition) in a separate tablespace, it is to your advantage to do so. Storing partitions in separate tablespaces enables you to:
Partitioning is transparent to existing applications and standard DML statements run against partitioned tables. However, an application can be programmed to take advantage of partitioning by using partition-extended table or index names in DML.
|
See Also:
Detailed information on the concepts of partitioning is contained in Oracle8i Concepts. Before attempting to create a partitioned table or index, or perform maintenance operations on any partitioned table, it is recommended that you review that information. You can find information on parallel execution in Oracle8i Concepts and Oracle8i Designing and Tuning for Performance. |
There are three partitioning methods offered by Oracle:
Indexes, as well as tables, can be partitioned. A global index can only be partitioned by range, but it may be defined on any type of partitioned, or nonpartitioned, table. It usually requires more maintenance than a local index.
A local index is constructed so that it reflects the structure of the underlying table. It is equipartitioned with the underlying table, meaning that it is partitioned on the same columns as the underlying table, creates the same number of partitions or subpartitions, and gives them the same partition bounds as corresponding partitions of the underlying table. For local indexes, index partitioning is maintained automatically when partitions are affected by maintenance activity. This ensures that the index remains equipartitioned with the underlying table.
Oracle's partitioning methods are introduced in the following sections:
Use range partitioning to map rows to partitions based on ranges of column values. This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example months of the year. Performance is best when the data evenly distributes across the range. If partitioning by range causes partitions to vary dramatically in size because of unequal distribution, you may want to consider one of the other methods of partitioning.
When creating range partitions, you must specify:
The example below creates a table of four partitions, one for each quarter's sales. The columns SALE_YEAR, SALE_MONTH, and SALE_DAY are the partitioning columns, while their values constitute a specific row's partitioning key. The VALUES LESS THAN clause determines the partition bound: rows with partitioning key values that compare less than the ordered list of values specified by the clause will be stored in the partition. Each partition is given a name (SALES_Q1, SALES_q2,...), and each partition is contained in a separate tablespace (TSA, TSB,...).
CREATE TABLE sales ( invoice_no NUMBER, sale_year INT NOT NULL, sale_month INT NOT NULL, sale_day INT NOT NULL ) PARTITION BY RANGE (sale_year, sale_month, sale_day) ( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01) TABLESPACE tsa, PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01) TABLESPACE tsb, PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01) TABLESPACE tsc, PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01) TABLESPACE tsd );
A row with SALE_YEAR=1999, SALE_MONTH=8, and SALE_DAY=1 has a partitioning key of (1999, 8, 1) and would be stored in partition SALES_Q3.
Each partition of a range-partitioned table is stored in a separate segment.
More specific information on creating range-partitioned tables is contained in the section "Creating Range Partitions".
After you have created a range-partitioned table, you can use the ALTER TABLE statement to add additional partitions, merge partitions, load data (exchange partition), or perform other maintenance operations. These maintenance operations are listed in Table 15-1. The section "Maintaining Partitions" describes and presents examples of these operations.
You can create nonpartitioned global indexes, range-partitioned global indexes, and local indexes on range-partitioned tables, and perform maintenance on them as specified in Table 15-2. See "Creating Partitions" and "Maintaining Partitions" for specific information on creating and maintaining partitioned indexes.
|
Note: If your enterprise has or will have databases using different character sets, use caution when partitioning on character columns, because the sort sequence of characters is not identical in all character sets. For more information, see Oracle8i National Language Support Guide. |
Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key. Creating and using hash partitions gives you a highly tunable method of data placement, because you can influence availability and performance by spreading these evenly sized partitions across I/O devices (striping).
To create hash partitions you specify the following:
The following example creates a hash-partitioned table. The partitioning column is ID, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (GEAR1, GEAR2,...).
CREATE TABLE scubagear (id NUMBER, name VARCHAR2 (60)) PARTITION BY HASH (id) PARTITIONS 4 STORE IN (gear1, gear2, gear3, gear4);
Each partition of a hash-partitioned table is stored in a separate segment.
More detailed information on creating hash-partitioned tables is contained in the section "Creating Hash Partitions".
The ALTER TABLE statement can be used to perform maintenance operations on hash-partitioned tables. Most range partition maintenance operations are supported for hash partitions, except for the following:
Additionally, there are two maintenance operations specifically for partitions created using the hash partitioning method.
Maintenance operations for hash-partitioned tables are listed in Table 15-1 and discussed in "Maintaining Partitions".
You can create nonpartitioned global indexes, range-partitioned global indexes, and local indexes on hash-partitioned tables. For more information about indexing see "Creating Partitions" and "Maintaining Partitions".
Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.
When creating composite partitions, you specify the following:
The following statement creates a composite-partitioned table. In this example, three range partitions are created, each containing 8 subpartitions. The subpartitions are not named so system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (TS1,...,TS4).
CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER) PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname) SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (MAXVALUE));
Each subpartition of a composite-partitioned table is stored its own segment. The partitions of a composite-partitioned table are logical structures only as their data is stored in the segments of their subpartitions. As with partitions, these subpartitions share the same logical attributes. Unlike range partitions in a range partitioned table, the subpartitions cannot have different physical attributes from the owning partition, although they are not required to reside in the same tablespace.
More specific information on creating composite-partitioned tables is contained in the section "Creating Composite Partitions and Subpartitions".
The ALTER TABLE statement can be used to perform maintenance operations on composite-partitioned tables. You can perform all range partition maintenance operations on a composite partition of a table. You can perform the same maintenance operations on the hash subpartitions as on the hash partitions of a hash-partitioned table. Maintenance operations for composite-partitioned tables are listed in Table 15-1 and discussed in "Maintaining Partitions".
You can create nonpartitioned global indexes, range-partitioned global indexes, and local indexes on composite-partitioned tables. For more information about indexing see "Creating Partitions" below and "Maintaining Partitions".
This section presents details and examples of creating partitions for the different types of partitioned tables and indexes. Creating a partitioned table or index is very similar to creating a regular table or index, but you include a partitioning clause. The partitioning clause, and subclauses, that you include depend upon the type of partitioning you are trying to achieve.
When you create (or alter) a partitioned table, a row movement clause, either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT can be specified. This clause either enables or disables the migration of a row to a new partition if its key is updated. The default is disable.
|
See Also:
Information on the exact syntax of the partitioning clauses for creating and altering partitioned tables and indexes, any restrictions on their use, and specific privileges required for creating and altering tables is contained in the Oracle8i SQL Reference. You can partition tables containing columns with LOBs, objects, varrays, and nested tables. For more information about creating such tables, refer to the following books: |
The PARTITION BY RANGE clause of the CREATE TABLE statement identifies that the table is to be range-partitioned. The PARTITION clauses identify the individual partition ranges, and optional subclauses of a PARTITION clause can specify physical and other attributes specific to a partition's segment. If not overridden at the partition level, partitions will inherit the attributes of their underlying table.
In this example, the example presented earlier for a range-partitioned table is made more complex. Storage parameters and a LOGGING attribute are specified at the table level. These will replace the corresponding defaults inherited from the tablespace level for the table itself, and will be inherited by the range partitions. However, since there was little business in the first quarter, the storage attributes for partition SALES_Q1 are made smaller. The ENABLE ROW MOVEMENT clause is specified to allow the migration of a row to a new partition if an update to a key value is made that would place the row in a different partition.
CREATE TABLE sales ( invoice_no NUMBER, sale_year INT NOT NULL, sale_month INT NOT NULL, sale_day INT NOT NULL ) STORAGE (INITIAL 100K NEXT 50K) LOGGING PARTITION BY RANGE ( sale_year, sale_month, sale_day) ( PARTITION sales_q1 VALUES LESS THAN ( 1999, 04, 01 ) TABLESPACE tsa STORAGE (INITIAL 20K, NEXT 10K), PARTITION sales_q2 VALUES LESS THAN ( 1999, 07, 01 ) TABLESPACE tsb, PARTITION sales_q3 VALUES LESS THAN ( 1999, 10, 01 ) TABLESPACE tsc, PARTITION sales q4 VALUES LESS THAN ( 2000, 01, 01 ) TABLESPACE tsd) ENABLE ROW MOVEMENT;
The rules for creating range-partitioned global indexes are similar to those for creating range-partitioned tables. The following is an example of creating a range-partitioned global index by SALES_MONTH on the above table. Each index partition is named but is stored in the default tablespace for the index.
CREATE INDEX month_ix ON sales(sales_month) GLOBAL PARTITION BY RANGE(sales_month) (PARTITION pm1_ix VALUES LESS THAN (2) PARTITION pm2_ix VALUES LESS THAN (3) PARTITION pm3_ix VALUES LESS THAN (4) PARTITION pm4_ix VALUES LESS THAN (5) PARTITION pm5_ix VALUES LESS THAN (6) PARTITION pm6_ix VALUES LESS THAN (7) PARTITION pm7_ix VALUES LESS THAN (8) PARTITION pm8_ix VALUES LESS THAN (9) PARTITION pm9_ix VALUES LESS THAN (10) PARTITION pm10_ix VALUES LESS THAN (11) PARTITION pm11_ix VALUES LESS THAN (12) PARTITION pm12_ix VALUES LESS THAN (MAXVALUE));
You can partition index-organized tables, and their secondary indexes, but only by the range method. In the following example, a range-partitioned index-organized table SALES is created. The INCLUDING clause specifies all columns after WEEK_NO are stored in an overflow segment; there is one overflow segment for each partition, all stored in the same tablespace (OVERFLOW_HERE). Optionally, OVERFLOW TABLESPACE could be specified at the individual partition level, in which case some or all of the overflow segments could have separate TABLESPACE attributes.
CREATE TABLE sales(acct_no NUMBER(5), acct_name CHAR(30), amount_of_sale NUMBER(6), week_no INTEGER, sale_details VARCHAR2(1000), PRIMARY KEY (acct_no, acct_name, week_no)) ORGANIZATION INDEX INCLUDING week_no OVERFLOW TABLESPACE overflow_here PARTITION BY RANGE (week_no) (PARTITION VALUES LESS THAN (5) TABLESPACE ts1, PARTITION VALUES LESS THAN (9) TABLESPACE ts2, ... PARTITION VALUES LESS THAN (MAXVALUE) TABLESPACE ts13);
The PARTITION BY HASH clause of the CREATE TABLE statement identifies that the table is to be hash-partitioned. The PARTITIONS clause can then be used to specify the number of partitions to create, and optionally, the tablespaces to store them in. Alternatively, you can use PARTITION clauses to name the individual partitions and their tablespaces.
The only attribute you can specify for hash partitions is TABLESPACE. All of the hash partitions of a table must share the same segment attributes (except TABLESPACE), which are inherited from the table level.
The following examples illustrate two methods of creating a hash-partitioned table named DEPT. In the first example the number of partitions is specified, but system generated names are assigned to them and they are stored in the default tablespace of the table.
CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32)) PARTITION BY HASH(deptno) PARTITIONS 16;
In this second example, names of individual partitions, and tablespaces in which they will reside, are specified. The initial extent size for each hash partition (segment) is also explicitly stated at the table level, and all partitions will inherit this attribute.
CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32)) STORAGE (INITIAL 10K) PARTITION BY HASH(deptno) (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2, PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);
If you create a local index for the above table, Oracle will construct the index so that it is equipartitioned with the underlying table and ensure that it is maintained automatically when maintenance operations are performed on the underlying table. The following is an example of creating a local index on the table DEPT.
CREATE INDEX locd_dept_ix ON dept(deptno) LOCAL
You can optionally name the hash partitions and tablespaces into which the local index partitions will be stored, but if you do not do so, Oracle will use the name of the corresponding base partition as the index partition name, and store the index partition in the same tablespace as the table partition.
To create a composite-partitioned table, you start by using the PARTITION BY RANGE clause of a CREATE TABLE statement. Next, you specify a SUBPARTITION BY HASH clause that follows similar syntax and rules as the PARTITION BY HASH statement. The individual PARTITION and SUBPARTITION or SUBPARTITIONS clauses follow.
Attributes specified for a (range) partition apply to all subpartitions of that partition. You can specify different attributes for each (range) partition, and a STORE IN clause can be specified at the partition level if the list of tablespaces across which that partition's subpartitions should be spread is different from those of other partitions. All of this is illustrated in the following example.
CREATE TABLE emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER) PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname) SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7) (PARTITION p1 VALUES LESS THAN (1000) PCTFREE 40, PARTITION p2 VALUES LESS THAN (2000) STORE IN (ts2, ts4, ts6, ts8), PARTITION p3 VALUES LESS THAN (MAXVALUE) (SUBPARTITION p3_s1 TABLESPACE ts4, SUBPARTITION p3_s2 TABLESPACE ts5));
The following statement creates a local index on the EMP table where the index segments will be spread across tablespaces TS7, TS8, and TS9.
CREATE INDEX emp_ix ON emp(deptno) LOCAL STORE IN (ts7, ts8, ts9);
This local index will be equipartitioned with the base table as follows:
This section describes how to perform partition and subpartition maintenance operations for both tables and indexes.
Table Table 15-1 lists the maintenance operations that can be performed on table partitions (or subpartitions) and, for each type of partitioning, lists the specific clause of the ALTER TABLE statement that is used to perform that maintenance operation.
Table 15-2 lists the maintenance operations that can be performed on index partitions, and indicates on which type of index (global or local) they can be performed. Global indexes do not reflect the structure of the underlying table, and if partitioned, they can only be partitioned by range. Range-partitioned indexes share some, but not all, of the partition maintenance operations that can be performed on range-partitioned tables.
Because local index partitioning is maintained automatically when table partitions and subpartitions are affected by maintenance activity, partition maintenance on local indexes is less necessary and there are fewer options.
Additionally, you can use the SQL*Loader, IMPORT, and EXPORT Utilities to load or unload data stored in partitioned tables. These utilities are all partition and subpartition aware.
This section describes how to add new partitions to a partitioned table and explains why partitions cannot be specifically added to global partitioned or local indexes.
You can use the ALTER TABLE...ADD PARTITION statement to add a new partition to the "high" end (the point after the last existing partition). If you want to add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.
For example, a DBA has a table, SALES, which contains data for the current month in addition to the previous 12 months. On January 1, 1999, the DBA adds a partition for January, which is stored in tablespace TSX.
ALTER TABLE sales ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' ) TABLESPACE tsx;
When you add a partition to a hash-partitioned table, Oracle populates the new partition with rows rehashed from other partitions of the table as determined by the hash function.
The following statements show two ways of adding a hash partition to table SCUBAGEAR. Choosing the first statement would add a new hash partition whose partition name will be system generated, and it will be placed in the table's 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;
If the table has a local index, all local index partitions are marked UNUSABLE and must be rebuilt. Any global index, or all partitions of a partitioned global index will also be marked UNUSABLE.
Partitions may be added at both the range partition level and the hash subpartition level.
You add a new range partition in similar fashion as described previously in "Adding a Partition to a Range-Partitioned Table", but you can specify a SUBPARTITIONS clause that allows you to add a specified number of subpartitions, or a SUBPARTITION clause for naming specific subpartitions. If no SUBPARTITIONS or SUBPARTITION clause is specified, the partition inherits table level defaults for subpartitions.
This example adds a range partition Q1_2000 to table SALES, which will be populated with data for the first quarter of the year 2000. There will be eight subpartitions stored in tablespace TBS5.
ALTER TABLE sales ADD PARTITION q1_2000 VALUES LESS THAN (2000, 04, 01) 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 composite-partitioned table. The newly added subpartition is populated with rows rehashed from other subpartitions of the same partition as determined by the hash function. Any global index, and local index subpartitions corresponding to the added and rehashed subpartitions must be rebuilt.
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;
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. Since Oracle assigns names and default physical storage attributes to the new index partitions, you may want to rename or alter them after the ADD operation is complete.
You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you want 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 the number of subpartitions in a composite-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 RDBMS, and is dropped after its contents have been redistributed.
Any local index partition corresponding to the selected partition is also dropped. Local index partitions corresponding to the one or more absorbing partitions are marked UNUSABLE, and must be rebuilt. Any global index is marked unusable.
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. Basically, this operation is the inverse of the MODIFY PARTITION...ADD SUBPARTITION clause discussed earlier.
ALTER TABLE diving MODIFY PARTITION us_locations COALESCE SUBPARTITION;
You may drop partitions from range or composite-partitioned tables. For hash-partitioned tables, or hash subpartitions of composite-partitioned tables, you must perform a coalesce operation instead.
You use the ALTER TABLE...DROP PARTITION statement to drop a table partition from either a range or composite partitioned table. If you want to preserve the data in the partition, you should merge the data into an adjacent partition instead.
If there are local indexes defined for the table, this statement also drops the matching partition or subpartitions from the local index. Any global nonpartitioned indexes on the table will be marked UNUSABLE, and all partitions of any global partitioned indexes will be marked UNUSABLE, unless the partition being dropped or its subpartitions are empty.
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, use either of the following methods to drop the table partition.
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 need rebuilding. Further, it is not possible to rebuild all partitions of an index in one statement; you must write 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 nov99_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.
For example, if you want to drop the first partition, which has a partition bound of 10000 you can issue the following statements:
DELETE FROM sales WHERE TRANSID < 10000; 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.
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.
ALTER TABLE sales DISABLE CONSTRAINT dname_sales1; ALTER TABLE sales DROP PARTITTION dec98; ALTER TABLE sales ENABLE CONSTRAINT dname_sales1;
This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table.
DELETE FROM sales WHERE TRANSID < 10000; 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 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, you can explicitly drop it by issuing the ALTER INDEX...DROP PARTITION statement. But, if a global index partition contains data, 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;
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-partitioned table, or convert the partition of the composite partitioned table into a hash-partitioned table.
Exchanging table partitions is most useful when you have an application using nonpartitioned tables which you want to convert to partitions of a partitioned table. For example, you may already have partition views that you want to migrate into partitioned tables. A scenario for converting a partitioned view is presented in "Converting a Partition View into a Partitioned Table".
Exchanging partitions also facilitates high-speed data loading when used with transportable tablespaces. This topic is discussed in "Using Transportable Tablespaces".
When you exchange partitions, logging attributes are preserved, but you can optionally specify if local indexes are also to be exchanged, and if rows are to be validated for proper mapping.
To exchange a partition of a range or hash-partitioned table with a nonpartitioned table, or the reverse, you use the ALTER TABLE...EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table STOCKS could be either range or hash partitioned.
ALTER TABLE stocks EXCHANGE PARTITION p3 WITH stock_table_3;
For this operation, you again use the ALTER TABLE...EXCHANGE PARTITION statement, but this time you are exchanging a whole hash-partitioned table, with all of its partitions, with a composite-partitioned table's range partition and all of its hash subpartitions. This is illustrated in the following example.
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 composite partitioned table as shown:
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 is the same as the subpartitioning key in table T2.
To migrate the data in T1 to T2, and validate the rows, use the following statement:
ALTER TABLE t1 EXCHANGE PARTITION p1 WITH TABLE t2 WITH VALIDATION;
Use the ALTER TABLE...EXCHANGE SUBPARTITION statement to convert a hash subpartition of a composite-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 indexes partitions are exchanged with corresponding indexes on Q3_1999.
ALTER TABLE sales EXCHANGE SUBPARTITIONS q3_1999_s1 WITH TABLE q3_1999 INCLUDING INDEXES;
You can use the ALTER TABLE...MERGE PARTITIONS statement to merge the contents of two adjacent range partitions into one partition. The resulting partition inherits the higher upper bound of the two merged partitions. The two original partitions are dropped, as are any corresponding local indexes. Any global nonpartitioned indexes on the table will be marked UNUSABLE, and all partitions of any global partitioned indexes will be marked UNUSABLE, if the partitions being merged are not empty. Also, unless the involved partitions or subpartitions are empty, Oracle marks UNUSABLE all resulting corresponding local index partitions or subpartitions.
You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.
You might want to merge partitions so as 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.
The following scripts create an example of merging range partitions.
First, create a partitioned table and create local indexes.
-- Create a 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-aug-1998','dd-mon-yyyy')) TABLESPACE quarter_one, PARTITION quarter_two VALUES LESS THAN ( TO_DATE('01-sep-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-nov-1998','dd-mon-yyyy')) TABLESPACE quarter_four ) / -- -- Create local PREFIXED index on Four_Seasons -- Prefixed because the leftmost columns of the index match the -- Partition 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 partitions.
-- -- Merge the first two partitions -- ALTER TABLE four_seasons MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two /
Then, 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 will correct this. -- ALTER TABLE four_seasons MODIFY PARTITION quarter_two REBUILD UNUSABLE LOCAL INDEXES /
When you merge range composite partitions, the subpartitions will be rehashed into either the number of subpartitions specified in a SUBPARTITIONS or SUBPARTITION clause, or, if no such clause is included, table-level defaults will be used.
ALTER TABLE all_seasons MERGE PARTITIONS quarter_1, quarter_2 INTO PARTITION quarter_2 SUBPARTITIONS 8;
You can modify the default attributes of a partition or subpartition of a table or index. When you modify default attributes, the new attributes will affect only future partitions that are created. The default values can still be specifically overridden when creating a new partition.
You modify the default attributes that will be inherited for range or hash partitions using the MODIFY DEFAULT ATTRIBUTES clause of ALTER TABLE. The following example changes the default value of PCTFREE in table EMP for any new partitions that are created.
ALTER TABLE emp MODIFY DEFAULT ATTRIBUTES PCTFREE 25;
For hash-partitioned tables, only the TABLESPACE attribute can be modified.
To modify the default attributes used for creating the subpartitions of a range partition in a composite-partitioned table, use the ALTER TABLE...MODIFY DEFAULT ATTRIBUTES FOR PARTITION. This statement modifies the TABLESPACE in which future subpartitions of partition P1 in composite-partitioned table EMP will reside.
ALTER TABLE emp MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE ts1;
Since all subpartitions must share the same attributes, except TABLESPACE, it is the only attribute that can be changed.
In similar fashion to table partitions, you can alter the default attributes that will be inherited by partitions of a range-partitioned global index, or local index partitions for range, hash, or composite-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 TABLESPACE...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. 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 composite-partitioned table, note the following:
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 composite-partitioned table SCUBAGEAR are marked UNUSABLE in this example:
ALTER TABLE scubagear MPDIFY PARTITION ts1 UNUSABLE LOCAL INDEXES;
You also use the ALTER TABLE...MODIFY PARTITION statement to modify attributes of a hash partition. However, since the physical attributes of individual hash partitions must all be the same (except for TABLESPACE), you are restricted to:
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 hash 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 allows you to 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 TABLE, 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 SUBPARTITION clause.
You can use the MOVE PARTITION clause of the ALTER TABLE statement to:
Typically, you can change the physical storage attributes of a partition in a single step via a ALTER TABLE/INDEX...MODIFY PARTITION statement. However, there are some physical attributes, such as TABLESPACE, that you cannot modify via MODIFY PARTITION. In these cases you can use the MOVE PARTITION clause.
When the partition you are moving contains data, MOVE PARTITION marks the matching partition in each local index, and all global index partitions as unusable. You must rebuild these index partitions after issuing MOVE PARTITION. Global indexes must also be rebuilt.
Use the MOVE PARTITION clause to move a partition. For example, a DBA wants to move the most active partition to a tablespace that resides on its own disk (in order to balance I/O) and he wishes to LOG the action. The DBA can issue the following statement:
ALTER TABLE parts MOVE PARTITION depot2 TABLESPACE ts094 NOLOGGING;
This statement always drops the partition's old segment and creates a new segment, even if you don't specify a new tablespace.
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);
Some clauses, such as MOVE PARTITION and DROP PARTITION, mark 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.
You might rebuild index partitions for any of the following reasons:
The following sections discuss your options for rebuilding index partitions and subpartitions.
You can rebuild global index partitions in two ways:
You can rebuild local indexes using either ALTER INDEX or ALTER TABLE:
The ALTER INDEX...REBUILD PARTITION statement rebuilds one partition of an index. It cannot be used on a composite-partitioned table. At the same time as you recreate the index, you can 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 is another tablespace.
ALTER INDEX scuba REBUILD SUBPARTITION bcd_types TABLESPACE tbs23 PARALLEL (DEGREE 2);
The REBUILD UNUSABLE LOCAL INDEXES clause of the 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.
You can rename a range or hash partition, using the ALTER TABLE...RENAME PARTITION statement. An example is:
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.
This next statement simply shows how to rename a subpartition that has a system generated name that was a consequence of adding a partition to an underlying table:
ALTER INDEX scuba RENAME SUBPARTITION sys_subp3254 TO bcd_types;
The SPLIT PARTITION clause or the ALTER TABLE or ALTER INDEX statement is used to redistribute the contents of a partition into two new partitions. You may want to do this when a partition becomes too large and causes backup, recovery, or maintenance operations to take a long time to complete. You can also use the SPLIT PARTITION clause to redistribute the I/O load.
If the partition you are splitting contains data, the ALTER TABLE...SPLIT PARTITION statement marks UNUSABLE the new partitions (there are two) in each local index, all global index partitions, and any global nonpartitioned index. You must rebuild such affected indexes or index partitions.
This clause cannot be used for hash partitions or subpartitions.
You can split a table partition by issuing the ALTER TABLE...SPLIT PARTITION statement. You may 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, the DBA issues 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;
This is the opposite of merging range composite partitions. When you split range composite partitions, the new subpartitions will be rehashed into either the number of subpartitions specified in a SUBPARTITIONS or SUBPARTITION clause, or, if no such clause is included, table-level defaults will be used.
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);
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.
The following statement splits the global index partition, QUON1:
ALTER INDEX quon1 SPLIT PARTITION canada AT VALUES LESS THAN ( 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 you only need to rebuild if that partition was previously marked UNUSABLE.
Use the ALTER TABLE...TRUNCATE PARTITION statement when you want 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, the ALTER TABLE TRUNCATE PARTITION statement truncates the matching partition in each local index. If there is a global index (partitioned or nonpartitioned) on the table, it is marked UNUSABLE and must be rebuilt.
You can use the ALTER TABLE...TRUNCATE PARTITION statement to remove all rows from a table partition with or without reclaiming space. If there are local indexes defined for this table, ALTER TABLE...TRUNCATE PARTITION also truncates the matching partition from each local index.
If the partition contains data and global indexes, use either of the following methods to truncate the table partition:
ALTER TABLE sales TRUNCATE PARTITION dec94; 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.
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.
If a partition contains data and has referential integrity constraints, choose either of the following methods to truncate the table partition:
ALTER TABLE sales DISABLE CONSTRAINT dname_sales1; ALTER TABLE sales TRUNCATE PARTITTION dec94; ALTER TABLE sales ENABLE CONSTRAINT dname_sales1;
This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table.
DELETE FROM sales WHERE TRANSID < 10000; 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;
This section presents some examples for working with partitioned tables and indexes.
A historical table describes the business transactions of an enterprise over intervals of time. Historical tables can be base tables, which contain base information; for example, sales, checks, orders. Historical tables can also be rollup tables, which contain summary information derived from the base information via operations such as GROUP BY, AVERAGE, or COUNT.
The time interval in a historical table is often a rolling window; DBAs periodically delete sets of rows that describe the oldest transaction, and in turn allocate space for sets of rows that describe the most recent transaction. For example, at the close of business on April 30, 1995 the DBA deletes the rows (and supporting index entries) that describe transactions from April 1994, and allocates space for the April 1995 transactions.
Now consider a specific example.
You have a table, ORDER, which contains 13 months of transactions: a year of historical data in addition to orders for the current month. There is one partition for each month; the partitions are named ORDER_yymm, as are the tablespaces in which they reside.
The ORDER table contains two local indexes, ORDER_IX_ONUM, which is a local, prefixed, unique index on the order number, and ORDER_IX_SUPP, which is a local, non-prefixed index on the supplier number. The local index partitions are named with suffixes that match the underlying table. There is also a global unique index, ORDER_IX_CUST, for the customer name. ORDER_IX_CUST contains three partitions, one for each third of the alphabet. So on October 31, 1994, change the time window on ORDER as follows:
ALTER TABLESPACE order_9310 BEGIN BACKUP; ... ALTER TABLESPACE order_9310 END BACKUP;
ALTER TABLE order DROP PARTITION order_9310;
ALTER TABLE order ADD PARTITION order_9411;
ALTER INDEX order_ix_cust REBUILD PARTITION order_ix_cust_AH; ALTER INDEX order_ix_cust REBUILD PARTITION order_ix_cust_IP; ALTER INDEX order_ix_cust REBUILD PARTITION order_ix_cust_QZ;
Ordinarily, Oracle acquires sufficient locks to ensure that no operation (DML, DDL, or utility) interferes with an individual DDL statement, such as ALTER TABLE...DROP PARTITION. However, if the partition maintenance operation requires several steps, it is the DBA's responsibility to ensure that applications (or other maintenance operations) do not interfere with the multi-step operation in progress.
A couple of methods for doing this are:
This scenario describes how to convert a partition view (also called "manual partition") into a partitioned table. The partition view is defined as follows:
CREATE VIEW accounts SELECT * FROM accounts_jan98 UNION ALL SELECT * FROM accounts_feb98 UNION ALL ... SELECT * FROM accounts_dec98;
To incrementally migrate the partition view to a partitioned table, follow these steps:
CREATE TABLE accounts_new (...) TABLESPACE ts_temp STORAGE (INITIAL 2) PARTITION BY RANGE (opening_date) (PARTITION jan98 VALUES LESS THAN ('01-FEB-1998'), ... PARTITION dec98 VALUES LESS THAN ('01-FEB-1998'));
ALTER TABLE accounts_new EXCHANGE PARTITION nov98 WITH TABLE accounts_nov98 WITH VALIDATION; ALTER TABLE accounts_new EXCHANGE PARTITION dec98 WITH TABLE accounts_dec98 WITH VALIDATION;
So now the placeholder data segments associated with the NOV98 and DEC98 partitions have been exchanged with the data segments associated with the ACCOUNTS_NOV98 and ACCOUNTS_DEC98 tables.
CREATE OR REPLACE VIEW accounts SELECT * FROM accounts_jan98 UNION ALL SELECT * FROM accounts_feb_98 UNION ALL ... UNION ALL SELECT * FROM accounts_new PARTITION (nov98) UNION ALL SELECT * FROM accounts_new PARTITION (dec98);
DROP VIEW accounts; RENAME accounts_new TO accounts;
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|