Back Up Next

Chapter 21 *

Oracle8: Partitioning Tables and Indexes *

Overview of Partitioning *

Partitioning Definition and Rules *

Exercises *

Creating Partitioned Tables *

Exercises *

DDL and DML Parallelism and Partitioning *

Exercises *

Benefits of Partitioning *

Exercises *

Restrictions on Partitioning *

Exercises *

Implementing Partitioned Indexes *

Creating Partitioned Indexes *

Exercises *

Equipartitioning and Indexes *

Exercises *

Strategies for Partitioning Indexes *

Exercises *

Restrictions on Partitioned Indexes *

Exercises *

Modifying Partitioned Database Objects *

Altering Partitioned Tables *

Exercises *

Altering Partitioned Indexes *

Exercises *

Privileges and Dictionary Views for Partitioned Objects *

Exercises *

Using Oracle Utilities and Partitioned Objects *

Using EXPLAIN PLAN with Partitioned Objects *

Exercises *

Using SQL*Loader with Partitioned Objects *

Exercises *

Using EXPORT/IMPORT with Partitioned Objects *

Exercises *

Using ANALYZE with Partitioned Objects *

Exercises *

Chapter Summary *

Two-Minute Drill *

 

Unit V

Preparing for OCP DBA Exam 5: Oracle8 Features for Administrators

 

Chapter 21

Oracle8: Partitioning Tables and Indexes

In this chapter, you will understand and demonstrate knowledge in the following areas:

Overview of partitioning
Implementing partitioned indexes
Modifying partitioned tables and indexes
Using Oracle utilities and partitioned objects

OCP Exam 5 is Oracle’s newest addition to the OCP DBA Exam series. It is designed to test the DBA’s knowledge of Oracle8’s new features. Those DBAs who succeed in obtaining Oracle7 DBA certification in the OCP program can take the optional fifth exam to extend their certification into Oracle8. The next five chapters are designed to help DBAs take the OCP Exam 5 and obtain certification in Oracle8.

One of Oracle’s new features for Oracle8 is the use of partitioned tables and indexes. Recall in Chapter 20 the discussion of table striping in Oracle7. This configuration allows the DBA to maximize parallelism in the database while also managing the data in a table more actively. However, the methods for table striping available in Oracle7 are only available on tables. In contrast, Oracle8 allows both tables and indexes to be partitioned. Furthermore, the mechanisms for table striping are crude in comparison to the approach Oracle8 offers with respect to partitioning. In this chapter, you will cover several areas related to partitioning database objects, including the basic facts a DBA should know about partitioning tables and indexes. The syntactic requirements for partitioning tables and indexes is also covered, as are the benefits and restrictions for partitioning tables and indexes. Finally, the impact of partitioning database objects on utilities such as SQL*Loader, IMPORT/EXPORT, explain plan, and analyze are also covered in this chapter.

Overview of Partitioning

In this section, you will cover the following topics related to overview of partitioning:

Partitioning definition and rules
Creating partitioned tables
DDL and DML parallelism and partitioning
Benefits of partitioning
Restrictions on partitioning

This section introduces you to the methods and mechanisms of partitioning database objects. After introducing you to the basic reasons for using the feature, the section will cover the syntax and semantics of creating tables with partitions. Finally, the section will cover the benefits of partitioning database objects. This new feature of Oracle8 promises to be complex, yet the opportunities and potential for performance improvement will certainly outweigh the complexity of the feature. In addition, partitioning tables promises to be a substantial portion of OCP Exam 5.

Partitioning Definition and Rules

What is a partitioned table or index? Quite simply, a partitioned table or index is similar to the table and index a DBA already understands, with one exception—the partitioned table or index is divided into several "sections," or partitions, according to some criteria. These partitions can then be managed by the DBA in many ways to improve performance on certain database operations. For example, in Chapter 20 the DBA was introduced to table striping as a way to place table data on separate disks. Partitioning a table allows the same possibility, but with a few key enhancements--one of which is that the data in each partition can correspond to a specific value in a column or columns, such as alphabetical order. A table containing employee addresses may be partitioned according to the state where the employee lives, or according to the alphabetical order of the person’s last name. In contrast, table striping only allowed the DBA to divide the data in a table according to the size of the extent allocated for the table—a rather imprecise measurement.

Partitioning data in a table or index is based on a range of key values. From the addresses for employees of a company example, the key value may be the state of residence or the last name. Say for the example that table EMPLOYEE has three columns, EMPID, NAME, and STATE. The table can be partitioned five ways, based on the NAME column. Each partition can then be set to contain rows in several alphabetic ranges, such as A–G, H–N, O–R, S–T, and U–Z. Figure 21-1 gives a pictorial demonstration of this example for partitioning a table, though an index can be partitioned in the same way.

Fig21-01.jpg (17587 bytes)

Figure 1: Partitioned tables

Partitioning represents one sign of Oracle8’s commitment to support large relational databases, commonly referred to as VLDBs, or very large databases. VLDBs can be either online transaction processing or decision support systems, the characteristics of which were already discussed in Unit IV of the Guide. Although it may not benefit a database to partition a table with 50,000 rows of data, a table that has several million records storing gigabytes of data may well benefit from partitioning. Consider the following scenario: If a table with several million records is stored on one disk, the DBA may spend hours trying to recover it if that disk fails. In contrast, if that same table was partitioned and spread over several disks, the impact of one of those disks failing is less severe, and more recoverable. Breaking the largest tables in VLDBs into manageable chunks allows for database object reorganization on part of the object as well, which can cut DBA maintenance time. Partitioning allows for extremely large growth in the Oracle database—it supports up to 64,000 partitions in a large table.

One final point about partitioned tables and indexes to be made in this discussion relates to the partitioning combinations available between tables and indexes. A nonpartitioned table can have indexes that are partitioned, and a nonpartitioned index can be created on a table with partitions. In addition, the DBA can create both indexes and tables with partitions. In short, any combination of partitioned and nonpartitioned tables and indexes is allowed. The decision to partition a table or index depends on performance and design considerations, however, which will be discussed in this section and the next.

Exercises

  1. What is partitioning? How does it differ from table striping?
  2. How many partitions are permitted in tables and indexes?
  3. On what basis is data divided into partitions? What combinations of partitioned database objects are allowed in Oracle?

Creating Partitioned Tables

A partition of a table can be thought of as a miniature version of that table. Each partition is stored in different segments, thereby allowing the DBA to query the DBA_SEGMENTS view to find information about table partitions. Any single partition must be identical in column definition to any other partition in the table. For example, the EMPLOYEE table from before, with its EMPID, NAME, and STATE columns, cannot have a partition that has an extra column called SALARY. Additionally, no partition in the table may have a constraint that another partition does not have, so a column with a NOT NULL constraint in one partition cannot be nullable in another partition. Finally, the datatypes for all columns in the table must be the same for every attribute. The EMPID column for the table EMPLOYEES example cannot be declared as a VARCHAR2(10) column in one partition but a NUMBER(10) in another. All column definitions, constraints, and partition information (including the column or columns that can be used as the grounds for the partition) should be defined using the create table statement.

CREATE TABLE employees
(empid NUMBER(10) NOT NULL,
name VARCHAR2(30) NOT NULL,
state VARCHAR2 NOT NULL,
CONSTRAINT pk_employee_01
PRIMARY KEY (empid))
PARTITION BY RANGE (empid)
(PARTITION X1 VALUES LESS THAN (200000),
PARTITION X2 VALUES LESS THAN (400000),
PARTITION X3 VALUES LESS THAN (600000),
PARTITION X4 VALUES LESS THAN (800000),
PARTITION X5 VALUES LESS THAN (1000000));

Partitions need not have the same values for parameters set in the storage clause of each partition, however. Instead, the DBA can tailor the storage parameters of each partition in whatever way seems appropriate. These storage values include parameters discussed in earlier chapters like initial, next, and pctfree. In this way, the DBA can handle row storage on tables that allow records in static partitions to have a lower value set for pctfree in their partition while more dynamic partitions may have pctfree specified higher to prevent row migration due to update statements. Also, each partition can be placed in different tablespaces.

