Release 2 (9.2)
Part Number A96521-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
This chapter describes various aspects of managing partitioned tables and indexes, and contains the following topics:
| See Also:
Chapter 14, "Managing Space for Schema Objects" is recommended reading before attempting tasks described in this chapter. |
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 can be partitioned in similar fashion. Each partition is stored in its own segment and can be managed individually. It can function 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.
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.
See Also:
|
There are several partitioning methods offered by Oracle:
Indexes, as well as tables, can be partitioned. A global index can only be partitioned by range, but it can be defined on any type of partitioned, or nonpartitioned, table. It can require 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.
The following sections can help you decide on a partitioning method appropriate for your needs:
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 are 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.
Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability 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);
Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to partition mapping.
The list partitioning method is specifically designed for modeling data distributions that follow discrete values. This cannot be easily done by range or hash partitioning because:
Further, list partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally.
Unlike the range and hash partitioning methods, multi-column partitioning is not supported for list partitioning. If a table is partitioned by list, the partitioning key can consist only of a single column of the table. Otherwise all columns that can be partitioned by the range or hash methods can be partitioned by the list partitioning method.
When creating list partitions, you must specify:
The following example creates a list-partitioned table. It creates table q1_sales_by_region which is partitioned by regions consisting of groups of states.
CREATE TABLE q1_sales_by_region (deptno number, deptname varchar2(20), quarterly_sales number(10, 2), state varchar2(2)) PARTITION BY LIST (state) (PARTITION q1_northwest VALUES ('OR', 'WA'), PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'), PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'), PARTITION q1_southeast VALUES ('FL', 'GA'), PARTITION q1_northcentral VALUES ('SD', 'WI'), PARTITION q1_southcentral VALUES ('OK', 'TX'));
A row is mapped to a partition by checking whether the value of the partitioning column for a row matches a value in the value list that describes the partition.
For example, some sample rows are inserted as follows:
q1_northwestq1_northwestq1_southeastq1_southwestOne of the interesting things to note about list partitioning is that there is no apparent sense of ordering between partitions (unlike range partitioning). You can also specify a default partition into which rows that do not map to any other partition are mapped. If a default partition were specified in the above example, the state CA would map to that partition.
Range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. These 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 range-hash partitions, you specify the following:
The following statement creates a range-hash partitioned table. In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, 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, ts2, ts3, ts4) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (MAXVALUE));
The partitions of a range-hash 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.
Like the composite range-hash partitioning method, the composite range-list partitioning method provides for partitioning based on a two level hierarchy. The first level of partitioning is based on a range of values, as for range partitioning; the second level is based on discrete values, as for list partitioning. This form of composite partitioning is well suited for historical data, but allows you to further group the rows of data based on unordered or unrelated column values.
When creating range-list partitions, you specify the following:
The following example illustrates how range-list partitioning might be used. The example tracks sales data of products by quarters and within each quarter, groups it by specified states.
CREATE TABLE quarterly_regional_sales (deptno number, item_no varchar2(20), txn_date date, txn_amount number, state varchar2(2)) TABLESPACE ts4 PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY')) (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'), SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'), SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'), SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'), SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'), SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX') ), PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY')) (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'), SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'), SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'), SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'), SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'), SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX') ), PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY')) (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'), SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'), SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'), SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'), SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'), SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX') ), PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY')) (SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'), SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'), SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'), SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'), SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'), SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX') ) );
A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying the subpartition whose descriptor value list contains a value matching the subpartition column value.
For example, some sample rows are inserted as follows:
q1_1999_northwestq2_1999_northeastq3_1999_southeastq4_1999_southwestThe partitions of a range-list partitioned table are logical structures only, as their data is stored in the segments of their subpartitions. The list subpartitions have the same characteristics as list partitions. You can specify a default subpartition, just as you specify a default partition for list partitioning.
Creating a partitioned table or index is very similar to creating a non-partitioned table or index (as described in Chapter 15, "Managing Tables"), but you include a partitioning clause. The partitioning clause, and subclauses, that you include depend upon the type of partitioning you want to achieve.
You can partition both regular (heap organized) tables and index-organized tables, including those containing LOB columns. You can create nonpartitioned global indexes, range-partitioned global indexes, and local indexes on partitioned tables.
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 ROW MOVEMENT.
The following sections present details and examples of creating partitions for the various types of partitioned tables and indexes:
See Also:
|
The PARTITION BY RANGE clause of the CREATE TABLE statement specifies 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 inherit the attributes of their underlying table.
In this example, more complexity is added to the example presented earlier for a range-partitioned table. Storage parameters and a LOGGING attribute are specified at the table level. These replace the corresponding defaults inherited from the tablespace level for the table itself, and are 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 on sales_month for 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));
|
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 Oracle9i Database Globalization Support Guide. |
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 are to reside, are specified. The initial extent size for each hash partition (segment) is also explicitly stated at the table level, and all partitions 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 constructs the index so that it is equipartitioned with the underlying table. Oracle also ensures that the index 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 loc_dept_ix ON dept(deptno) LOCAL;
You can optionally name the hash partitions and tablespaces into which the local index partitions are to be stored, but if you do not do so, Oracle uses the name of the corresponding base partition as the index partition name, and stores the index partition in the same tablespace as the table partition.
The semantics for creating list partitions are very similar to those for creating range partitions. However, to create list partitions, you specify a PARTITION BY LIST clause in the CREATE TABLE statement, and the PARTITION clauses specify lists of literal values, which are the discrete values of the partitioning columns that qualify rows to be included in the partition. For list partitioning, the partitioning key can only be a single column name from the table.
Available only with list partitioning, you can use the keyword DEFAULT to describe the value list for a partition. This identifies a partition that will accommodate rows that do not map into any of the other partitions.
Like for range partitions, 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 inherit the attributes of their underlying table.
The following example creates table sales_by_region and partitions it using the list method. The first two PARTITION clauses specify physical attributes, which override the table-level defaults. The remaining PARTITION clauses do not specify attributes and those partitions inherit their physical attributes from table-level defaults. A default partition is specified.
CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER, store_name VARCHAR(30), state_code VARCHAR(2), sale_date DATE) STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5 PARTITION BY LIST (state_code) ( PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ') STORAGE (INITIAL 20K NEXT 40K PCTINCREASE 50) TABLESPACE tbs8, PARTITION region_west VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO') PCTFREE 25 NOLOGGING, PARTITION region_south VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'), PARTITION region_central VALUES ('OH','ND','SD','MO','IL','MI','IA'), PARTITION region_null VALUES (NULL), PARTITION region_unknown VALUES (DEFAULT) );
To create a range-hash 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 clause. The individual PARTITION and SUBPARTITION or SUBPARTITIONS clauses, and optionally a SUBPARTITION TEMPLATE clause, follow.
Attributes specified for a range partition apply to all subpartitions of that partition. You can specify different attributes for each range partition, and you can specify a STORE IN clause 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));
To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".
The following statement is an example of creating a local index on the emp table where the index segments are spread across tablespaces ts7, ts8, and ts9.
CREATE INDEX emp_ix ON emp(deptno) LOCAL STORE IN (ts7, ts8, ts9);
This local index is equipartitioned with the base table as follows:
The concept of range-list partitioning is similar to that of the other composite partitioning method, range-hash, but this time you specify that the subpartitions are to be list rather than hash. Specifically, after the CREATE TABLE ... PARTITION BY RANGE clause, you include a SUBPARTITION BY LIST clause that follows similar syntax and rules as the PARTITION BY LIST clause. The individual PARTITION and SUBPARTITION clauses, and optionally a SUBPARTITION TEMPLATE clause, follow.
The range partitions of the composite partitioned table are described as for noncomposite range partitioned tables. This allows that optional subclauses of a PARTITION clause can specify physical and other attributes, including tablespace, specific to a partition's segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.
The list subpartition's descriptions, in the SUBPARTITION clauses, are described as for noncomposite list partitions, except the only physical attribute that can be specified is a tablespace (optional). Subpartitions inherit all other physical attributes from the partition description.
The following example of creates a table that specifies a tablespace at the partition and subpartition levels. The number of subpartitions within each partition varies, and default subpartitions are specified.
CREATE TABLE sample_regional_sales (deptno number, item_no varchar2(20), txn_date date, txn_amount number, state varchar2(2)) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY')) TABLESPACE tbs_1 (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'), SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'), SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'), SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'), SUBPARTITION q1_others VALUES (DEFAULT) TABLESPACE tbs_4 ), PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY')) TABLESPACE tbs_2 (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'), SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'), SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'), SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'), SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'), SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX') ), PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY')) TABLESPACE tbs_3 (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'), SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'), SUBPARTITION q3_others VALUES (DEFAULT) TABLESPACE tbs_4 ), PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY')) TABLESPACE tbs_4 );
This example results in the following subpartition descriptions:
q1_1999 are all contained in tbs_1, except for the subpartition q1_others, which is stored in tbs_4 and contains all rows that do not map to any of the other partitions.q2_1999 are all stored in tbs_2.q3_1999 are all contained in tbs_3, except for the subpartition q3_others, which is stored in tbs_4 and contains all rows that do not map to any of the other partitions.q4_1999. This results in one default subpartition being created and stored in tbs_4. The subpartition's name is system generated in the form SYS_SUBPn.To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".
You can create subpartitions in a composite partitioned table using a subpartition template. A subpartition template simplifies the specification of subpartitions by not requiring that a subpartition descriptor be specified for every partition in the table. Instead, you describe subpartitions only once in a template, then apply that subpartition template to every partition in the table.
The subpartition template is used whenever a subpartition descriptor is not specified for a partition. If a subpartition descriptor is specified, then it is used instead of the subpartition template for that partition. If no subpartition template is specified, and no subpartition descriptor is supplied for a partition, then a single default subpartition is created.
In the case of range-hash partitioned tables, the subpartition template can describe the subpartitions in detail, or it can specify just the number of hash subpartitions.
The following example creates a range-hash partitioned table using a subpartition template:
CREATE TABLE emp_sub_template (deptno NUMBER, empname VARCHAR(32), grade NUMBER) PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname) SUBPARTITION TEMPLATE (SUBPARTITION a TABLESPACE ts1, SUBPARTITION b TABLESPACE ts2, SUBPARTITION c TABLESPACE ts3, SUBPARTITION d TABLESPACE ts4 ) (PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (MAXVALUE) );
This example produces the following table description:
partition name_subpartition name
The following query displays the subpartition names and tablespaces:
SQL> SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME 2 FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP_SUB_TEMPLATE' 3 ORDER BY TABLESPACE_NAME; TABLESPACE_NAME PARTITION_NAME SUBPARTITION_NAME --------------- --------------- ------------------ TS1 P1 P1_A TS1 P2 P2_A TS1 P3 P3_A TS2 P1 P1_B TS2 P2 P2_B TS2 P3 P3_B TS3 P1 P1_C TS3 P2 P2_C TS3 P3 P3_C TS4 P1 P1_D TS4 P2 P2_D TS4 P3 P3_D 12 rows selected.
The following example, for a range-list partitioned table, illustrates how using a subpartition template can help you stripe data across tablespaces. In this example a table is created where the table subpartitions are vertically striped, meaning that subpartition n from every partition is in the same tablespace.
CREATE TABLE stripe_regional_sales ( deptno number, item_no varchar2(20), txn_date date, txn_amount number, state varchar2(2)) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) SUBPARTITION TEMPLATE (SUBPARTITION northwest VALUES ('OR', 'WA') TABLESPACE tbs_1, SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE tbs_2, SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE tbs_3, SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE tbs_4, SUBPARTITION midwest VALUES ('SD', 'WI') TABLESPACE tbs_5, SUBPARTITION south VALUES ('AL', 'AK') TABLESPACE tbs_6, SUBPARTITION others VALUES (DEFAULT ) TABLESPACE tbs_7 ) (PARTITION q1_1999 VALUES LESS THAN ( TO_DATE('01-APR-1999','DD-MON-YYYY')), PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-JUL-1999','DD-MON-YYYY')), PARTITION q3_1999 VALUES LESS THAN ( TO_DATE('01-OCT-1999','DD-MON-YYYY')), PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY')) );
If you specified the tablespaces at the partition level (for example, tbs_1 for partition q1_1999, tbs_2 for partition q1_1999, tbs_3 for partition q3_1999, and tbs_4 for partition q4_1999) and not in the subpartition template, then the table would be horizontally striped. All subpartitions would be in the tablespace of the owning partition.
For index-organized tables, you can use the range or hash partitioning method. However, only range partitioned index-organized tables can contain columns with LOBs. The semantics for creating range or hash-partitioned index-organized tables is similar to that for regular tables with these differences:
ORGANIZATION INDEX clause, and INCLUDING and OVERFLOW clauses as necessary.PARTITION or PARTITIONS clauses can have OVERFLOW subclauses that allow you to specify attributes of the overflow segments at the partition level.Specifying an OVERFLOW clause results in the overflow data segments themselves being equi-partitioned with the primary key index segments. Thus, for partitioned index-organized tables with overflow, each partition has an index segment and an overflow data segment.
For index-organized tables, the set of partitioning columns must be a subset of the primary key columns. Since rows of an index-organized table are stored in the primary key index for the table, the partitioning criterion has an effect on the availability. By choosing the partition key to be a subset of the primary key, an insert operation only needs to verify uniqueness of the primary key in a single partition, thereby maintaining partition independence.
Support for secondary indexes on index-organized tables is similar to the support for regular tables, however, certain maintenance operations do not mark global indexes UNUSABLE, as is the case for regular tables.
See Also:
|
You can partition index-organized tables, and their secondary indexes, 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 OVERFLOW TABLESPACE overflow_ts2, ... PARTITION VALUES LESS THAN (MAXVALUE) TABLESPACE ts13);
The other option for partitioning index-organized tables is to use the hash method. In the following example the index-organized table, sales, is partitioned by the hash method.
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 PARTITION BY HASH (week_no) PARTITIONS 16 STORE IN (ts1, ts2, ts3, ts4) OVERFLOW STORE IN (ts3, ts6, ts9);
Use caution when creating partitioned objects in a database with tablespaces of multiple block size. The storage of partitioned objects in such tablespaces is subject to some restrictions. Specifically, all partitions of the following entities must reside in tablespaces of the same block size:
LOB columns stored out of lineTherefore:
LOB column, each partition of that column must be stored in tablespaces of equal block sizes. However, different LOB columns can be stored in tablespaces of different block sizes.When you create or alter a partitioned table or index, all tablespaces you explicitly specify for the partitions and subpartitions of each entity must be of the same block size. If you do not explicitly specify tablespace storage for an entity, the tablespaces Oracle uses by default must be of the same block size. Therefore you must be aware of the default tablespaces at each level of the partitioned object.
This section describes how to perform partition and subpartition maintenance operations for both tables and indexes.
Table 17-1 lists 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.
|
Note: The first time you 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 partitioned tables with B-tree indexes. For more information, see the Oracle9i Data Warehousing Guide. |
Table 17-2 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, 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 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.
Before discussing the individual maintenance operations for partitioned tables and indexes, it is important to discuss the effects of the UPDATE GLOBAL INDEXES clause that can be specified in the ALTER TABLE statement.
By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) global indexes. You must then rebuild the entire global index or, if partitioned, all of its partitions. Oracle enables you to override this default behavior if you specify UPDATE GLOBAL INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells Oracle to update the global index at the time it executes the maintenance operation DDL statement. This provides the following benefits:
UNUSABLE. The index remains available even while the partition DDL is executing and it can be used to access other partitions in the table.But also consider the following performance implications when you specify UPDATE GLOBAL INDEXES:
UNUSABLE are updated. However, this must be compared against the time it takes to execute DDL without updating indexes, and then rebuilding 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.DROP, TRUNCATE, and EXCHANGE operations are no longer fast operations. Again, one must compare the time it takes to do the DDL and then rebuild all global indexes.NOLOGGING.The following operations support the UPDATE GLOBAL INDEXES clause:
ADD PARTITION|SUBPARTITION (hash only)COALESCE PARTITION|SUBPARTITIONDROP PARTITIONEXCHANGE PARTITION|SUBPARTITIOMERGE PARTITIONMOVE PARTITION|SUBPARTITIONSPLIT PARTITIONTRUNCATE PARTITION|SUBPARTITIONThis 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.
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 jan96 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, Oracle populates the new partition with rows rehashed from an existing partition (selected by Oracle) as determined by the hash function.
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 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;
Indexes may be marked UNUSABLE as explained in the following table:
The following statement illustrates adding 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.
Partitions can be added at both the range partition level and the hash subpartition level.
Adding a new range partition to a range-hash partitioned table is as described previously in "Adding a Partition to a Range-Partitioned Table". However, 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 are 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 range-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.
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;
Local index subpartitions corresponding to the added and rehashed subpartitions must be rebuilt. Unless you specify UPDATE GLOBAL INDEXES, all global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.
Partitions can be added at both the range partition level and the list subpartition level.
Adding a new range partition to a range-list partitioned table is as described previously in "Adding a Partition to a Range-Partitioned Table". However, 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.
This following statement statements 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.
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 partitioned table.
The following statement adds a new subpartition to the existing set of subpartitions in 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;
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. Oracle 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 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 range-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 Oracle, and is dropped after its contents have been redistributed.
Indexes may be marked UNUSABLE as explained in the following table:
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 in "Adding a Subpartition to a Range-Hash Partitioned Table".
ALTER TABLE diving MODIFY PARTITION us_locations COALESCE SUBPARTITION;
You can drop partitions from range, composite, list, or composite range-list partitioned tables. For hash-partitioned tables, or hash subpartitions of range-hash partitioned tables, you must perform a coalesce operation instead.
Use one of the following statements to drop a table partition or subpartition:
ALTER TABLE ... DROP PARTITION to drop a table partitionALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a range-list partitioned tableIf you want to preserve the data in the partition, use the MERGE PARTITION statement instead of the DROP PARTITION statement.
If there are local indexes defined for the table, 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 are true:
UPDATE GLOBAL INDEXES (cannot be specified for index-organized tables)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 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) will have been marked UNUSABLE. The following statements provide and 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 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.
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, and also fires triggers and generates redo and undo logs.
For example, to drop the first partition, which has a partition bound of 10000, 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.
Method 3:
Specify UPDATE GLOBAL INDEXES in the ALTER TABLE statement. This causes the global index to be updated at the time the partition is dropped.
ALTER TABLE sales DROP PARTITION dec98 UPDATE GLOBAL 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:
Disable the integrity constraints, issue the ALTER TABLE ... DROP PARTITION statement, then enable the integrity constraints:
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.
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 enforces referential integrity constraints, and also fires triggers and generates redo and undo log.
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 range-hash partitioned table, or convert the partition of the range-hash partitioned table into a hash-partitioned table. Similarly, you can convert a list-partitioned table into a partition of a range-list partitioned table, or convert the partition of the range-list partitioned table into a list-partitioned table
Exchanging table partitions is most useful when you have an application using nonpartitioned tables that you want to convert to partitions of a partitioned table. For example, you could already have partition views that you want to migrate into partitioned tables. Exchanging partitions also facilitates high-speed data loading when used with transportable tablespaces.
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).
Unless you specify UPDATE GLOBAL INDEXES (this cannot be specified for index-organized tables), Oracle marks UNUSABLE the global indexes, or all global index partitions, on the table whose partition is being exchanged. Any global indexes, or global index partitions, on the table being exchanged are marked UNUSABLE.