Release 2 (8.1.6)
Part Number A76965-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Concepts Release 2 (8.1.6) Part Number A76965-01 |
|
This chapter describes partitioned tables and indexes, and explains some administrative considerations for partitioning. It covers the following topics:
This section explains how partitioning can help you manage large tables and indexes in an Oracle database. It includes the following sections:
Oracle8i Designing and Tuning for Performance for information about partitioning materialized views
See Also:
Partitioning addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes. Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.
Two primary methods of partitioning are available: range partitioning, which partitions the data in a table or index according to a range of values, and hash partitioning, which partitions the data according to a hash function. Another method, composite partitioning, partitions the data by range and further subdivides the data into subpartitions using a hash function.
All partitions of a table or index have the same logical attributes, although their physical attributes may be different. For example, all partitions in a table share the same column and constraint definitions, and all partitions in an index share the same index columns, but storage specifications and other physical attributes such as PCTFREE, PCTUSED, INITRANS, and MAXTRANS may vary for different partitions of the same table or index.
Like partitions, all subpartitions of a table or index have the same logical attributes. Unlike partitions, however, the subpartitions of a single partition cannot have different physical attributes.
A separate segment stores each partition of a range-partitioned or hash-partitioned table or index, and each subpartition of a composite-partitioned table or index. The partitions of a composite-partitioned table or index are logical structures only--they do not occupy separate segments because their data is stored in the segments of their subpartitions.
Optionally, you can store each partition (or subpartition of a composite-partitioned table or index) in a separate tablespace, which has the following advantages:
In Figure 11-1, the SALES table contains historical data divided by week number into 13 four-week partitions.
This SQL statement creates the range-partitioned table shown in Figure 11-1:
CREATE TABLE sales ( acct_no NUMBER(5), acct_name CHAR(30), amount_of_sale NUMBER(6), week_no INTEGER ) PARTITION BY RANGE ( week_no ) ... (PARTITION sales1 VALUES LESS THAN ( 4 ) TABLESPACE ts0, PARTITION sales2 VALUES LESS THAN ( 8 ) TABLESPACE ts1, ... PARTITION sales13 VALUES LESS THAN ( 52 ) TABLESPACE ts12 );
The Oracle server can explicitly recognize partitions and subpartitions. Oracle uses this knowledge to optimize SQL statements by pruning unnecessary partitions or subpartitions. For example, if a query only involves Q1 sales data, there is no need to retrieve data for the remaining three quarters. Such intelligent pruning can dramatically reduce the data volume, resulting in substantial improvements in query performance.
If the optimizer determines that the selection criteria used for pruning are satisfied by all the rows in the accessed partition or subpartition, it removes those criteria from the predicate list (WHERE clause) during evaluation in order to improve performance. However, the optimizer cannot prune partitions if the SQL statement applies a function to the partitioning column, with the exception of the TO_DATE function. Similarly, the optimizer cannot use an index if the SQL statement applies a function to the indexed column, unless it is a function-based index.
Pruning can eliminate index partitions even when the underlying table's partitions cannot be eliminated, if the index and table are partitioned on different columns. You can often improve the performance of operations on large tables by creating partitioned indexes which reduce the amount of data that your SQL statements need to access or modify.
The ability to prune unneeded partitions or subpartitions from SQL statements increases performance and availability for many purposes, including:
"DATE Datatypes" for more information about what happens if a SQL statement applies a function to a TO_DATE partitioning column
See Also:
An additional area of optimization for partitioned tables is a partition-wise join, which is a large join operation that is broken into smaller joins that are performed sequentially or in parallel.
In order to use partition-wise joining, both tables must be equipartitioned.
If the optimizer determines that partition-wise joining provides a performance gain, then it will be used. In some situations, the optimizer can combine pruning and partition-wise joining.
This section identifies the classes of databases that could benefit from the use of partitioning, and characterizes them in terms of the problems they present:
A very large database (VLDB) contains hundreds of gigabytes or even a few terabytes of data. Partitioning provides support for VLDBs that contain mostly structured data, rather than unstructured data. These VLDBs typically owe their size to the presence of a few very large data objects (tables and indexes) rather than to the presence of a very large number of data objects.
There are two major categories of VLDBs:
A VLDB can be characterized as an OLTP database if most of its workload is OLTP. Similarly, a VLDB can be characterized as a DSS database if most of its workload consists of DSS queries.
Partitioning efficiently supports both OLTP VLDBs and DSS VLDBs.
Historical databases are the most common type of DSS VLDB. They contain two classes of tables:
The time interval reflected in a historical table is a rolling window, so periodically the database administrator (DBA) deletes the set of rows describing the oldest transactions and allocates space for the set of rows describing new transactions. For example, at the close of business on April 30, 1997 the DBA deletes the rows and all supporting index entries that describe May 1995 transactions and allocates space for May 1997 transactions.
Most of the data in a historical VLDB is stored in a few very large historical tables that present special problems due to their size and the requirement to smoothly roll out old data and roll in new data.
Partitioning addresses the problem of supporting large historical tables and their indexes by dividing historical data into time-related partitions that can be managed independently and added or deleted conveniently.
Mission-critical OLTP databases present special availability and performance problems even if they are not very large. For example, it may be necessary to perform scheduled maintenance operations or recover a 10-gigabyte table in a very short period of time, perhaps an hour or less. Also, the DBA may need a degree of control over data placement that is hard to achieve when a table or index is spread over multiple drives.
Partitioning can increase the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures. You can also improve access performance to a critical table or index by controlling performance parameters on a partition basis.
Partitions enable data management operations like data loads, index creation, and data purges at the partition level, rather than on the entire table, resulting in significantly reduced times for these operations.
Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations:
Partition maintenance operations are faster than full table or index maintenance operations. A speedup can be achieved equal to the ratio:
if there are no interpartition stored constructs, such as global indexes and referential integrity constraints.
To further reduce downtime, a partition maintenance operation can take advantage of performance features that are available for table and index-level maintenance operations, such as the PARALLEL, NOLOGGING, and DIRECT (or APPEND) clauses where applicable.
Partition independence for the partition maintenance operations makes it possible to perform concurrent maintenance operations on different partitions of the same table or index, as well as concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
For example, you can Direct Path Load into partitions PA and PB at the same time, while applications are executing standard SQL SELECT and DML operations against other partitions.
Partition independence is particularly important for operations that involve data movement. Such operations can take a long time (minutes, hours, or even days). Partitioning can reduce the window of unavailability on other partitions to a short time (a few seconds) during operations that involve data movement, if there are no inter-partition stored constructs, such as global indexes and referential integrity constraints.
Partition independence is not needed for short operations with no data movement because these operations complete in a short time.
Some maintenance operations are unplanned events, required to recover from hardware or software failures that cause data loss or corruption. Recovery from hardware failures and many system software failures is accomplished by running the RECOVER statement on a database, tablespace, or datafile. Any tables or indexes that have records in a tablespace or datafile being recovered remain unavailable during recovery. Increased availability is particularly important for mission-critical OLTP databases.
Because partitions are independent of each other, the unavailability of a piece or a subset of pieces does not affect access to the rest of the data.
Storing partitions in separate tablespaces provides the following benefits:
DSS queries on very large tables present special performance problems. A query that requires a table scan can take a long time, because it must inspect every row in the table. There is no way to identify and skip subsets of irrelevant rows. The problem is particularly important for historical tables for which many queries concentrate access on rows that were generated recently.
Partitions help solve this DSS performance problem. A query that only requires rows that correspond to a single partition or range of partitions can be executed using a partition scan rather than a table scan.
For example, a query that requests data generated in the month of October 1997 can scan just the rows stored in the October 1997 partition, rather than rows generated over many years of activity. This improves response time and it can also substantially reduce the temporary disk space requirement for queries that require sorts.
Partitioning can control how data is spread across physical devices. To balance I/O use, you can specify where to store the partitions of a table or index.
With this level of location control, you can accommodate the special needs of applications that require fast response time by reducing disk contention and using faster devices. On the other hand, data that is accessed infrequently, such as old historical data, can be moved to slow disks or stored in subsystems that support a storage hierarchy.
Disk striping and partitioning are both tools that can improve performance through the reduction of contention for disk arms. Which tool to use, or in which proportions to use them together, is an important issue to consider when physically designing databases. These issues should be considered not only with respect to performance, but also with respect to availability and partition independence.
Figure 11-2 shows the two extremes of combining partitioning and striping. Both (a) and (b) in Figure 11-2 show four partitions spread across eight disks, but (a) stripes each partition onto its own pair of disks, whereas (b) stripes each partition onto all eight disks.
The performance characteristics are better in (b), but if any single disk failure occurs, all partitions are adversely affected.
The availability characteristics are better in (a), because failure of a single disk only affects one partition.
Intermediate configurations are also possible, where subsets of partitions are striped over subsets of disks.
The trade-off between performance and availability must be decided when determining how to partition tables and indexes, and how to stripe the disks on which they are stored.
For mission-critical databases it is recommended that partition independence and availability be favored. Therefore each partition that you want to stripe across disks should be striped onto its own set of disk drives, which should include enough drives to achieve the required I/O parallelism for accesses to that partition.
The vast majority of application programs require partition transparency. That is, the programs should be insensitive to whether the data they access is partitioned and how it is partitioned.
A few application programs, however, can take advantage of partitions by explicitly requesting access to an individual partition, rather than the entire table. For example, a user might want to break a long batch job on a very large table into a sequence of short nightly batch jobs on individual partitions.
Instead of using partitioned tables, you can build separate tables with identical templates and define a view that does a UNION of these tables. This is known as manual partitioning, and the view is known as a partition view. Partition views were the only form of partitioning available in Oracle7. They are not recommended for new applications in Oracle8i.
Partition views that were created for Oracle7 databases can be converted to partitioned tables by using the EXCHANGE PARTITION clause of the ALTER TABLE statement.
|
See Also:
Oracle8i Administrator's Guide for instructions on converting partition views to partitioned tables |
This section describes the basic partitioning model, which includes these partitioning methods:
This section also includes these additional topics:
You can partition a table or index with clauses to the CREATE TABLE or CREATE INDEX statement. After creating a partitioned table or index, you can use an ALTER TABLE or ALTER INDEX statement to modify its partitioning attributes.
The partitioning syntax for CREATE TABLE and CREATE INDEX statements is very similar. The CREATE TABLE statement specifies:
If the table is nonpartitioned, these are the real physical attributes of the segment associated with the table.
If the table is partitioned, these table-level attributes specify defaults for the individual partitions of the table.
For a partitioned table, there is also a partition specification that includes:
Each partition description includes a clause defining supplemental partition-level information about the algorithm used to map rows to partitions. This clause can also specify a partition name and physical attributes for the partition.
Each subpartition description for composite partitioning can specify a subpartition name and a tablespace for the subpartition.
Partitioned tables cannot have any columns with LONG or LONG RAW datatypes. If a table or index is partitioned on a column that has the DATE datatype and if the NLS date format does not specify the century with the year, the partition descriptions must use the TO_DATE function to specify the year completely; otherwise you cannot create the table or index.
You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table--they cannot be global indexes.
Cost based optimization is used when a SQL statement accesses partitioned tables or indexes. Rule base optimization is not available for partitions. A single execution plan is used for all partitions of a partitioned table.
Statistics can be gathered by partition or subpartition, using the DBMS_STATS package or the ANALYZE statement. It is important to gather statistics whenever the nature of the data in a partitioned table changes significantly. The statistics can be found in these data dictionary views:
Range partitioning maps rows to partitions based on ranges of column values. Range partitioning is defined by the partitioning specification for a table or index:
PARTITION BY RANGE ( column_list )
and by the partitioning specifications for each individual partition:
VALUES LESS THAN ( value_list )
where:
In each partition, all rows or rows pointed to by index entries have partitioning keys that compare less than the partition bound for that partition. Unless the partition is the first partition in the table or index, all of its partitioning keys also compare greater than or equal to the partition bound for the previous partition.
For example, in the following table of four partitions (one for each quarter's sales), a row with SALE_YEAR=1997, SALE_MONTH=7, and SALE_DAY=18 has partitioning key (1997, 7, 18). Therefore it belongs in the third partition and is stored in tablespace TSC. A row with SALE_YEAR=1997, SALE_MONTH=7, and SALE_DAY=1 has partitioning key (1997, 7, 1) and also belongs in the third partition, stored in tablespace TSC.
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 (1997, 04, 01) TABLESPACE tsa, PARTITION sales_q2 VALUES LESS THAN (1997, 07, 01) TABLESPACE tsb, PARTITION sales_q3 VALUES LESS THAN (1997, 10, 01) TABLESPACE tsc, PARTITION sales_q4 VALUES LESS THAN (1998, 01, 01) TABLESPACE tsd );
You can use the ALTER TABLE MERGE PARTITIONS statement to merge the contents of two adjacent range partitions into one partition. You might want to do this to keep historical data online in larger partitions. For example, you might want to have daily partitions, with the oldest partition rolled up into weekly partitions, which can then be rolled up into monthly partitions, and so on.
You can use the ALTER TABLE EXCHANGE PARTITION statement to exchange the data and index segments of:
See Also:
Although partitioning by range is well-suited for historical databases, it may not be the best choice for other purposes. Another method of partitioning, hash partitioning, uses a hash function on the partitioning columns to stripe data into partitions. Hash partitioning allows data that does not lend itself to range partitioning to be easily partitioned for performance reasons such as parallel DML, partition pruning, and partition-wise joins.
Hash partitioning is a better choice than range partitioning when:
Hash partitions can be named and stored in specific tablespaces. Local indexes on hash partitions are equipartitioned with the table data. For local index partitions, you can specify the partition names and tablespaces. Storage parameters cannot be specified.
The number of partitions should be a power of two (2, 4, 8, and so on) to obtain the most even data distribution. In addition, cardinality is very important for choosing a key for hash partitioning. For example, suppose that you want to have 16 hash partitions, and that you choose a column with 25 distinct values. Then you might have a scenario where eight of your hash partitions contain a single distinct value, and eight contain two distinct values. Thus, your hash partitions will be widely different sizes. In fact, with such a small number of distinct values, it is even possible that some hash partitions will contain zero distinct values and others will contain three or more distinct values. Since one of the reasons to use hash partitioning is to have evenly sized partitions, Oracle recommends that you do not use hash partitioning on key columns with low cardinality.
In general, the hash-partitioning key should be unique or near unique. Since a table may contain several near-unique keys and thus several candidates for hash partitioning, the other consideration for choosing the partitioning key is the access profile. Hash partitioning can improve the performance of single-key lookups (for example, SELECT * FROM emp WHERE empno = 123), but it does not improve range lookups (for example, SELECT * FROM emp WHERE sal > 5000). Perhaps more importantly, hash partitioning can enable partition-wise joins when two tables are hash-partitioned on the join key. This should be the primary performance consideration for choosing a hash-partitioning key.
The following example creates a table that names and stores a hash partition in a specific tablespace:
CREATE TABLE product( ... ) STORAGE (INITIAL 10M) PARTITION BY HASH(column_list) ( PARTITION p1 TABLESPACE h1, PARTITION p2 TABLESPACE h2 );
The concepts of splitting, dropping, and merging partitions do not apply to hash partitions. However, you can increase or decrease the number of partitions by using ALTER TABLE to ADD or COALESCE hash partitions.
You can use the ALTER TABLE EXCHANGE PARTITION statement to exchange the data and index segments of:
Composite partitioning partitions data using the range method and, within each partition, subpartitions it using the hash method. This type of partitioning supports historical operations data at the partition level and parallelism (parallel DML) and data placement at the subpartition level.
Composite partitioning:
The partitions of a composite-partitioned table or index are logical structures only--their data is stored in the segments of their subpartitions.
The following example creates a table that uses composite partitioning, assuming the NLS DATE format is DD-MON-YYYY:
CREATE TABLE orders( ordid NUMBER, orderdate DATE, productid NUMBER, quantity NUMBER) PARTITION BY RANGE(orderdate) SUBPARTITION BY HASH(productid) SUBPARTITIONS 8 STORE IN(ts1,ts2,ts3,ts4,ts5,ts6,ts7,ts8) ( PARTITION q1 VALUES LESS THAN('01-APR-1998'), PARTITION q2 VALUES LESS THAN('01-JUL-1998'), PARTITION q3 VALUES LESS THAN('01-OCT-1998'), PARTITION q4 VALUES LESS THAN(MAXVALUE));
In this example, the ORDERS table is range partitioned on the ORDERDATE key, in four separate ranges representing quarters of the year. Each range partition is further subpartitioned on the PRODUCTID key into eight subpartitions, for a total of 32 subpartitions. Each tablespace contains one subpartition from each partition.
The following example creates a table that uses composite partitioning with each subpartition explicitly named and stored in a specified tablespace:
CREATE TABLE orders( ... ) PARTITION BY RANGE(orderdate) SUBPARTITION BY HASH(productid) SUBPARTITIONS 8 STORE IN (ts1,ts2,ts3,ts4,ts5,ts6,ts7,ts8) ( PARTITION q1 VALUES LESS THAN('01-APR-1998') ( SUBPARTITION q1_h1 TABLESPACE ts1, ... SUBPARTITION q1_h7 TABLESPACE ts7, SUBPARTITION q1_h8 TABLESPACE ts8) PARTITION q2 VALUES LESS THAN('01-JUL-1998'), ... );
Every partition or subpartition has a name, which must conform to the usual rules for naming schema objects and their parts. In particular:
For composite partitioning, the names of subpartitions and partitions are in the same namespace; that is, a partition and a subpartition belonging to the same parent table or index cannot have the same name.
You can rename a partition or subpartition; however, you cannot create any synonyms on a partition or subpartition name.
Partition and subpartition names can optionally be referenced in DDL and DML statements and in utility statements like Import/Export and SQL*Loader. They always appear in context with the name of their parent table or index and they are never qualified by a schema name. The schema name can be used to qualify the parent table or index. For example:
ALTER TABLE admin.patient_visits DROP PARTITION pv_dec92; SELECT * FROM sales PARTITION (s_nov97) s WHERE s.amount_of_sale > 1000;
|
See Also:
"Partition-Extended and Subpartition-Extended Table Names" for more information about referencing partitions and subpartitions in SQL statements |
The partitioning columns (or subpartitioning columns) of a table or index consist of an ordered list of columns whose values determine how the data is partitioned or subpartitioned. This list can include up to 16 columns, and cannot include any of the following types of columns:
A row's partitioning key is an ordered list of its values for the partitioning columns. Similarly, in composite partitioning a row's subpartitioning key is an ordered list of its values for the subpartitioning columns. Oracle applies either the range or hash method to each row's partitioning key or subpartitioning key to determine which partition or subpartition the row belongs in.
In a range-partitioned table or index, the partitioning key of each row is compared with a set of upper and lower bounds to determine which partition the row belongs in:
The partition bounds collectively define an ordering of the partitions in a table or index. The first partition is the partition with the lowest VALUES LESS THAN clause, and the last or highest partition is the partition with the highest VALUES LESS THAN clause.
If you attempt to insert a row into a table and the row's partitioning key is greater than or equal to the partition bound for the highest partition in the table, the insert will fail.
When comparing character values in partitioning keys and partition bounds, characters are compared according to their binary values. However, if a character consists of more than one byte, Oracle compares the binary value of each byte, not of the character. The comparison also uses the comparison rules associated with the column data type. For example, blank-padded comparison is done for the ANSI CHAR data type. The NLS parameters, specifically the initialization parameters NLS_SORT and NLS_LANGUAGE and the environment variable NLS_LANG, have no effect on the comparison.
The binary value of character data varies depending on which character set is being used (for example, ASCII or EBCDIC). For example, ASCII defines the characters A through Z as less than the characters a through z, whereas EBCDIC defines A through Z as being greater than a through z. Thus, partitions designed for one sequence will not work with the other sequence. You must repartition the table after importing from a table using a different character set.
|
See Also:
|
You can specify the keyword MAXVALUE for any value in the partition bound value_list. This keyword represents a virtual infinite value that sorts higher than any other value for the data type, including the NULL value.
For example, you might partition the OFFICE table on STATE (a CHAR(10) column) into three partitions with the following partition bounds:
NULL cannot be specified as a value in a partition bound value_list. An empty string also cannot be specified as a value in a partition bound value_list, because it is treated as NULL within the database server.
For the purpose of assigning rows to partitions, Oracle sorts nulls greater than all other values except MAXVALUE. Nulls sort less than MAXVALUE.
This means that if a table is partitioned on a nullable column, and the column is to contain nulls, then the highest partition should have a partition bound of MAXVALUE for that column. Otherwise the rows that contain nulls will map above the highest partition in the table and the insert will fail.
If the partition key includes a column that has the DATE datatype and the NLS date format does not specify the century with the year, you must specify partition bounds using the TO_DATE() function with a 4-character format mask for the year. Otherwise you will not be able to create the table or index.
For example, you might create the SALES table using a DATE column:
CREATE TABLE sales ( invoice_no NUMBER, sale_date DATE NOT NULL ) PARTITION BY RANGE (sale_date) ( PARTITION sales_q1 VALUES LESS THAN (TO_DATE('1997-04-01','YYYY-MM-DD')) TABLESPACE tsa, PARTITION sales_q2 VALUES LESS THAN (TO_DATE('1997-07-01','YYYY-MM-DD')) TABLESPACE tsb, PARTITION sales_q3 VALUES LESS THAN (TO_DATE('1997-10-01','YYYY-MM-DD')) TABLESPACE tsc, PARTITION sales_q4 VALUES LESS THAN (TO_DATE('1998-01-01','YYYY-MM-DD')) TABLESPACE tsd );
When you query or modify data, it is recommended that you use the TO_DATE() function in the WHERE clause so that the value of the date information can be determined at compile time. However, the optimizer can prune partitions using a selection criterion on partitioning columns of type DATE when you use another format, as in the following examples:
SELECT * FROM sales WHERE s_saledate BETWEEN TO_DATE('01-JUL-94', 'DD-MON-YY') AND TO_DATE('01-OCT-94', 'DD-MON-YY'); SELECT * FROM sales WHERE s_saledate BETWEEN '01-JUL-1994' AND '01-OCT-1994';
In this case, the date value will be complete only at runtime. Therefore you will not be able to see which partitions Oracle is accessing as is usually shown on the partition_start and partition_stop columns of the EXPLAIN PLAN statement output on the SQL statement. Instead, you will see the keyword 'KEY' for both columns.
When a table or index is partitioned by range on multiple columns, each partition bound and partitioning key is a list (or vector) of values. The partition bounds and keys are ordered according to ANSI SQL2 vector comparison rules. This is also the way Oracle orders multicolumn index keys.
To compare a partitioning key with a partition bound, you compare the values of their corresponding columns until you find an unequal pair and then that pair determines which vector is greater. The values of any remaining columns have no effect on the comparison.
In mathematical terms, for vectors V1 and V2 which contain the same number of values, Vx[i] is the ith value in Vx. Assuming that V1[i] and V2[i] have compatible datatypes:
For example, if the partition bound for partition P is (7, 5, 10) and the partition bound for the next lower partition is (6, 7, 3) then:
Note that the value in the key's third column can be greater than the corresponding value in the partition bound--(x, x, 11) vs. (x, x, 10) in this case--because the comparison does not consider values in the third column after finding an inequality in the second column.
Note that the value in the key's first column can be equal to the value in the first column of the partition bound. The VALUES LESS THAN clause applies to the columns collectively, not to individual columns.
If MAXVALUE appears as an element of a partition bound value_list, then the values of all the following elements are irrelevant. For example, a partition bound of (10, MAXVALUE, 5) is equivalent to a partition bound of (10, MAXVALUE, 6) or to a partition bound of (10, MAXVALUE, MAXVALUE).
Multicolumn partitioning keys are useful when the primary key for the table contains multiple columns, but rows are not distributed evenly over the most significant column in the key. For example, suppose that the SUPPLIER_PARTS table contains information about which suppliers provide which parts, and the primary key for the table is (SUPPNUM, PARTNUM). It is not sufficient to partition on SUPPNUM because some suppliers might provide hundreds of thousands of parts, while others provide only a few specialty parts. Instead, you can partition the table on (SUPPNUM, PARTNUM).
Multicolumn partitioning keys are also useful when you represent a date as three CHAR columns instead of a DATE column.
If you specify a partition bound other than MAXVALUE for the highest partition in a table, this imposes an implicit CHECK constraint on the table. This constraint is not recorded in the data dictionary, but the partition bound itself is recorded.
Two tables or indexes are equipartitioned when the following conditions are true:
They do not have to be the same type of schema object. For example, a table and an index can be equipartitioned.
If A and B are range-partitioned tables or indexes, where A[i] is the ith partition in A and B[i] is the ith partition in B, then A and B are equipartitioned if all of the following are true:
If Apcol[i] is the ith partitioning column in A and Bpcol[i] is the ith partitioning column in B, then the following must also be true:
A[i] and B[i] may differ in their physical attributes. In particular, they do not have to reside in the same tablespace.
Equipartitioning is important to consider when designing the database:
Figure 11-3 shows four logically related schema objects that are equipartitioned:
The logical relationship between the four schema objects is shown on the left in Figure 11-3. The physical partitioning is shown on the right. Triangles represent indexes and rectangles represent tables.
This section describes the rules for creating partitioned tables and indexes and the physical attributes of partitions.
The rules for partitioning tables are simple:
This section discusses the physical attributes of table partitions for range, hash, and composite partitioning.
Default physical attributes are initially specified when the CREATE TABLE statement creates a partitioned table. Since there is no segment corresponding to the partitioned table itself, these attributes are only used in derivation of physical attributes of member partitions. Default physical attributes can later be modified using ALTER TABLE MODIFY DEFAULT ATTRIBUTES.
For hash partitioning, all partitions have the same physical characteristics and so the only physical attribute you can specify for a partition is its tablespace.
Physical attributes of table partitions created by CREATE TABLE or ALTER TABLE ADD PARTITION are determined as follows:
For hash partitioning, ALTER TABLE MOVE PARTITION can be used to move the partition to a different tablespace. For range partitioning, this statement can move the partition or modify its physical attributes. Resulting attributes are determined as follows:
For range partitioning, the physical attributes of table partitions created by ALTER TABLE SPLIT PARTITION are determined as follows:
Physical attributes of all partitions of a table may be modified by ALTER TABLE, for example, ALTER TABLE tablename NOLOGGING changes the logging mode of all partitions of tablename to NOLOGGING.
|
See Also:
"Tablespace and Storage Attributes of LOB Data Partitions" for additional information about the physical attributes of table partitions that contain LOB datatypes |
For composite partitioning, the partitions specify default physical attributes for the subpartitions and the subpartitions are similar to hash partitions, in that the only physical attribute you can specify explicitly for a subpartition is its tablespace.
The default physical attributes are initially specified when the CREATE TABLE statement creates a composite partitioned table. Since there is no segment corresponding to the partitions or to the table itself, these attributes are only used in derivation of the attributes for member subpartitions. The default attributes can later be modified using ALTER TABLE MODIFY DEFAULT ATTRIBUTES or ALTER TABLE MODIFY DEFAULT ATTRIBUTES FOR PARTITION.
The physical attributes for subpartitions created by CREATE TABLE or ALTER TABLE ADD PARTITION are determined as follows:
ALTER TABLE MOVE SUBPARTITION can be used to move a subpartition to a different tablespace, but it does not change other physical attributes of the subpartition. ALTER TABLE MODIFY PARTITION modifies the physical attributes of all of that partition's existing subpartitions as well as the default physical attributes of the partition itself. You can use the FOR PARTITION clause of ALTER TABLE MODIFY PARTITION to avoid changing the attributes of existing subpartitions. Attributes modified at the table level affect the defaults at all three levels: table, partition, and subpartition.
|
See Also:
"Tablespace and Storage Attributes of LOB Data Partitions" for additional information about the physical attributes of table subpartitions that contain LOB datatypes |
The rules for partitioning indexes are similar to those for tables:
However, partitioned indexes are more complicated than partitioned tables because there are four types of partitioned indexes:
These types are described below. Oracle supports three of the four types. Global nonprefixed indexes are not useful in real applications.
In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index is created by specifying the LOCAL attribute.
Oracle constructs the local index so that it is equipartitioned with the underlying table. Oracle partitions the index 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.
Oracle also maintains the index partitioning automatically when partitions in the underlying table are added, dropped, merged, or split, or when hash partitions or subpartitions are added or coalesced. This ensures that the index remains equipartitioned with the table.
A local index can be created UNIQUE if the partitioning columns form a subset of the index columns. This restriction guarantees that rows with identical index keys always map into the same partition, where uniqueness violations can be detected.
Local indexes have the following advantages:
Oracle8i Supplied PL/SQL Packages Reference for a description of the DBMS_PCLXUTIL package
See Also:
A local index is prefixed if it is partitioned on a left prefix of the index columns.
For example, if the SALES table and its local index SALES_IX are partitioned on the WEEK_NUM column, then index SALES_IX is local prefixed if it is defined on the columns (WEEK_NUM,XACTION_NUM). On the other hand, if index SALES_IX is defined on column PRODUCT_NUM then it is not prefixed.
Figure 11-4 shows another example of a local prefixed index.
Local prefixed indexes can be unique or nonunique.
A local index is nonprefixed if it is not partitioned on a left prefix of the index columns.
You cannot have a unique local nonprefixed index unless the partitioning key is a subset of the index key.
Figure 11-5 shows an example of a local nonprefixed index.
In a global partitioned index, the keys in a particular index partition may refer to rows stored in more than one underlying table partition or subpartition. A global index can only be range-partitioned, but it can be defined on any type of partitioned table.
A global index is created by specifying the GLOBAL attribute. The database administrator is responsible for defining the initial partitioning of a global index at creation and for maintaining the partitioning over time. Index partitions can be merged or split as necessary.
Normally, a global index is not equipartitioned with the underlying table. There is nothing to prevent an index from being equipartitioned with the underlying table, but Oracle does not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations. So an index that is equipartitioned with the underlying table should be created as LOCAL.
A global partitioned index contains a single B-tree with entries for all rows in all partitions. Each index partition may contain keys that refer to many different partitions or subpartitions in the table.
The highest partition of a global index must have a partition bound all of whose values are MAXVALUE. This insures that all rows in the underlying table can be represented in the index.
A global partitioned index is prefixed if it is partitioned on a left prefix of the index columns. See Figure 11-6 for an example. A global partitioned index is nonprefixed if it is not partitioned on a left prefix of the index columns. Oracle does not support global nonprefixed partitioned indexes.
Global prefixed partitioned indexes can be unique or nonunique.
Nonpartitioned indexes are treated as global prefixed nonpartitioned indexes.
Global partitioned indexes are harder to manage than local indexes:
Table 11-1 summarizes the types of partitioned indexes that Oracle supports.
Table 11-1 Types of Partitioned Indexes
| Type of Index | Index Equipartitioned with Table | Index Partitioned on Left Prefix of Index Columns | UNIQUE Attribute Allowed | Example | ||
|---|---|---|---|---|---|---|
| Table Partitioning Key | Index Columns | Index Partitioning Key | ||||
|
Local Prefixed (any partitioning method) |
Yes |
Yes |
Yes |
A |
A,B |
A |
|
Local Nonprefixed (any partitioning method) |
Yes |
No |
Yes1 |
A |
B |
A |
|
Global Prefixed (range partitioning only) |
No2 |
Yes |
Yes |
A |
B |
B |
|
Global Nonprefixed3 |
-- |
-- |
-- |
-- |
-- |
-- |
Nonprefixed indexes are particularly useful in historical databases. In a table containing historical data, it is common for an index to be defined on one column to support the requirements of fast access by that column, but partitioned on another column (the same column as the underlying table) to support the time interval for rolling out old data and rolling in new data.
Consider the SALES table presented in Figure 11-1 ("SALES Table Partitioned by Week"). It contains a year's worth of data, divided into 13 partitions. It is range partitioned on WEEK_NO, four weeks to a partition. You might create a nonprefixed local index SALES_IX on SALES. The SALES_IX index is defined on ACCT_NO because there are queries that need fast access to the data by account number. However, it is partitioned on WEEK_NO to match the SALES table. Every four weeks the oldest partitions of SALES and SALES_IX are dropped and new ones are added.
It is more expensive to probe into a nonprefixed index than to probe into a prefixed index.
If an index is prefixed (either local or global) and Oracle is presented with a predicate involving the index columns, then partition pruning can restrict application of the predicate to a subset of the index partitions.
For example, in Figure 11-4 ("Local Prefixed Index"), if the predicate is DEPTNO=15, the optimizer knows to apply the predicate only to the second partition of the index. (If the predicate involves a bind variable, the optimizer will not know exactly which partition but it may still know there is only one partition involved, in which case at run time, only one index partition will be accessed.)
When an index is nonprefixed, Oracle often has to apply a predicate involving the index columns to all N index partitions. This is required to look up a single key, or to do an index range scan. For a range scan, Oracle must also combine information from N index partitions. For example, in Figure 11-5 ("Local Nonprefixed Index"), a local index is partitioned on CHKDATE with an index key on ACCTNO. If the predicate is ACCTNO=31, Oracle probes all 12 index partitions.
Of course, if there is also a predicate on the partitioning columns, then multiple index probes might not be necessary. Oracle takes advantage of the fact that a local index is equipartitioned with the underlying table to prune partitions based on the partition key. For example, if the predicate in Figure 11-5 is CHKDATE<3/97, Oracle only has to probe two partitions.
So for a nonprefixed index, if the partition key is a part of the WHERE clause but not of the index key, then the optimizer determines which index partitions to probe based on the underlying table partition.
When many queries and DML statements using keys of local, nonprefixed, indexes have to probe all index partitions, this effectively reduces the degree of partition independence provided by such indexes.
When deciding how to partition indexes on a table, consider the mix of applications that need to access the table. There is a trade-off between performance on the one hand and availability and manageability on the other. Here are some of the guidelines you should consider:
For example, a query using the predicate "ACCTNO between 40 and 45" on the table CHECKS of Figure 11-5 ("Local Nonprefixed Index") causes parallel scans of all the partitions of the nonprefixed index IX3. On the other hand, a query using the predicate "DEPTNO between 40 and 45" on the table DEPTNO of Figure 11-4 ("Local Prefixed Index") cannot be parallelized because it accesses a single partition of the prefixed index IX1.
Default physical attributes are initially specified when a CREATE INDEX statement creates a partitioned index. Since there is no segment corresponding to the partitioned index itself, these attributes are only used in derivation of physical attributes of member partitions. Default physical attributes can later be modified using ALTER INDEX MODIFY DEFAULT ATTRIBUTES.
Physical attributes of partitions created by CREATE INDEX are determined as follows:
Values of physical attributes specified (explicitly or by default) for the index are used whenever the value of a corresponding partition attribute is not specified. Handling of the TABLESPACE attribute of partitions of a LOCAL index constitutes an important exception to this rule in that in the absence of a user-specified TABLESPACE value, that of the corresponding partition of the underlying table is used.
Physical attributes (other than TABLESPACE, as explained above) of partitions of local indexes created in the course of processing ALTER TABLE ADD PARTITION are set to the default physical attributes of each index.
Physical attributes (other than TABLESPACE, as explained above) of index partitions created by ALTER TABLE SPLIT PARTITION are determined as follows:
Physical attributes of an existing index partition can be modified by ALTER INDEX MODIFY PARTITION and ALTER INDEX REBUILD PARTITION. Resulting attributes are determined as follows:
Values of physical attributes of the partition before the statement was issued are used whenever a new value is not specified. Note that ALTER INDEX REBUILD PARTITION can be used to change the tablespace in which a partition resides.
Physical attributes of global index partitions created by ALTER INDEX SPLIT PARTITION are determined as follows:
Values of physical attributes of the partition being split are used whenever a new value is not specified.
Physical attributes of all partitions of an index (along with default values) may be modified by ALTER INDEX, for example, ALTER INDEX indexname NOLOGGING changes the logging mode of all partitions of indexname to NOLOGGING.
|
See Also:
"Tablespace and Storage Attributes of LOB Index Partitions" for additional information about the physical attributes of LOB index partitions |
Tables that contain LOB columns can be partitioned. However, a partitioning key cannot contain a LOB column. The LOB data and LOB index segments of a LOB column are equipartitioned with the base table.
For every partition of a partitioned table that contains a LOB column, there is a LOB data segment for the LOB data partition and a LOB index segment for the LOB index partition. These data and index segments contain the LOBs that belong to the rows in that partition.
Similarly, for every subpartition of a composite-partitioned table that contains a LOB column, there is a LOB data segment for the LOB data subpartition and a LOB index segment for the LOB index subpartition. These data and index segments contain the LOBs that belong to the rows in that subpartition. In the following discussion, partition refers either to a partition of a range- or hash-partitioned table or index or to a subpartition of a composite-partitioned table or index.
Equipartitioning of LOB data and LOB index segments localizes the effects of maintenance operations, resulting in more efficient use of resources and improved availability of data.
The algorithm for determining a tablespace for a given LOB data partition is similar to that for determining a tablespace for a LOCAL index partition. In determining values of physical storage attributes other than TABLESPACE for LOB data partitions, Oracle uses the same algorithm that determines the values of physical attributes for table partitions. Note that you can explicitly specify the LOB storage characteristics for a specific LOB column at the partition level or at the table level.
The following rules determine the tablespace of a LOB data partition:
The following example illustrates these rules:
CREATE TABLE PT1 (A NUMBER, B BLOB, C CLOB, D CLOB) LOB (B,D) STORE AS (STORAGE (NEXT 15K)) LOB (C) STORE AS (TABLESPACE TSB) PARTITION BY RANGE (A) (PARTITION P VALUES LESS THAN (MAXVALUE) TABLESPACE TS1 LOB (B) STORE AS (TABLESPACE TSA), (Rule 1) LOB (C) STORE AS (PCTVERSION 20), (Rule 2) LOB (D) STORE AS (STORAGE (NEXT 10K))) (Rule 3) TABLESPACE TSX;
In this example, the tablespace for the LOB data partitions corresponding to partition P are determined as follows:
"Tablespace and Storage Attributes of LOB Index Partitions" for a discussion of how to determine a tablespace in which a LOB index partition is located
See Also:
The values of the storage attributes (other than TABLESPACE) for a LOB data partition are determined as follows:
See the next section for a discussion of how the storage attributes for a LOB index partitions are determined.
LOB index partitions always reside in the same tablespace as the LOB data partitions to which they correspond, that is, the LOB index partitions are co-located with the LOB data partitions. All other attributes of a LOB index partition are determined based on attributes of the LOB data partition to which they correspond and default attributes of the tablespace in which both the LOB data and its corresponding LOB index partition reside.
The following example shows how LOB index partitions collocate with the LOB data partitions to which they correspond:
CREATE TABLE PT1 (A NUMBER, B BLOB, C CLOB, D CLOB) LOB (B,D) STORE AS (STORAGE (NEXT 15K)) LOB (C) STORE AS (TABLESPACE TSB); PARTITION BY RANGE (A) (PARTITION P VALUES LESS THAN (MAXVALUE) TABLESPACE TS1 LOB (B) STORE AS (TABLESPACE TSA), (Rule 1) LOB (C) STORE AS (PCTVERSION 20), (Rule 2) LOB (D) STORE AS (STORAGE (NEXT 10K))) (Rule 3) TABLESPACE TSX;
In this example, the LOB index partitions that correspond to LOB data partitions associated with partition P are located in the following tablespaces:
The values of the storage attributes (other than TABLESPACE) for a LOB index partition are determined based on the values of attributes of a corresponding LOB data partition and the default attributes of the tablespace in which the LOB index partition is located.
Regular views on partitioned tables with LOB columns work the same way that they do on tables without LOB columns. Object views can also be created on top of partitioned tables with LOB columns.
The same view-based privilege checking is performed on LOBs selected from views on partitioned tables as for LOBs selected from views on nonpartitioned tables. The user must have privileges to access the LOB through the view from which the LOB locator is obtained (SELECTed). This view-based privilege checking is necessary for snapshots (that is, materialized views used for replication).
For BFILEs, only the LOB locator is stored in the table while the actual BFILE data exists in an external operating system file. Therefore the BFILE locator is what gets partitioned with the rest of the table, not the BFILE data. The BFILE locator is of varying length and stores the directory alias and file name along with other control information. Thus, a BFILE column in a partitioned table is similar to a VARCHAR2 column in a partitioned table.
You can partition an index-organized table by range of column values. An index-organized table differs from a heap-organized table in the following ways:
When partitioning an index-organized table, note the following:
Restricting the partitioning columns to a subset of the primary key columns ensures that when you insert a row into a partition, the uniqueness of the primary key can be verified by searching that partition. Without this restriction, it would be necessary to search other partitions as well, and so the partitions would not be independent of each other.
When the partitioning columns form a prefix of the primary key columns, the partition bounds form a sequence in primary key order. For queries that require data from more than one partition, a simple concatenation of resulting rows from each partition preserves the primary key order. This is the optimal way of partitioning an index-organized table.
When the partitioning columns do not form a prefix of the primary key columns, each partition's data is sorted in primary key order but selecting rows from more than one partition in primary key order requires a merge of the individually sorted partition rows.
If you want to partition an index-organized table on columns that are not a subset of the primary key columns, you can use this workaround:
For example, for an index-organized table that has columns A, B, and C with a primary key (A, B), if you want to partition the table on column C you should change the primary key to (A, B, C) and define a unique constraint on (A, B). Then an insert operation will insert the row into the target partition and insert the key values for (A,B) into a nonpartitioned index on (A,B), which verifies the uniqueness across all the partitions.
To create a partitioned index-organized table without row overflow, you need to specify ORGANIZATION INDEX at the table level only. All partitions inherit the ORGANIZATION INDEX property from the table.
You can specify default values for physical attributes at the table level and can override them at the partition level. These attributes apply to the primary key index segment that is created for each partition. The tablespace for an index segment can be specified at the partition level or at the table level. If it is not specified at either level, the user's default tablespace is used.
The following example shows the creation of an index-organized table with no row overflow:
CREATE TABLE orders( id NUMBER, odate DATE, ... PRIMARY KEY(id, odate)) ORGANIZATION INDEX PARTITION BY RANGE(odate) ( PARTITION p1 ... TABLESPACE q1, PARTITION p2 ... TABLESPACE q2);
In this example, the index organized table ORDERS is range partitioned on the ODATE column, with each partition stored in its own tablespace. No overflow is provided for.
The overflow option allows storing the tail portion of a row in an overflow data segment. The following are the key aspects for partitioned index-organized tables with overflow:
The following example shows the creation of a partitioned index-organized table with partitioned overflow stored in a single tablespace:
CREATE TABLE orders( id NUMBER, odate DATE, notes VARCHAR2(1000), ... PRIMARY KEY(id, odate)) ORGANIZATION INDEX INCLUDING odate OVERFLOW TABLESPACE all_overflow PARTITION BY RANGE(odate) ( PARTITION p1 ... TABLESPACE q1, PARTITION p2 ... TABLESPACE q2);
In this example, the table has a separate tablespace for overflow data segments. Even though they are stored in the same physical tablespace (ALL_OVERFLOW), the overflow data segments are partitioned on the same partition columns as used in the index-organized table. Note the use of the INCLUDING ODATE clause--this means that all data, past and including the ODATE column, will be stored in the overflow.
The following example shows the creation of a partitioned index-organized table with partitioned overflow stored in multiple tablespaces:
CREATE TABLE orders( id NUMBER, odate DATE, notes VARCHAR2(1000), ... PRIMARY KEY(id, odate)) ORGANIZATION INDEX INCLUDING odate PARTITION BY RANGE(odate) ( PARTITION p1 ... TABLESPACE q1 OVERFLOW TABLESPACE q1_overflow, PARTITION p2 ... TABLESPACE q2 OVERFLOW TABLESPACE q2_overflow);
In this example, each partitioned overflow segment is stored in its own tablespace.
You can create local prefixed, local non-prefixed, and global prefixed partitioned indexes on index-organized tables. Indexes on index-organized tables store primary key-based (logical) rowids as opposed to physical rowids, and may contain additional estimated data as part of the rowid to speed up secondary index-based access.
For accessing an index-organized table partition by its global partition index, Oracle identifies the partition based on the logical rowid. This is possible because the rowid contains primary key columns, which in turn contain all of the partitioning columns. Once the partition is identified, Oracle can use the estimate to directly access the leaf block that would hold the index row. If the estimate is invalid, an index scan on the relevant partition B-tree is required.
DML table locks synchronize DML statements (INSERT, UPDATE, and DELETE) with DDL statements and LOCK TABLE statements. DML table locks also synchronize DDL and LOCK TABLE statements among themselves. For partitioned or subpartitioned tables, Oracle uses DML partition locks or DML subpartition locks to provide partition independence for DDL and utility operations:
Partition independence or subpartition independence allows you to perform DDL and utility operations on selected partitions or subpartitions without reducing activity on other partitions or subpartitions.
A partition lock protects the data in an individual partition of a partitioned table while multiple users are accessing that partition or other partitions in the table concurrently.
Partition locks fall between table locks and row locks in the DML locking hierarchy:
Partition locks can be acquired in the same modes as table locks: Share (S), Exclusive (X), Row Share (SS), Row Exclusive (SX), and Share Row Exclusive (SSX).
|
See Also:
"Concurrency Model for Maintenance Operations" for more information about partition locking for DML and DDL statements |
When updating a LOB as a whole or only partially (using DBMS_LOB operations), in addition to acquiring a DML SX-lock on a partitioned table, Oracle acquires DML SX-lock(s) on one or more table partitions.
DML subpartition locks allow you to perform DDL and utility operations on selected subpartitions without reducing activity on other subpartitions of the same partition, as well as on subpartitions of other partitions.
A subpartition lock protects the data in an individual subpartition while multiple users are accessing that subpartition or other subpartitions in the same partition, or some subpartitions in other partitions of the table concurrently.
Oracle does not acquire DML partition locks when performing DML or DDL operations on composite partitioned tables:
As with partition locks, subpartition locks fall between table locks and row locks in the DML locking hierarchy:
Subpartition DML locks can be acquired in the same modes as table and partition DML locks: Share (S), Exclusive (X), Row Share (SS), Row Exclusive (SX), and Share Row Exclusive (SSX).
Introducing an extra level of DML locking may affect the performance of short transactions in the Oracle Parallel Server environment because extra messages are sent to the Distributed Lock Manager.
To improve performance in the Oracle Parallel Server environment, you can turn off DML locking on selected tables with the ALTER TABLE DISABLE TABLE LOCK statement, which disables both table and partition DML locks. DDL statements are not allowed when DML locking is disabled.
This section covers the following topics:
For the purposes of this chapter, a maintenance operation is a DDL statement that alters a table or index definition or a utility (such as Export, Import, or SQL*Loader) that performs bulk load or unload of data.
Most maintenance operations on nonpartitioned tables and indexes also work on partitioned tables and indexes. For example, DROP TABLE can drop a partitioned table, and Export can export a partitioned table. However, some maintenance operations must be performed on individual partitions rather than the whole partitioned table or index. For example, ALTER TABLE ALLOCATE EXTENT cannot be used for a range-partitioned table; instead, you use ALTER TABLE MODIFY PARTITION ALLOCATE EXTENT for the partition or partitions that need new extents.
Maintenance operations are considered fast if their expected duration is not affected by the size (number of records) of the schema objects they operate upon. Fast maintenance operations result only in dictionary and segment header changes, and do not cause data scans and data updates. They are expected to complete in a short time (order of seconds). For example, RENAME is a fast operation while CREATE INDEX is not a fast operation.
A partition maintenance operation modifies one partition of a partitioned table or index. For example, you might add a new partition to an existing table, or you might move a partition to a different tablespace for better I/O load balancing, or you might load a partition.
Some partition maintenance operations are planned events. For example, in a historical database, the database administrator (DBA) periodically drops the oldest partitions from the database and adds a set of new partitions. This drop and add operation occurs on a regularly scheduled basis. Another example of a planned maintenance operation is a periodic Export/Import to recluster data and reduce fragmentation.
Other partition maintenance operations are unplanned events, required to recover from application or system problems. For example, unexpected transaction activity may force the DBA to split a partition to rebalance I/O load, or the DBA may need to rebuild one or more index partitions.
The partition maintenance operations are:
See Also:
The concurrency model described in this section defines when it is possible to run more than one DDL and utility operation on the same schema object at the same time. It also defines which query and DML operations can be run concurrently with DDL and utility operations.
The model applies to all DDL statements. It also applies to utilities like SQL*Loader.
There are two types of maintenance operations: one-step and three-step.
One-step operations:
Three-step operations:
Finally, some operations may follow either one-step or three-step protocol:
If the table being altered has no globalthen statements in this group execute using the one-step protocol and they are fast. Otherwise, they execute using the three-step protocol. In the latter case, the base table is locked in Row Exclusive (SX) mode and the partition is locked in Exclusive (X) mode.
If the partition to be split is USABLE, then the statement follows the 3-step protocol, and partitions resulting from the SPLIT are USABLE. If, on the other hand, the partition being split is UNUSABLE, then the operation follows the 1-step protocol, and resulting partitions are also marked UNUSABLE.
If the partition to be dropped is USABLE, then the statement follows the three-step protocol. Otherwise it follows the one-step protocol.
Conventional Path SQL*Loader and Import use SQL INSERT so they are classified as DML operations for the purposes of the model. Export uses SQL SELECT so it is classified as a query operation.
The rules in this section can be derived from the definitions of one-step and three-step operations.
While a one-step operation is in progress:
Because queries (READ operations) do not take DML locks, queries are allowed on a partition which is being split or moved while the SPLIT or MOVE is being processed. However, the current segments are dropped at the end of the operation, and the space may be reused. An error is signalled if the space is reused.
While an ALTER TABLE MOVE PARTITION, ALTER TABLE SPLIT PARTITION, ALTER TABLE EXCHANGE PARTITION, or Direct Path Load Table Partition is in progress on a partition:
While a CREATE INDEX or ALTER INDEX REBUILD PARTITION or ALTER INDEX DROP/SPLIT PARTIITON applied to a USABLE partition for a global index is in progress:
While an ALTER INDEX REBUILD PARTITION for a local index is in progress on a partition which corresponds to an underlying table partition:
Some maintenance operations on a partition of a table cause the global indexes of the table or the index partitions to become UNUSABLE. An example is ALTER TABLE MOVE PARTITION. The DBA has to run a script that includes global index rebuilds in addition to the partition maintenance operation. Consequently from a user point of view these operations serialize access to the entire table. Operations such as ALTER TABLE MOVE/SPLIT PARTITION make UNUSABLE any nonpartitioned global indexes as well as all partitions of partitioned global indexes.
Note that table partition operations which mark all partitions of global indexes also mark one partition of local index (the partition corresponding to the table partition being operated on) UNUSABLE.
Similarly some partition maintenance operations require disabling Referential Integrity Constraints before the operation, and re-enabling them afterwards. An example is a ALTER TABLE DROP PARTITION of a nonempty partition. The DBA has to run a script that includes constraint re-enabling in addition to the partition maintenance operation. Consequently, from a user point of view these operations serialize access to the entire table.
Table 11-2 shows the operations that can be performed concurrently with maintenance operations on subpartitions.
Table partition maintenance operations handle partitioned tables with LOB columns as follows:
Addition of LOB columns to partitioned tables has no effect on the concurrency model for maintenance operations.
Queries whose execution starts before invocation of a partition maintenance operation, or before dictionary updates are done during a partition maintenance operation, correctly access via Consistent Read the data of the affected partitions as existing at query snapshot time. Such queries either successfully complete returning all relevant data as present at snapshot time, or fail to complete returning error ORA-8103 or ORA-1410. The application should reissue the query if one of these errors is returned.
Queries that use a partitioned index, and that start with some of the index partitions marked as INDEX UNUSABLE, return an error when they actually access one of these partitions for the first time. This happens even if the partition has been made USABLE after query start.
Although many of the new DDL statements are partition-based, cursor invalidation is still table-based. This means that any DDL statement that modifies table T also invalidates all cursors that depend on T, even if the statement affects only one partition P of T and the cursors do not access partition P.
All partition maintenance operations can be run in LOGGING mode. However, some operations support a NOLOGGING clause:
LOGGING is the default, except when the database operates in NOARCHIVELOG mode in which case NOLOGGING is the default. DDL and utility statements that do not support the LOGGING/NOLOGGING clause always run in recoverable mode (LOGGING).
|
Note: LOGGING or NOLOGGING is not an attribute of an operation but of a physical object. Hence, you cannot specify LOGGING or NOLOGGING in an INSERT statement. Instead, if you want to alter the logging mode of a table or indexes involved in an insert operation, you need to issue ALTER TABLE/INDEX [NO]LOGGING before issuing the INSERT statement. For more information, see "Logging Mode". |
You can always rename, change the physical storage attributes, or rebuild a partition of a local or global index. Changing how an index is partitioned must be handled differently depending on whether the index is local or global.
Oracle guarantees that the partitioning of a local index matches the partitioning of the underlying table. It does this by automatically creating or dropping index partitions as necessary when you alter the underlying table. You cannot explicitly add, drop, or split a partition in a local index.
For each local index:
Note that local index partitions produced as a result of splitting a parent table partition are marked UNUSABLE if a corresponding table partition is nonempty.
When Oracle creates a new local index partition by the ADD or SPLIT operations of the corresponding table partition:
Rather than dropping a local index partition explicitly (for example, before loading data into its corresponding table partition), you can:
"Partition and Subpartition Names" for more information about naming a new local index partition
See Also:
The DBA is responsible for maintaining the partitioning of a global index. You can drop or split a partition in a global index. However, you cannot add a partition to a global index because the high partition of a global index always has a partition bound of MAXVALUE.
|
See Also:
"Management of Global Partitioned Indexes" for more information on managing global indexes |
The ALTER INDEX REBUILD PARTITION statement can be used to regenerate a single partition in a local or global partitioned index. This saves you from having to perform DROP INDEX and then CREATE INDEX, which would affect all partitions in the index.
ALTER INDEX REBUILD PARTITION has four important applications:
Some maintenance operations mark indexes INDEX UNUSABLE (IU). INDEX UNUSABLE is an attribute of a nonpartitioned index and of a partition in a partitioned index. When an index or index partition is marked IU, you get an error if you try to execute a SELECT or DML statement that requires the index or partition.
When a single index partition is marked IU, you must rebuild the partition to make it valid again before using it. However, while one partition is marked IU the other partitions of the index are valid and you can execute SELECT or DML statements that require the index as long as the statements do not access the IU partition.
You can also split or rename the IU partition before rebuilding it, and you can drop an IU partition of a GLOBAL index.
When a nonpartitioned index is marked IU, you can drop the index. You can also drop an IU partition of a GLOBAL index.x and re-create it, and you can use ALTER INDEX REBUILD to rebuild a nonpartitioned index.
Six types of maintenance operations can mark index partitions INDEX UNUSABLE. In all cases, you must rebuild the index partitions when the operation is complete.
Privileges for partitions are granted on the parent table or index, not on individual partitions. If you want to grant access to a table on a per-partition basis, you can define a view on a partition of a table and then grant privileges on that view.
If a user or role has the privileges required to perform an Oracle operation on nonpartitioned tables and indexes, including the necessary resource privileges, then the same Oracle operations are allowed on partitioned tables and indexes. For example:
If a user or role has the privileges required to perform an ALTER operation on a table or index, then the ALTER operations on partitions of the table or index can be invoked, with some exceptions.
|
See Also:
|
All of the ALTER TABLE PARTITION operations are audited just like ALTER TABLE operations. No additional audit attributes are used for partitions.
You can perform bulk operations at the partition or subpartition level. That is, bulk operations can be restricted to just the rows of a particular partition or subpartition. For example, if you want to drop a partition without making all the global indexes UNUSABLE, you would want to delete all the rows from just that partition.
Such operations are very naturally expressed with a SQL extension that provides syntax for partition-extended table names and subpartition-extended table names. Trying to phrase the same operations with a WHERE clause predicate is often cumbersome, especially when a range partitioning key uses multiple columns.
The table specification syntax for the following DML statements can contain an optional PARTITION specification for partitioned tables, or an optional PARTITION or SUBPARTITION specification for composite-partitioned tables:
For example:
SELECT * FROM schema.table PARTITION part_name;
For a composite-partitioned table, using the PARTITION specification restricts the operation to data contained in all subpartitions of the specified partition.
The PARTITION or SUBPARTITION syntax for table specifications provides a simple way of viewing individual partitions or subpartitions as tables: You can use the partition-extended table name or subpartition-extended table name to create a view that selects from just one partition or subpartition, and this view can then be used in place of a table. For example:
CREATE VIEW sales_feb98_v1 AS SELECT * FROM sales SUBPARTITION (feb98_s1); SELECT * FROM sales_feb98_v1;
With such views you can also build partition-level or subpartition-level access control mechanisms by granting or revoking privileges on these views to or from other users or roles.
This section describes restrictions on the use of the PARTITION and SUBPARTITION clauses in table specifications, and provides examples of SQL statements that include the PARTITION or SUBPARTITION clause.
The use of partition- and subpartition-extended table names has the following restrictions:
A partition- or subpartition-extended table name cannot contain a database link or a synonym which translates to a table with a database link. If you need to use remote partitions or subpartitions, you can create a view at the remote site which uses the partition- or subpartition-extended table name syntax and refer to that remote view.
A SQL statement that has the partition- or subpartition-extended table name syntax cannot be used in a PL/SQL block, although it can be used through dynamic SQL with the DBMS_SQL package. If you need to refer to a partition or subpartition within a PL/SQL block, you can use views which in turn use the partition- or subpartition-extended table name syntax.
A partition or subpartition extension must be specified with a base table. No synonyms, views, or any other schema objects are allowed.
The following statements contain valid partition-extended table names:
SELECT * FROM sales PARTITION (nov97) s WHERE s.amount_of_sale > 1000; UPDATE sales PARTITION (feb98) s SET s.account_name = UPPER(s.account_name); DELETE FROM sales PARTITION (nov97) WHERE amount_of_sale != 0; INSERT INTO sales PARTITION (oct97) SELECT * FROM lastest_data; INSERT INTO sales PARTITION (oct97) VALUES (...); INSERT INTO sales PARTITION (oct97) (acct_no, ..., week_no) VALUES (...); LOCK TABLE sales PARTITION (jun98) IN EXCLUSIVE MODE; CREATE VIEW sales_feb98 AS SELECT * FROM sales PARTITION (feb98);
The following statements contain valid subpartition-extended table names:
SELECT * FROM sales SUBPARTITION (nov97_s1) s WHERE s.amount_of_sale > 1000; UPDATE sales SUBPARTITION (feb98_s4) s SET s.account_name = UPPER(s.account_name); DELETE FROM sales SUBPARTITION (nov97_s3) WHERE amount_of_sale != 0; INSERT INTO sales SUBPARTITION (oct97_s5) SELECT * FROM lastest_data; INSERT INTO sales SUBPARTITION (oct97_s2) VALUES (...); INSERT INTO sales SUBPARTITION (oct97_s4) (acct_no, ..., week_no) VALUES (...); LOCK TABLE sales SUBPARTITION (jun98_s1) IN EXCLUSIVE MODE; CREATE VIEW sales_feb98_1 AS SELECT * FROM sales SUBPARTITION (feb98_s1);
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|