CREATE TABLE employees
(empid VARCHAR2 NOT NULL,
name VARCHAR2(30) NOT NULL,
state VARCHAR2 NOT NULL,
CONSTRAINT pk_employee_01
PRIMARY KEY (empid))
PARTITION BY RANGE (empid)
(PARTITION X1 VALUES LESS THAN (200000) tablespace data01
STORAGE (10M NEXT 10M PCTFREE 10 PCTUSED 40),
PARTITION X2 VALUES LESS THAN (400000) tablespace data02
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X3 VALUES LESS THAN (600000) tablespace data03
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X4 VALUES LESS THAN (800000) tablespace data04
STORAGE (10M NEXT 10M PCTFREE 20 PCTUSED 40),
PARTITION X5 VALUES LESS THAN (1000000) tablespace data05
STORAGE (10M NEXT 10M PCTFREE 5 PCTUSED 40));

There are several components to the partition specification, including the partition key, the partition range specification, and the tablespace placement and storage parameters. The syntax for specifying the partition key is the partition by range clause of the create table statement. The partition key can contain more than one column. If the key contains more than one column, then the data placed in each partition will depend on values in all columns of the partition key. The ranges used for partitioning table data are defined for each partition using the values less than (value) clause, and the value specified in the cannot include a data operation, save for one—the to_date( ) operation. For millennium compliance, it is best to use a four-character century indication in the character string that will be converted to a date. Thus, the ‘24-SEP-02’ specification is not sufficient, but ‘24-SEP-2002’ is. The value specified for the values less than clause is not included in the range of values allowed, and as such is referred to as the noninclusive upper bound. Observe that in some cases it may be difficult to define a noninclusive upper bound. For example, the values less than (‘Z’) clause will define a partition that does not accept the letter Z in the partition key. To avoid potential problems caused by this feature, the DBA can use the maxvalue keyword. Thus, the values less than (maxvalue) will encompass the letter Z in the partition. The lower bound for each partition range is either the lowest value permitted for the range or the value specified for the previous partition. If NULL is specified for a column in the partition key, the row data for that NULL column specified will be placed in the partition whose range includes maxvalue. If maxvalue is not specified for any partition range, then the NULL cannot be specified for the partition-key columns.

When creating multiple partitioned tables, or partitioned indexes that correspond to partitioned tables, the DBA may want to consider equipartitioning. Equipartitioning occurs when objects share the same partitioning definitions and column datatypes (including length, precision, and scale) for partition keys and ranges. These objects may have different storage parameters and their partitions may be stored in different tablespaces, however. Using equipartitioning helps to limit recovery and maintenance time for objects that are partitioned similarly, since other partitions can be accessed while recovery or maintenance takes place.

Each partition can be referenced according to its partition name as part of DML statements. The syntax for doing so involves the partition keyword along with the name of the partition. Thus, each partition in the EMPLOYEES table can be thought of as a miniature version of the table. The partition containing rows for employees whose EMPID is between 200,000 and 399,999 can be referenced as partition (partition_name). In addition, a view may be created to mask this syntax.

INSERT INTO employees PARTITION (X2)
VALUES (…);

CREATE VIEW empl
AS SELECT * FROM employees PARTITION X2;

Exercises

  1. What are the components of a partition statement? Which components of a table must be the same in all partitions? Which partition components may be different in each partition?
  2. What is equipartitioning?
  3. How are individual partitions accessed?

DDL and DML Parallelism and Partitioning

One of the primary benefits of partitioning is its ability to take advantage of new features in the Oracle8 architecture for improved parallelism in data definition operations. Recall from previous discussion that data definition operations are specified by the data definition language, or DDL. Statements that create database objects are part of the data definition language. The DDL statements that use parallelism include create table partition, create index partition, create table as select, alter table move partition, alter table split partition, and alter index rebuild partition. The parallelism used in the object creation statements listed correlates directly to the number of partitions specified for creation in the DDL statement. However, for the object maintenance operations specified, Oracle accesses individual partitions in parallel.

Some new locks are also available in Oracle8, which are used to manage change access to partitions. These new partition locks allow the users to lock a table by partition, leaving other partitions available for change by other users. The partition locks are acquired when Oracle executes the cursor, as opposed to table locks, which are acquired at the time the cursor is parsed. For smaller transactions, however, this additional level of locking may hinder performance. To avoid that performance hit, the DBA can execute the alter table name disable table lock statement, which disables partition locks in addition to table locks, as implied by its syntax. However, while table locks are disabled, no alter statements are allowed when DML locks are disabled.

Performance has also been facilitated for statements designed to modify partitioned database objects with the use of a processing structure called the one-step operation. DDL statements that use this operation include create table statements, alter table add partition, the more general alter table add for column or constraint addition, and other operations. These operations execute quickly, but may not allow other operations in the database to run at the same time because of the higher level of locking required to complete the operation. DDL statements that use the one-step operation lock the table exclusively, thereby making other processes wait until the DDL statement is complete. Although select statements will continue to access the object, update, insert, and delete statements will have to wait until the one-step operation is complete.

The other processing structure is the three-step operation. This operation is used for many alter table operations like alter table action partition, where action may be any of the following actions for changing table partitions: split, modify, exchange, drop, or move. Other statements that use three-step operations are create index (global only), alter index rebuild partition (global only), and others. These operations take longer to execute than one-step operations such as DDL operations, but often allow other processes to operate on the object at the same time due to less restrictive share locking. The three steps in a three-step operation are as follows. First, Oracle will read the dictionary, then it will acquire the appropriate lock on a partition and make its change, and finally it will update the dictionary.

Exercises

  1. What are the benefits of executing DDL and DML statements in parallel in Oracle8?
  2. What new locks are available in Oracle8 to support partitions?
  3. Define a one-step operation. What are some one-step operations?
  4. Define a three-step operation. What are some three-step operations?

Benefits of Partitioning

There are several benefits for partitioning objects. For example, a partitioned table allows the DBA to manage each partition of the table independently of other partitions. Thus, the availability of data on a partitioned table is higher than its nonpartitioned counterpart. In addition, a situation where disk failure damages only one partition of an object will not damage the entire object, and the rest of the partitions can be available for use while the DBA recovers the damaged partition.

The DBA will also enjoy some improvements in the administration of a partitioned object. Recall that the storage parameters and tablespace location for a partitioned object can be different for each partition of that object. Partitions can also be moved from tablespace to tablespace as needed. In addition, the partition can have activities performed on it that previously were limited to objects as a whole. These activities include truncation of a partition, dropping a partition, or adding a partition. In addition, a partition can be further subdivided without disrupting other partitions in the table.

Finally, the partitioning of database objects improves performance of SQL statements against those objects. For example, if a user attempts to select data from a table that cannot be found in a particular partition according to the definition of that partition, the SQL statement processing mechanism in Oracle8 can eliminate that partition immediately. In addition, parallel processing of data in partitioned objects is improved, allowing Oracle to find requested data in partitioned objects faster than in nonpartitioned objects. This performance improvement is available in select, update, insert, and delete statements. Also, since partitions can be placed in different tablespaces, the DBA can eliminate I/O bottlenecks easily by placing partitions on different disks as well. And sort operations on queries on partitioned objects will require smaller temporary space allocation, due to the fact that the sort is applied to partitions rather than to the entire table.

Exercises

  1. How does partitioning improve management and maintenance on a database object?
  2. How does partitioning improve the availability of data on a database object?
  3. How does partitioning improve performance in obtaining or changing data in an object?

Restrictions on Partitioning

There are several restrictions on partitioned tables. Every possible value for the column on which the partition key is defined must be accommodated by a partition. Thus, ranges of A–P, Q–Z are allowed, but ranges A–O, R–Z are not. In addition, NULL cannot be specified for a column in the partition key unless a partition is defined to accept values less than maxvalue. An insert on the table will fail if the value specified for the partition is outside any range specified for any partition on the table. The partitioned table may not contain a column declared with the LONG or LONG RAW datatypes, or any column declared with the large object (LOB) datatypes new to Oracle8. For more information on the new large object datatypes, refer to Chapter 23.

There is another restriction on partitioned tables related to changes in the partition-key values. Sometimes, it may be difficult to update the data in a partition key. The value in a partition-key column cannot be changed if the change will cause the row to move partitions. Thus, there is some data change allowed in partitioned tables, but the changes in partition-key values cannot be too drastic. If the DBA anticipates the data in a column will change, the DBA may not want to choose that column as the partition key.

There are some restrictions on referencing partitions individually in SQL and PL/SQL. First, the individual partitions of the table cannot be referenced through a database link or a synonym. However, table partitions can be referenced individually if a view has been created on the individual partition. Also, a PL/SQL block may not contain SQL that refers directly to a partition in a table. However, the user may create dynamic SQL using the DBMS_SQL package that references individual partitions in PL/SQL blocks, or may use views that reference the individual partition in the table. Finally, only table partitions may only be directly referenced.

Exercises

  1. What restrictions apply to specifying the partition key? Can gaps appear in partition definitions? Explain.
  2. What datatype restrictions exist on partitioned tables?
  3. What restrictions on partition references exist in SQL and PL/SQL? Can a partition be referenced via a database link? Explain.

Implementing Partitioned Indexes

In this section, you will cover the following topics related to implementing partitioned indexes:

Creating partitioned indexes
Equipartitioning and indexes
Strategies for partitioning indexes
Restrictions on partitioning indexes

In addition to tables, indexes on tables may be partitioned. This section will discuss the various topics of partitioning indexes in the Oracle8 database. The different types of partitioned indexes will be identified and explained, along with the syntax requirements for creating them. This section will also discuss strategies for using the various types of partitioned indexes available in Oracle8, along with the situations that justify the use of each. Finally, the restrictions on each of the partitioned indexes will be presented in this section. As with partitioning tables, understanding of index partitioning is an important new concept in Oracle database administration.

Creating Partitioned Indexes

As with tables, an index can be either partitioned or not partitioned. Since indexes on a table are never as large as the table itself, the motivation for partitioning an index for better storage management may not be as acute as it is with tables. However, there are still some compelling reasons to partition indexes, which will be covered.

In general, the DBA must answer several questions in order to determine if she should partition an index. One consideration in partitioning an index is the type of access to data required by applications and users. Partitioning indexes allows for more flexibility in terms of how users and applications can access the data. As with tables, partitioned indexes allow for a higher degree of data availability. The data in one index partition remains available in the event that another partition becomes unavailable. Management and maintenance on large indexes also run faster on partitions, leaving more data available during management or maintenance operations.

Partitioned indexes offer a higher degree of parallelism than their nonindexed counterparts as well. As with tables, the partitions of the index can be distributed over multiple tablespaces on different disks to eliminate I/O bottlenecks and improve data access. This distribution has the added benefit of reducing the dependency of a partitioned index on any one disk storing its partitions. If one disk fails, only the partition on that disk is lost, and only that data remains unavailable while the DBA recovers the partition. In general, partitioning indexes offers the same benefits for parallelism that partitioning tables offers.

There are four general categories of database indexes available in Oracle. They are nonpartitioned indexes, global prefixed indexes, local prefixed indexes, and local nonprefixed indexes. Each category has its own approach to improving overall performance, availability, and maintenance of the index. As with tables, each partition in a partitioned index can be thought of as a miniature version of the entire index, complete with its own definition, B-tree structure, and data, and stored in its own segment. A nonpartitioned index has already been covered. It is the most basic type of B-tree index that is available in Oracle7. The rest of the options available in Oracle8 covered in this discussion are partitioned indexes.

Partitions in indexes, like partitions in tables, must have a partition key, used to divide the contents of the index into ranges. The categorization of an index as prefixed or nonprefixed is related to the order of columns in the composite index and the columns used in the partition key. Recall that any index can have more than one column in it. In order for SQL select statements to use the index in the query, both the columns specified and the order specified in the where clause must correspond directly with the position of the column in the index. Special consideration must be given to determine if an index is prefixed. An index is prefixed if it is partitioned on a left prefix of the index columns. For example, if the EMPLOYEES table and its local index X_EMPLOYEES_01 are partitioned on the STATE column, then index X_EMPLOYEES_01 is local prefixed if it is defined on the columns (STATE, EMPID). On the other hand, if index X_EMPLOYEES_01 is defined on column NAME, then it is not prefixed. Partitioned indexes containing only one column are automatically considered prefixed. The performance advantage provided by prefixed unique indexes is that these indexes allow select statements against the index to access only one partition in order to find the data. If this prefixed index contains nonunique values, the index will still search only one partition for the requested data if the where clause specifies a value for all columns in the index. If not, then all partitions of the index must be scanned. The following block contains a definition of the table and its associated global partitioned index:

CREATE TABLE employees
(empid VARCHAR2 NOT NULL,
name VARCHAR2(30) NOT NULL,
state VARCHAR2 NOT NULL,
CONSTRAINT pk_employee_01
PRIMARY KEY (empid))
PARTITION BY RANGE (empid)
(PARTITION X1 VALUES LESS THAN (200000) tablespace data01
STORAGE (10M NEXT 10M PCTFREE 10 PCTUSED 40),
PARTITION X2 VALUES LESS THAN (400000) tablespace data02
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X3 VALUES LESS THAN (600000) tablespace data03
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X4 VALUES LESS THAN (800000) tablespace data04
STORAGE (10M NEXT 10M PCTFREE 20 PCTUSED 40),
PARTITION X5 VALUES LESS THAN (1000000) tablespace data05
STORAGE (10M NEXT 10M PCTFREE 5 PCTUSED 40));

CREATE INDEX x_emp_01
ON employees (empid, name)
GLOBAL
PARTITION BY RANGE (empid)
(PARTITION ix1 VALUES LESS THAN (300000),
PARTITION ix2 VALUES LESS THAN (700000),
PARTITION ix3 VALUES LESS THAN (MAXVALUE))

Discussing prefixed indexes gives rise to a question—what about indexes where the partition key does not correspond to the columns indexed? This question is the definition of a nonprefixed index. The index may be partitioned based on range, but the partition is based on the partition key rather than on the left-most column on the index. The performance gains using this index are somewhat different. First, if select statements want to access data in the nonprefixed index, the index and the where clause must contain the partition key as part of the index key to guarantee uniqueness. If these conditions are met, then only the partition containing requested data will be searched. If the index is nonunique, then Oracle will need to search all partitions of the index in order to get the data requested. The following block contains the definition of the partitioned table and its local, nonprefixed, nonpartitioned index:

CREATE TABLE employees
(empid VARCHAR2 NOT NULL,
name VARCHAR2(30) NOT NULL,
state VARCHAR2 NOT NULL,
CONSTRAINT pk_employee_01
PRIMARY KEY (empid))
PARTITION BY RANGE (empid)
(PARTITION X1 VALUES LESS THAN (200000) tablespace data01
STORAGE (10M NEXT 10M PCTFREE 10 PCTUSED 40),
PARTITION X2 VALUES LESS THAN (400000) tablespace data02
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X3 VALUES LESS THAN (600000) tablespace data03
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X4 VALUES LESS THAN (800000) tablespace data04
STORAGE (10M NEXT 10M PCTFREE 20 PCTUSED 40),
PARTITION X5 VALUES LESS THAN (1000000) tablespace data05
STORAGE (10M NEXT 10M PCTFREE 5 PCTUSED 40));

CREATE INDEX x_employees_01
ON employees (name)
LOCAL;

Exercises

  1. What is the difference between a partitioned index and a nonpartitioned index?
  2. What is the difference between a prefixed index and a nonprefixed index?
  3. What are the four types of indexes?

Equipartitioning and Indexes

Notice that each of the previous code blocks used to create an index contains new keywords: global and local. Either of these keywords can be used in the create index statement to denote the equipartitioning status of the index and its underlying table. Recall that equipartitioning is when two or more objects specify identical partitioning information, including keys and ranges. When the DBA specifies the global keyword for the index, there is no relationship between the partitions of the index and the partitions of the table--even if those partition definitions are identical. Thus, there is no equipartitioning relationship between the global index and the table. When the DBA specifies the local keyword for the index, Oracle will maintain an equipartitioning relationship between the index and the table. All rows in a table partition will have an entry in the corresponding index partition. If there is a change in table data, or a maintenance operation on a table partition, the effect on the associated index will be limited to the corresponding partition. Thus, partition independence is as prevalent in the use of index partitions as it was in the discussion of table partitions. In addition, a DBA creating the equipartitioned index can leave out the name of the partition on the indexes, and Oracle will automatically name the index partitions the same thing as their table partition counterparts.

In the following case, the partitioned table counterpart is demonstrated in the previous section. Prefixing the partition key to the index key on local indexes is often useful in searching for data on large systems in parallel based on the index key, while nonprefixed local indexes provide good search performance on columns in the partition key. The DBA should be aware of a few issues related to nonprefixed indexes and prefixed indexes. First, the nonprefixed index takes more resources to scan than a prefixed index. This is the case because Oracle can usually eliminate partitions from being scanned if the index is prefixed, but cannot if the index is nonprefixed. The following block contains the definition of the table with its local, equipartitioned index:

CREATE TABLE employees
(empid VARCHAR2 NOT NULL,
name VARCHAR2(30) NOT NULL,
state VARCHAR2 NOT NULL,
CONSTRAINT pk_employee_01
PRIMARY KEY (empid))
PARTITION BY RANGE (empid)
(PARTITION X1 VALUES LESS THAN (200000) tablespace data01
STORAGE (10M NEXT 10M PCTFREE 10 PCTUSED 40),
PARTITION X2 VALUES LESS THAN (400000) tablespace data02
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X3 VALUES LESS THAN (600000) tablespace data03
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X4 VALUES LESS THAN (800000) tablespace data04
STORAGE (10M NEXT 10M PCTFREE 20 PCTUSED 40),
PARTITION X5 VALUES LESS THAN (1000000) tablespace data05
STORAGE (10M NEXT 10M PCTFREE 5 PCTUSED 40));

CREATE INDEX x_employees_01
ON employees (empid)
LOCAL
(PARTITION xe1 TABLESPACE index_01,
PARTITION xe2 TABLESPACE index_02,
PARTITION xe3 TABLESPACE index_03,
PARTITION xe4 TABLESPACE index_04,
PARTITION xe5 TABLESPACE index_05);

The other alternative for DBAs is to create a global index--that is, an index that is not equipartitioned with its underlying table. The global index is created with the global keyword. When the DBA creates a global index, she forgoes the advantages of Oracle’s automatic maintenance of the equipartitioning relationship between the table and index—even if the partition definitions for both objects are the same. It must also be remembered that the highest partition in the global index needs to be specified with the maxvalue keyword in order to allow the highest partition to catch all errant values specified for the partition key column. Usually, the SQL execution mechanism is not able to eliminate partitions from being scanned unless all columns in the partition key are specified in the where clause of the select statement. In general, global indexes are less effective than local indexes because when the associated table is modified, all partitions of a global index are affected. Finally, all global indexes are prefixed. Creating a global partitioned index on the EMPLOYEES table can be accomplished with the second statement in the following code block:

CREATE TABLE employees
(empid VARCHAR2 NOT NULL,
name VARCHAR2(30) NOT NULL,
state VARCHAR2 NOT NULL,
CONSTRAINT pk_employee_01
PRIMARY KEY (empid))
PARTITION BY RANGE (empid)
(PARTITION X1 VALUES LESS THAN (200000) tablespace data01
STORAGE (10M NEXT 10M PCTFREE 10 PCTUSED 40),
PARTITION X2 VALUES LESS THAN (400000) tablespace data02
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X3 VALUES LESS THAN (600000) tablespace data03
STORAGE (10M NEXT 10M PCTFREE 15 PCTUSED 40),
PARTITION X4 VALUES LESS THAN (800000) tablespace data04
STORAGE (10M NEXT 10M PCTFREE 20 PCTUSED 40),
PARTITION X5 VALUES LESS THAN (1000000) tablespace data05
STORAGE (10M NEXT 10M PCTFREE 5 PCTUSED 40));

CREATE INDEX x_employees_01
ON employees (empid, name)
GLOBAL
PARTITION BY RANGE (empid)
(PARTITION ex1 VALUES LESS THAN (500000),
PARTITION ex2 VALUES LESS THAN (MAXVALUE));

Exercises

  1. How does a local index take advantage of equipartitioning? What are some of the advantages local indexes offer?
  2. What is a global index? What are the equipartitioning issues related to global indexes? What value must be specified in the highest partition to define the range of values stored in that partition?

Strategies for Partitioning Indexes

With so many options for defining partition keys and index keys with respect to one another, and whether to use equipartitioning on the index and corresponding table, the DBA may wonder—what strategies are available for partitioning indexes? The answer is that there are several issues that must be answered in order to determine what indexes to use on the database. The questions a DBA must answer are as follows:

Is (are) the table’s partition-key column in the same order as the index columns?
Will the index be used to search for data on a column not in the partition key?
Can the DBA accept additional performance work by the index in order to allow for the high availability inherent in partitioning?
Is the index to be used on decision support or OLTP systems?

If the answer to the question of whether a table’s partition-key columns are in the same order as the indexed-key columns is yes, then the DBA can consider using local prefixed indexes. These indexes offer high performance, high availability, and ease of use for the DBA—in short, the local prefixed index allows the DBA to use all the advantages partitioning has to offer. Alternately, if the index will be used to search for data on a column not in the partition key, the DBA can create a global prefixed index. When doing so, the DBA sacrifices some of the ease in management inherent with partition independence, but user query performance will still be high.

If the DBA wants to index columns that are not in the partition key, but still wants to maintain the ease of use and high data availability that partition independence offers, the DBA can create a nonprefixed local index. Often, this option can be used in decision support systems such as data warehouses, where users frequently want to execute select statements on arbitrary search conditions that don’t follow lines of partitioning. The nonprefixed local index works well when the application needs a nonunique index that conforms to lines of partitioning also. If, however, the DBA is supporting an OLTP system, a global prefixed index may be the answer. While the local nonprefixed index gives higher data availability and partition independence, the global prefixed index maximizes search performance for the select statement.

Exercises

  1. What index can be used to support tables when the partition key is the same as the index key?
  2. What index can be used to support tables where indexes are required on a column not part of the partition key?
  3. What index can be used to support nonunique columns when the DBA still wants to have partition independence? What is partition independence?

Restrictions on Partitioned Indexes

With respect to creating partitioned indexes, there are certain types of indexes that may not be partitioned. One of those indexes is the index that is used in an index cluster. Another is a bitmap index. Other than that, all B-tree indexes in Oracle7 can be partitioned. Beware of partitioning incorrectly, however. A well-meaning DBA can create performance issues on the database by using the wrong type of partition. In general, if the DBA can partition the table safely, the DBA may first want to consider if it is possible to create a local prefixed index to use equipartitioning. If not, the DBA should carefully consider the other partitioned index options available.

Several situations may arise on the database that put partitions of an index in a damaged state called INDEX UNUSABLE. When an index partition goes into this state, the DBA must rebuild the partition. Tools like IMPORT and SQL*Loader may leave an index partition in this state if either the direct path load fails in SQL*Loader or if the conventional path load or IMPORT is manually set to bypass updates on the partitioned index. In addition, when the DBA moves a table partition such that ROWIDs for data in the partition must change with the alter table move partition statement, the associated index partition will be left in the INDEX UNUSABLE state. The same thing will happen if the DBA truncates a table partition with the alter table truncate partition statement, as long as the underlying table had data in it. If the DBA splits a table partition with the alter table split partition statement, the associated index partition will be redefined but not rebuilt, thus leaving the partition in the INDEX UNUSABLE state. The same situation occurs when the alter index split partition statement is executed. These six situations require the DBA to execute an index partition maintenance operation on local equipartitioned indexes with alter table modify partition rebuild unusable local indexes. For global indexes or local nonprefixed indexes in the INDEX UNUSABLE state, the DBA must use the alter index rebuild partition statement. More information about the maintenance and modification of partitioned tables and indexes will be covered in the next section.

Exercises

  1. What is the INDEX UNUSABLE state?
  2. What situations involving SQL*Loader and IMPORT will put an index partition into the INDEX UNUSABLE state? What situations involving table operations will put an index partition into the INDEX UNUSABLE state?
  3. What statement will the DBA use to repair the partitions in the INDEX UNUSABLE state?

Modifying Partitioned Database Objects

In this section, you will cover the following topics related to modifying partitioned database objects:

Altering partitioned tables
Altering partitioned indexes
Privileges and restrictions for altering partitioned objects
Using dictionary views for partition reference

Once created, the DBA will be required to maintain the partitioned database objects available in Oracle8. Data may be added more quickly to one partition than to another, requiring the DBA to split that partition into two or more components. Data may be removed systematically from partitions on tables whose partition key is based on date of insert. The DBA may simply need information about the partitions on a table or index, requiring knowledge of the new dictionary views used to identify partition information for the database. This section covers these areas of Oracle8 administration of partitioned tables. The discussions in this section include altering partitions on tables and indexes, rebuilding partitioned indexes, rebuilding index partitions, and the privileges required to execute partition modification.

Altering Partitioned Tables

Partitions can be added to a table in the following way. First, the DBA must remember that partitions cannot be added to the table in the middle of the range—they can only be added onto the high end of the table. It usually works best when the DBA can add partitions based on a partition key that can increase over time, like a sequential number or a date. The following alter table add partition statement demonstrates adding partitions to the EMPLOYEES table where EMPID is 1,000,000 to 1,200,000:

ALTER TABLE employees
ADD PARTITION X6 VALUES LESS THAN (1200000) TABLESPACE data_06;

In addition, the DBA can rename a partition, from something obscure to something meaningful. Consider the create table statement presented in the first section used to create the EMPLOYEES table. The partitions on that table were named X1 through X5, which have little intrinsic meaning. The DBA may want to rename those partitions with more meaningful identifiers. The statement used to rename a table partition is alter table rename partition, and it is used as follows:

ALTER TABLE employees
RENAME PARTITION X1 TO emps_lt200000;

The DBA may also want to drop a partition on the database. Dropping a partition eliminates the partition definition and the data, and all data added to the table that would have been stored in the dropped partition will then be stored in the next highest partition. However, when the highest partition is dropped, information that would have been placed in that partition may no longer be added to the table. Dropping a table partition renders associated entire global indexes unusable. The syntax for dropping a partition is alter table drop partition, listed here:

ALTER TABLE employee
DROP PARTITION X1;

Sometimes a DBA may wonder if it is better to delete all data from a partition before dropping the partition. When a global index is defined against the table, dropping a partition in the table will render the entire index unusable. If these indexes must remain available, the DBA must determine how many rows will be removed from the table (and thus the index) as a result of dropping the partition. If the number of rows removed from the table as a result of deleting the partition is high in proportion to the overall number of rows in the table, the DBA should probably drop the partition without deleting the data from it (and thus from the index). This is because the resource cost and time required to delete the data, plus the cost of rebuilding the index, will likely be higher than would be required simply to drop the partition and rebuild the global index. But, if the partition contains only a small number of rows in proportion to the overall table, it may be worth the while of the DBA to delete the rows from the partition before dropping it to save time later when rebuilding the index. Finally, the DBA may be unable to drop a partition if it is part of a parent table on which referential integrity constraints exist without first disabling the constraints or cascading the data removal into the child table.

If the DBA finds that one partition is growing at a rate much faster than others, she can split the partition into smaller partitions to balance the load of that table on any one disk. To do this, the DBA must use the alter table split partition statement. Splitting the partition creates a new partition named according to a value set in the statement. If the original partition contains data that will be moved into the new partition, then the corresponding local index partition will be unusable. However, if no data is present in the partition, then the resultant partitions in the index will both be valid. The index partition can be rebuilt with the alter index rebuild partition. A statement splitting partitions on the EMPLOYEES table appears in the following code block:

ALTER TABLE employees SPLIT PARTITION X5
AT (900000)
INTO (PARTITION X5A TABLESPACE data_05,
PARTITION X5B TABLESPACE data_07);

The DBA can move a partition from one data segment to another, either in the current tablespace to reduce fragmentation or to a new tablespace to balance I/O load, using the alter table move partition statement. As discussed, moving a partition will render associated local index partitions and entire global indexes unusable. The usage of the alter table move partition statement is demonstrated in the following code block:

ALTER TABLE employees MOVE PARTITION X2
TABLESPACE data_08;

At some point in the existence of a partitioned table, the DBA may want to spin off a partition to its own table. Alternately, the DBA may want to convert a nonpartitioned table into the partition of another table. This type of partition exchange is accomplished with the use of alter table exchange partition. The exchange can be done in either direction, so long as the nonpartitioned table becoming a partition in a larger table is not clustered--and their logical definitions, including columns and constraints, must be identical. No data is moved, but the data dictionary information on both tables is modified, and all the global indexes associated with the partitioned table and any partitioned indexes on the nonpartitioned table will be rendered unusable. Corresponding local index partitions can be exchanged at the DBA’s discretion with the use of the including indexes clause, or left unusable with the excluding indexes clause. The alter table exchange partition statement can be used to create tables out of partitions as follows:

ALTER TABLE employees
EXCHANGE PARTITION X1
WITH TABLE emps_lt200000
EXCLUDING INDEXES;

ALTER TABLE employees
EXCHANGE PARTITION X2
WITH TABLE emps_lt400000
INCLUDING INDEXES;

Recall from earlier discussion that the DBA can eliminate all data from a table quickly using the alter table truncate statement. This same option is available for partitions in Oracle8. Truncating a partition will render all global indexes on that table unusable if the table contained data. If not, then the index remains in VALID state. Local indexes will have the corresponding partition truncated as well, which has an added bonus of resetting the status of the index partition to VALID if it had previously been unusable. The storage for the partition can either be reused by the partition or returned to the tablespace as free, using the reuse storage or drop storage clauses, respectively. An example for truncating a table partition with the alter table truncate statement appears in the following code block:

ALTER TABLE employees
TRUNCATE PARTITION X4
DROP STORAGE;

ALTER TABLE employees
TRUNCATE PARTITION X4
REUSE STORAGE;

The final statement available for modifying a table allows the DBA to modify many aspects of specified partitions on the table, such as physical storage parameters. The general statement that does all these things is the alter table modify partition. In addition, the DBA can modify the storage parameters for the entire table with the alter table statement. However, these physical storage parameters will not be incorporated into an existing partition; only partitions added will be impacted. The usage of the alter table modify partition statement to change physical storage parameters is as follows:

ALTER TABLE employees
MODIFY PARTITION X4
STORAGE (NEXT 10M MAXEXTENTS 20);

There are some other features about alter table modify partition that allow the DBA to perform activities related to the local partitioned indexes. It was mentioned earlier that the DBA can rebuild an index partition associated with a table partition when the index partition is in INDEX UNUSABLE state with the alter table modify partition rebuild unusable local indexes statement. In addition, the DBA can invalidate an index partition to defer maintenance on the index during data operations such as large table loads with the alter table modify partition unusable local indexes. Examples for using both these statements appear in the following code block:

ALTER TABLE employees
MODIFY PARTITION X3
UNUSABLE LOCAL INDEXES;

ALTER TABLE employees
MODIFY PARTITION X3
REBUILD UNUSABLE LOCAL INDEXES;

The last area of this discussion concerns changing column or constraint definition on the entire table. There are several restrictions on this activity, many of which center around modification of the column in the partition key. The datatype and the length of the partition-key column on the table cannot be changed, nor can this information be changed on columns used to partition indexes on the table. The partitioned table may not contain any columns defined with LONG, LONG RAW, or LOB datatypes. Finally, if any partition in the table is in a read only tablespace, a new column with a default value specified may not be added, nor can a column declared to be type VARCHAR2 be changed to type CHAR, nor can the length of a CHAR column be increased. To modify any other aspect of the table’s column or constraint definition, the alter table statement may be used as it was in Oracle7.

ALTER TABLE employees
ADD (salary number(10));

Exercises

  1. Which of the alter table statements in this section will cause an index to go into the INDEX UNUSABLE state?
  2. A table has three partitions. Partition A contains low range data, partition B contains midrange data, and partition C contains high range data. The DBA drops partition B. Later, a user inserts data that would have been placed in partition B had that partition still existed. Into which partition will Oracle place the data?
  3. In the situation described in question 2, what will happen if partition C is dropped and the same event occurs?
  4. What restrictions apply to changing column definitions on partitioned tables? What about restrictions on datatypes and partitioned tables? What about restrictions on tables where partitions are stored in read only tablespaces?

Altering Partitioned Indexes

Like tables, partitioned indexes can have many modifications made to them. The first that will be covered in this discussion is dropping index partitions. This is accomplished with the alter index drop partition statement. Once dropped, data that would have gone into that partition now goes into the next highest partition. Only global partitioned indexes can be dropped using this statement, and even then the highest partition cannot be dropped. Dropping an index partition effectively removes the index data that was stored in the partition. The statement used to drop an index partition is demonstrated in the following code block:

ALTER INDEX x_emp_01
DROP PARTITION ix1;

In addition, the DBA can rename a partition, from something obscure to something meaningful. The partitions on this index were named IX1 through IX3, which have little intrinsic meaning. The DBA may want to rename those partitions with more meaningful identifiers. The statement used to rename a table partition is alter table rename partition, and it is used as follows:

ALTER TABLE employees
RENAME PARTITION ix1 TO emps_lt300000;

If the DBA finds that one partition is growing at a rate much faster than others, the DBA can split the partition into smaller partitions to balance the load of that index on any one disk. Only global indexes may be split. To do this, the DBA must use the alter index split partition statement. Splitting the partition creates a new partition named according to a value set in the statement. If the partition split was unusable at the time of the split, then both partitions produced will also be unusable, and must be rebuilt. If the original partition was empty, then both partitions will be rebuilt. The index partition can be rebuilt with the alter index rebuild partition. A statement splitting partitions on an index appears in the following code block:

ALTER INDEX x_emp_01 SPLIT PARTITION ix1
AT (150000)
INTO (PARTITION ix1a TABLESPACE index_01
PARTITION ix1b TABLESPACE index_04);

Physical storage parameters for indexes can be changed with the alter index modify partition statement. Alternately, this statement can be used to change the status of an index to unusable. Both global and local partitioned indexes can have physical attributes or status changed with this feature. In addition, all partitions of an index can be marked as unusable with the alter index unusable statement. These statements are used as follows:

ALTER INDEX x_emp_01
MODIFY PARTITION ix1b
STORAGE (NEXT 5M);

ALTER INDEX x_emp_01
MODIFY PARTITION ix1a
UNUSABLE;

ALTER INDEX x_emp_01
UNUSABLE;

The usage of the alter index rebuild partition statement has already been explained. Any partitioned index can be rebuilt using this statement, allowing other partitions to continue undisturbed while the DBA handles maintenance. In some cases, however, it may run faster for the DBA to drop and re-create the index, particularly with global partitioned indexes. Optionally, the partition can be rebuilt in a different tablespace with the use of the tablespace keyword. Use of alter index rebuild partition is detailed in the following code block:

ALTER INDEX x_emp_01
REBUILD PARTITION ix1b;

ALTER INDEX x_emp_01
REBUILD PARTITION ix1b;
TABLESPACE index_05;

The parallelism with which Oracle will search for data in the index can also be set. The statement used for this process is the alter index parallel statement. Within this statement, there are two options that should be set: degree and instances. The degree option specifies how many processes can be set up to search through partitions for data. The instances option specifies how many instances may search in parallel for data. The total number of processes that may ever be used to search for data in parallel is degree * instances. Usage of the alter index parallel statement appears in the following code block:

ALTER INDEX x_emp_01
PARALLEL (DEGREE 5 INSTANCES 2);

The final area of index management that will be covered is the use of the skip_unusable_indexes option for the session. This parameter is defined as TRUE or FALSE for the current session. When it is set to TRUE, Oracle will allow the user to execute insert, update, and delete operations on table partitions whose corresponding nonunique indexes are in an unusable state. In this way, the user can make large modifications to data, deferring the index modification to a later point in time. To change the value for this parameter, use the alter session set skip_unusable_indexes = true statement.

ALTER SESSION
SET SKIP_UNUSABLE_INDEXES = TRUE;

Exercises

  1. How does the DBA re-enable the use of an index partition after the partition is put into INDEX UNUSABLE status?
  2. What are two uses for the alter index modify partition statement?
  3. If the user would like to insert data on a table with a nonunique index in INDEX UNUSABLE state, what parameter must be set for the session, and how?

Privileges and Dictionary Views for Partitioned Objects

No new privileges are introduced in Oracle8 for the management of partitions. In general, the same privileges available for Oracle7 used to create tables and indexes are used in Oracle8 to create nonpartitioned and partitioned tables. The same applies to dropping tables, except for dropping or truncating partitions on tables the user doesn’t own, for which the drop any table privilege is required. To alter table and index partitions, the privileges required are alter table (or index) action partition, where action can be modify, rebuild, split, add, or move.

In addition, there are several dictionary views that can be used to identify information about the partitioned table or index. Some of the dictionary views have already been presented, such as DBA_TABLES, DBA_INDEXES, DBA_TAB_COLUMNS, DBA_OBJECTS. Other views are new, and their uses are listed here:

DBA_PART_TABLES Gives information about how the table is partitioned for all tables in the database, including partition keys
DBA_PART_INDEXES Gives information about how the index is partitioned for all tables in the database, including partition keys
DBA_PART_KEY_COLUMNS Identifies the partition key used for all tables and indexes in the database
DBA_TAB_PARTITIONS Identifies information about the partitions of all tables in the database
DBA_IND_PARTITIONS Identifies information about the partitions of all indexes in the database
DBA_PART_COL_STATISTICS Identifies statistics for cost-based optimization for partition columns for all tables and indexes in the database
DBA_TAB_COL_STATISTICS Identifies statistics for cost-based optimization for table columns for all tables in the database
DBA_PART_HISTOGRAMS Shows the distribution of data in partitions for all partitions in the database
DBA_TAB_HISTOGRAMS Shows the distribution of the data in tables for all tables in the database

Exercises

  1. Which new dictionary views give statistical information for index and table partition statistics for cost-based optimization?
  2. In which new dictionary views can the DBA find information about the partition key and the partition range for tables and indexes?

Using Oracle Utilities and Partitioned Objects

In this section, you will cover the following topics for using Oracle utilities and partitioned objects:

Using explain plan with partitioned objects
Using SQL*Loader with partitioned objects
Using EXPORT/IMPORT with partitioned objects
Using analyze with partitioned objects

The addition of partitions to the Oracle table and index architecture is a major enhancement designed to support large database applications like OLTP systems and data warehouses. This addition of partitions presents some changes in the way some utilities manage table and index data. The affected utilities are those that DBAs use most frequently in conjunction with tables and indexes, such as IMPORT, EXPORT, and SQL*Loader. Since partitions affect the ways users obtain data from tables and indexes, some changes were made to utilities like analyze and explain plan as well. This section will present the changes made to several utilities as a result of the addition of partitions.

Using EXPLAIN PLAN with Partitioned Objects

The addition of partitions to the Oracle table and index architecture indicates some underlying changes to the methods Oracle will use to obtain data from those partitioned objects. New operations are used in the SQL processing mechanism, and to reflect these changes in the processing of SQL statements, the explain plan utility has been changed as well. The first change made is to the PLAN_TABLE. As discussed earlier, the PLAN_TABLE is used to store execution plan information generated by Oracle when a user requests this information. The new columns are PARTITION_START, PARTITION_STOP, and PARTITION_ID. The first two columns named are used to show the user which partitions Oracle will scan for the SQL statement undergoing explain plan activity, while the third shows the step that produced the values for the first two columns. An explanation of each column is listed below:

PARTITION_START Shows the start partition of the range of accessed partitions
PARTITION_STOP Shows the stop partition of the range of accessed partitions
PARTITION_ID Shows the step that determines the values for the start and stop columns

In addition, there are some new operations that users of the explain plan feature will notice when creating execution plans for Oracle SQL statements that access partitioned tables and indexes. The new internal Oracle operation is called partition. Also, the existing table access and index operations have been modified to reflect the use of partitions. For the OPTIONS column currently existing in the PLAN_TABLE, some new values may appear in relation to partitions. These new values are concatenated, single, and empty. These values will appear as part of the execution plan in relation to the operation Oracle must execute to obtain data from the database. The first, concatenated, means that the partition step had to concatenate the results of several partitions in order to search for data. The second, single, means that the partition step will access only one partition to find requested data. The third, empty, indicates the partition step found no partitions to access.

For the table access operation, there are several new values possible for the OPTIONS column. These options are by user ROWID, by index ROWID, by global index ROWID, and by local index ROWID. These operations identify the new indexes available in Oracle8 as the source for ROWID information.

Exercises

  1. What new columns have been added to the PLAN_TABLE for supporting execution plans for partitioned tables and indexes?
  2. What is the new operation used in explain plan for handling partitions? What are the new options available with that operation? What are the new options available for the TABLE ACCESS operation?

Using SQL*Loader with Partitioned Objects

A few changes are noteworthy with respect to SQL*Loader and partitioned objects. For the conventional path, the DBA can load data to each partition as necessary. In addition, SQL*Loader can run multiple loads at the same time for different partitions of the same table. In the control file, the DBA must identify the partition and the table to be loaded. Row data that does not belong in the partition will be placed in the bad file for later load to another partition. SQL*Loader also handles loads to all partitions of the table at the same time using the direct path. The corresponding indexes to the partitioned table being loaded are created automatically, but only one load may be run of this type on the same table at the same time. Finally, there are some changes to the direct path data load. First, a parallel direct load on one partition can be run at the same time as a parallel load on another partition. An additional parameter is included for the direct path parallel load, PARALLEL=TRUE. Global indexes should be dropped before engaging in the direct path parallel load, and the corresponding local index is rendered unusable.

Exercises

  1. What changes to support partitions have been made for the SQL*Loader conventional path load? How many loads can run at the same time on the same table with five partitions?
  2. What changes have been made to load all partitions of a partitioned table with SQL*Loader?
  3. What option is available for improved direct path load performance using parallelism?

Using EXPORT/IMPORT with Partitioned Objects

The following changes have been made for EXPORT to handle partitions. An entire partitioned table can be exported in all modes, namely table, user, and full. However, a single partition can be exported only in table mode, specified by the following format: table:partition. For IMPORT, the following changes are made. The DBA can import data from a database export dump file for a partitioned table into the database as a nonpartitioned table, and vice-versa. By default, IMPORT will create a partitioned table if the EXPORT was of a partitioned table. IMPORT operates in all modes, including table, user, and full. IMPORT can also use the skip_unusable_indexes option discussed earlier.

Exercises

  1. What new features for EXPORT have been added to support partitioned tables?
  2. What new features of IMPORT have been added to support partitioned tables?

Using ANALYZE with Partitioned Objects

The final tool considered is the analyze command. The DBA can collect statistics for a single table or index partition using analyze. The analyze command can also validate the structure of a single table or index partition. Histograms can also be produced per partition. The analyze command can even perform the list chained rows feature on only one partition. As before, analyze can still perform its analysis on all partitions of a table or index. This is the default functionality if a specific partition is not specified, even if the index or table is partitioned.

ANALYZE INDEX ix1b
COMPUTE STATISTICS FOR ALL COLUMNS;

ANALYZE TABLE employees PARTITION X3
LIST CHAINED ROWS

Exercises

  1. What new syntax is required in the analyze command for analyzing only the partitions of a table or index?
  2. If analyze is executed on an index that is partitioned and no specific partition is specified, what part of the index will analyze work on?

Chapter Summary

This chapter covered a major new feature for Oracle8, that of partitioning tables. This area affects both the selection and change of data, as well as the administration of the indexes and tables that store data. The areas of creating partitioned tables and indexes were covered, along with the modification of those partitions once the table or index is created. The chapter discussed the benefits of partitioning tables and indexes, and the effect these new features for tables and indexes have on several utilities, such as explain plan, SQL*Loader, analyze, and IMPORT/EXPORT.

The first section covered the topic of why to use partitioning. There are several benefits for partitioning tables. Especially for large tables and indexes on data warehouses and other large-volume data systems, partitions of a table or index can be much easier to manage during maintenance operations than the entire table or index itself. This ease of maintenance translates into shorter maintenance cycles and increased data availability for the large database system, because the rest of the partitions in a table or index will continue to be available even if one of the partitions is unavailable. Partitioned tables and indexes can be thought of as miniature versions of that same object, because the column and constraint definition for each partition of the table or index must be identical, although the storage parameters and tablespace location can be different for each partition. Also, the availability of each partition is independent of the availability of any other partition in the table or index, creating partition independence. Many different combinations of partitioned indexes to partitioned tables are allowed, although the most efficient combination is usually to have a partitioned table with an equipartitioned local index. Equipartitioning is when two or more objects share the same partition definition, and where data in the partition of one object appears in the partition of another object.

Equipartitioning is specified with the creation of indexes with the local clause. Equipartitioned database tables and their corresponding indexes have partition independence from other partitions in the database table or index to which they belong, but they also have a special relationship to the corresponding partition in the other database object. The two partitions in different database objects are related in that their partition keys and ranges are the same, and the data in one partition will have a corresponding data record in the other partition. Also, if something happens to the availability of data in the partition of the table, the availability of data in the corresponding index partition will likely be impacted.

The DBA partitions tables in the following way. The create table statement includes the column and constraint definition, as well as all components of the partition definition. There are three parts to the partition definition. They are the partition key, the partition range identifiers, and the partition physical storage clause. The partition key is specified with the partition by range (column) clause, where column equals the column used for the partition key. Usually, it is best to specify the partition key to be a column that doesn’t allow NULL values. The partition key is then used to define the various value ranges that will be put into each partition. This is defined with the values less than (value) clause, where value equals the high end of the range permitted for that partition. The implied low end of that range is the value specified as the high end for the previous range, or the lowest possible value for the column. The highest partition has the high-end value for the column defined as its high-end range. This can either be an actual value for the column or the maxvalue keyword, which is a catchall keyword stating that all values for this column that do not fit in another partition go into this one. Finally, the tablespace clause used for partition storage can be named after the values less than (value) clause, and the physical storage options such as pctfree, pctused, initial, and next can be specified in the storage clause.

There are some restrictions on partitioning tables. First, a table cannot be partitioned if it has a column defined with the LONG, LONG RAW, or the new large object (LOB) datatypes. Also, data cannot be inserted into a partitioned table or index if the value for the partition key is not in the range specified by the partition key. No partition can have a column or constraint that another partition for the same table does not also have. The column used for the partition key cannot have its datatype changed. However, the combinations of partitioned tables and indexes are unrestricted—a partitioned table can have nonpartitioned indexes or partitioned indexes, or even both, and a nonpartitioned table can have either nonpartitioned or partitioned indexes, or even both.

Partitioned indexes can be created with the create index statement. As with tables, the index is partitioned with the same three parts, a partition key specified with partition by range and the range specified by values less than. As with tables, the physical storage of index partitions can be different from partition to partition. There are four types of partitions: nonpartitioned indexes (already discussed and understood from Oracle7), local prefixed indexes, local nonprefixed indexes, and global prefixed indexes. The two general items that distinguish indexes are global or local, and prefixed or nonprefixed. A global index is one that is not equipartitioned according to its associated table (even if the partitions in both objects are identical), while a local index is equipartitioned with its table. Prefixing or nonprefixing indicates the relationship between the partition key and the overall columns in the index. An index is prefixed if it is partitioned on a left prefix of the index columns. For example, if the EMPLOYEES table and its local index X_EMPLOYEES_01 are partitioned on the STATE column, then index X_EMPLOYEES_01 is local prefixed if it is defined on the columns (STATE, EMPID). On the other hand, if index X_EMPLOYEES_01 is defined on column NAME, then it is not prefixed. A partitioned index with only one column is by definition prefixed, because the one column in the partition key is also the only column in the index key, and therefore leftmost. In contrast, if the column of the partition key of the index are not in the same order as the overall order of columns in the index, then the index is nonprefixed. An index partition may become unusable if the associated table partition containing data is truncated, dropped, split, or otherwise modified.

Tables can be modified with the alter table action partition statement, where action can be replaced with split to divide one partition into two, drop to eliminate the partition, add to create another partition, or rename to change the name of the partition. Also, it can be replaced with truncate to delete all records from the partition or move to move a partition into another extent in the current or different tablespace. It can also be replaced with exchange to change a partition into a separate table or vice versa, or modify to change physical storage parameters (but not the tablespace) or to rebuild an unusable index. The alter table statement is used to add columns and modify column datatypes

Index partitions can be modified with the alter index action partition. Several operations are possible, including drop to drop the partition, rename to change the name of the partition, rebuild to fix an index partition that is no longer usable, modify to change the storage parameters for the index, split to divide the partition into two or more parts, parallel to set the parallelism of the index, or unusable to set the index as unusable.

In addition to the dictionary views used to obtain information about tables and indexes, the following new dictionary views support use of partitions. DBA_PART_TABLES gives information about how the table is partitioned for all tables in the database. DBA_PART_INDEXES gives information about how the index is partitioned for all tables in the database. DBA_PART_KEY_COLUMNS identifies the partition key used for all tables and indexes in the database. DBA_TAB_PARTITIONS offers information about the partitions of all tables in the database. DBA_IND_PARTITIONS gives information about the partitions of all indexes in the database. DBA_PART_COL_STATISTICS lists statistics for cost-based optimization for partition columns, for all tables and indexes in the database. DBA_TAB_COL_STATISTICS offers statistics for cost-based optimization for table columns, for all tables in the database. DBA_PART_HISTOGRAMS shows the distribution of data in partitions for all partitions in the database. DBA_TAB_HISTOGRAMS shows the distribution of the data in tables for all tables in the database.

Several utilities were changed to accommodate partitions. They include explain plan, analyze, SQL*Loader, IMPORT and EXPORT. For explain plan, three new columns were added to the PLAN_TABLE, called PARTITION_START, PARTITION_STOP, and PARTITION_ID. A new operation called partition was added, along with three new options for its execution, concatenated (several partitions were put together to be searched for requested data), single (only one partition will be searched for requested data), and empty (the partition operation produced no partitions to search). Some new options for the table access operation were added as well, corresponding to the new indexes that are available. The options for TABLE ACCESS are by user ROWID, by index ROWID, by global index ROWID, and by local index ROWID.

For SQL*Loader, there are changes to the conventional path and the direct path. For conventional path, SQL*Loader may load one partition only, but several loads can operate on the same table but different partitions to execute data loads on partitioned tables more quickly. For the direct path, SQL*Loader allows the PARALLEL parameter, which is set to TRUE or FALSE depending on whether the DBA wants to load an individual partition using the direct path in parallel. If PARALLEL is used, then SQL*Loader will load records to one partition with multiple I/O processes running in parallel. Multiple loads can run on the same table but different partitions to load other partitions in parallel as well. Finally, if the DBA wants to load all partitions using SQL*Loader, she can do so with the direct path load, where SQL*Loader will load each partition sequentially. Only one load can run on the table or index at a time if this method is used.

For IMPORT and EXPORT, entire partitioned tables can be imported or exported, or an individual partition can be exported and then imported as a nonpartitioned table. Partition exports can only be done in EXPORT table mode. Partitions for tables must be specified as table:partition. For analyze, both tables and individual partitions can have this utility run on them, but to use the utility on a partition, only the partition must be named explicitly. Otherwise, the entire table will be analyzed.

Two-Minute Drill

Tables and indexes in Oracle8 can be partitioned.
Table and index partitions are defined with three new parts to the create table and create index statements: partition by range (column) to define the partition key, values less than (value) to define the upper bound for each partition subrange, and tablespace location and storage parameters. Only the storage parameters need be preceded by the storage clause.
Tables and indexes can have up to 64,000 partitions.
Oracle8 can store up to 512 petabytes of data.
No table containing a column defined to be type LONG, LONG RAW, or any of the new LOB datatypes can be partitioned.
A table’s partitions can be altered in several ways.
The alter table drop partition statement drops a named partition and its contents.
The alter table add partition statement adds a partition over and above the highest range currently existing on a partition in the table.
The alter table rename partition statement renames a partition from one thing to another.
The alter table modify partition statement sets the equipartitioned local index data to INDEX UNUSABLE status, or allows the equipartitioned local index to be rebuilt, or allows the DBA to change physical storage parameters (but not tablespace location).
The alter table truncate partition statement deletes all data from the table partition.
The alter table split partition statement splits one partition into two.
The alter table move partition statement moves the partition to another extent in the same tablespace or into another tablespace.
The alter table exchange partition statement turns a partition into its own table, and vice versa.
The following restrictions apply to changing column and constraint definitions on a partitioned table.
The partition key’s datatype or size cannot be changed.
All values for the partition-key column must be accommodated by a partition, thus ranges of A–P, Q–Z are allowed, but ranges A–O, R–Z are not.
If no partition defined for the partitioned object contains values less than (maxvalue), the partition-key column cannot contain NULL values.
An insert on the table will fail if the value specified for the partition is outside any range specified for any partition on the table.
The partitioned table may not contain a column declared with the LONG or LONG RAW datatypes, or any column declared with the large object (LOB) datatypes new to Oracle8.
The value in a partition-key column cannot be changed if the change will cause the row to move partitions.
The individual partitions of the table cannot be referenced through a database link or a synonym.
A PL/SQL block may not contain SQL that refers directly to a partition in a table. However, the user may create dynamic SQL using the DBMS_SQL package that references individual partitions in PL/SQL blocks, or use views that reference the individual partition in the table. Only table partitions may be directly referenced, not index partitions.
The alter index drop partition statement drops the named partition and its contents.
The alter index rename partition statement renames the partition.
The alter index rebuild partition statement fixes an INDEX UNUSABLE index partition.
The alter index modify partition statement changes physical storage parameters for a partition.
The alter index split partition statement splits one partition into two.
The alter index unusable statement makes an index partition unusable.
The alter index parallel statement defines parallelism for the index.
The explain plan utility has several new features to support partitioned tables.
A new operation exists called partition that handles searching partitions.
Three options for partition operation are concatenated, single, and empty.
Three new columns for the PLAN_TABLE are PARTITION_START, PARTITION_STOP, and PARTITION_ID.
EXPORT and IMPORT can handle partitioned tables. EXPORT can create export files containing only the named partition if used in table mode.
The analyze statement can be executed either individual partitions or on entire partitioned database objects.
SQL*Loader can run multiple conventional or direct path loads on different partitions in the object. For direct path loads, the load on each partition can be executed using the PARALLEL=TRUE parameter, which sets the direct load to run in parallel when loading one partition.
To load all partitions of the table with one load, SQL*Loader allows a sequential load. Only one sequential load can operate on a partitioned table at one time.