Partitions in Oracle8

Wish you a very happy New Year 1999!

This new year, we'll discuss something new - Partitions in Oracle8. In Oracle7, we accomplished partitioning by creating multiple tables and building a view over the tables. We have something very useful in Oracle8.

What is Partitioning? (Excerpts from Oracle8 concepts manual)

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.

All partitions of a table or index have the same logical attributes, although their physical attributes can 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. However, storage specifications and other physical attributes such as PCTFREE, PCTUSED, INITRANS, and MAXTRANS can vary for different partitions of the same table or index. Each partition is stored in a separate segment. Optionally, you can store each partition in a separate tablespace, which has the following advantages:

The Oracle server incorporates the intelligence to explicitly recognize partitions. This knowledge is exploited in optimizing SQL statements to mark the partitions that need to be accessed, eliminating ("pruning") unnecessary partitions from access by those SQL statements. For each SQL statement, depending on the selection criteria specified, unneeded partitions can be eliminated. 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. The ability to prune unneeded partitions from SQL statements increases performance and availability for many purposes, including partition-level load, purge, backup, restore, reorganization, and index building.

How to create a partitioned table?

Creating partitions is very similar to creating a table or index: you must use the CREATE TABLE statement with the PARTITION CLAUSE. The first step to create a partitioned table would be to identify the columns to partition and the range of values which go to each partition. Then you determine the tablespaces where each partition should go. Since we create partitions for large tables, it is good to have separate tablespaces for each partition, which gives you the flexibility in backup, recovery and maintenance. Also for historical tables, you can make some of the partitions READ ONLY, and save time in backup. Here is a script to create a simple partitioned table.

CREATE TABLE AA_GENERAL_LEDGER
(GL_ACCT_MONTH NUMBER (4),
GL_BATCH VARCHAR2(4),
GL_JIB VARCHAR2(1),
... ... ... ...
GLR_OVER_UNDER_IND VARCHAR2(1))
PCTFREE 0 PCTUSED 40 INITRANS 1
STORAGE(INITIAL 250M NEXT 10M MINEXTENTS 1 MAXEXTENTS 1000 PCTINCREASE 0 )
PARTITION BY RANGE (GL_ACCT_MONTH)
(PARTITION AAGL7912 VALUES LESS THAN (8000) TABLESPACE AAGL7912
STORAGE (INITIAL 100M NEXT 10M PCTINCREASE 0),
PARTITION AAGL8012 VALUES LESS THAN (8100) TABLESPACE AAGL8012,
PARTITION AAGL8112 VALUES LESS THAN (8200) TABLESPACE AAGL8112,
PARTITION AAGL8212 VALUES LESS THAN (8300) TABLESPACE AAGL8212,
... ... ... ...
PARTITION AAGL9712 VALUES LESS THAN (9800) TABLESPACE AAGL9712 ,
PARTITION AAGL9801 VALUES LESS THAN (MAXVALUE) TABLESPACE AAGL9801
STORAGE (INITIAL 50M NEXT 5M PCTINCREASE 0)
);

Before issuing the command, all the tablespaces mentioned should be created and made available. Here the table is created with GL_ACCT_MONTH as the partition key. I call the partitions and the tablespaces the same name, since the tablespaces are specifically for these partitions. The partition AAGL7912 will have all the records with the GL_ACCT_MONTH value less than 8000, similarly the partition AAGL9801 has all the records with the GL_ACCT_MONTH value greater than or equal to 9800. Note that I have defined the storage parameter along with the table definition, these parameters will be used for all partitions except AAGL7912 and AAGL9801 because separate storage parameters are defined specifically for these partitions. MAXVALUE is a keyword to specify the upper partition range, which is equalent to inifinity. You can also specify LOGGING or NOLOGGING and TABLESPACE in the table level or partition level.

Export / Import with partitioned tables

Export and import work the same as a non-partitioned table with the partitioned table. In the table level export, you have the option of exporting the entire table with all partitions or to export a single partition. To export a single partition, specify the partition name followed by the table name, separated by a colon. Full database and user mode Export do not support partition-level Export; only table mode Export does. Because incremental Exports (incremental, cumulative, and complete) can be done only in full database mode, partition-level Export cannot be specified for incremental exports.

In this example, we export the partition AAGL8212 from table AA_GENERAL_LEDGER.

exp file=test.dmp log=test.log tables=AA.AA_GENERAL_LEDGER:AAGL8212

If you omit the partition name, all partitions in the table will be exported.

Import also can be done at a table level or partition level. If you import a partitioned table, and the table does not exist, import creates the table with partitions. To import data from a non-partitioned table to a partitioned table or vice-versa, create the table first and do the import with IGNORE=Y parameter.

Partition-level Import imports a set of partitions from a source table into a target table. Note the following points:

Creating indexes on partitioned tables

Partitioned indexes are more complicated than partitioned tables because there are four types of range-partitioned indexes: local prefixed, local non-prefixed, global prefixed, and global non-prefixed. These types are described below. Oracle supports three of the four types (global non-prefixed indexes are not useful in real applications).

Local Indexes

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 range-partitions the index on the same columns as the underlying table, creates the same number of partitions, and gives them the same partition bounds as corresponding partitions of the underlying table. Oracle also maintains the index partitioning automatically as partitions in the underlying table are added, dropped, or split. This ensures that the index remains equipartitioned with the table.

Equipartitioning a table and its index has the following advantages:

Local Prefixed Index: A local index is prefixed if it is partitioned on a left prefix of the index columns. For example, the index on columns (GL_ACCT_MONTH, GL_BATCH) is prefixed, since the partitioning on the table is with the column GL_ACCT_MONTH.

Local Non-prefixed Index: A local index is non-prefixed if it is not partitioned on a left prefix of the index columns. For example, the index on columns (GL_BATCH, GL_ACCT_MONTH) is non-prefixed.

Global Indexes

In a global index, the keys in a particular index partition may refer to rows stored in more than one underlying table partition. A global index is created by specifying the GLOBAL attribute (this is the default). The user is responsible for defining the initial partitioning of a global index at creation and for maintaining the partitioning over time. Index partitions can be dropped 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 index contains (conceptually) a single B*-tree with entries for all rows in all partitions. Each index partition may contain keys that refer to many different partitions 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.

Global Prefixed Index: A global index is prefixed if it is partitioned on a left prefix of the index columns. Global prefixed indexes can be unique or non-unique.

Global Non-prefixed Index: A global index is non-prefixed if it is not partitioned on a left prefix of the index columns. Oracle does not support global non-prefixed indexes.

Partitions in DML

The table specification syntax for the following DML statements may contain an optional partition specification for non-remote partitioned tables:

For example:

SELECT * FROM schema.table PARTITION (part_name);

This syntax provides a simple way of viewing individual partitions as tables: A view can be created which selects from just one partition using the partition-extended table name, and this view can be used in lieu of a table. With such views you can also build partition-level access control mechanisms by granting (revoking) privileges on these views to (from) other users or roles.

The use of partition-extended table names has the following restrictions:

In order to provide partition independence for DDL and utility operations, Oracle supports DML partition locks. Partition independence allows you to perform DDL and utility operations on selected partitions without quiescing activity on other partitions. The purpose of a partition lock is to protect the data in an individual partition 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

Maintaining Partitions

Moving Table Partitions

You can use the MOVE PARTITION clause to move a partition. For example, a DBA wishes to move the most active partition to a tablespace that resides on its own disk (in order to balance I/O). The DBA can issue the following statement:

ALTER TABLE parts MOVE PARTITION depot2
TABLESPACE ts094 NOLOGGING;

This statement always drops the partition's old segment and creates a new segment, even if you don't specify a new tablespace. When the partition you are moving contains data, MOVE PARTITION marks the matching partition in each local index, and all global index partitions as unusable. You must rebuild these index partitions after issuing MOVE PARTITION

Adding Table Partitions

You can use the ALTER TABLE ADD PARTITION statement to add a new partition to the "high" end (the point after the last existing partition). If you wish to add a partition at the beginning or in the middle of a table, or if the partition bound on the highest partition is MAXVALUE, you should instead use the SPLIT PARTITION statement. When the partition bound on the highest partition is anything other than MAXVALUE, you can add a partition using the ALTER TABLE ADD PARTITION statement.

For example, a DBA has a table, SALES, which contains data for the current month in addition to the previous 12 months. On January 1, 1999, the DBA adds a partition for January:

ALTER TABLE sales
ADD PARTITION jan99 VALUES LESS THAN ( '990201' )
TABLESPACE tsjan99;

When there are local indexes defined on the table and you issue the ALTER TABLE ... ADD PARTITION statement, a matching partition is also added to each local index. Since Oracle assigns names and default physical storage attributes to the new index partitions, you may wish to rename or alter them after the ADD operation is complete.

Dropping Table Partitions

You can use the ALTER TABLE DROP PARTITION statement to drop table partitions. If there are local indexes defined for the table, ALTER TABLE DROP PARTITION also drops the matching partition from each local index. You cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

If, however, the partition contains data and global indexes, and you leave the global indexes in place during the ALTER TABLE DROP PARTITION statement which marks all global index partitions unusable, so you must rebuild them afterwards.

Truncating Partitioned Tables

You can use the ALTER TABLE TRUNCATE PARTITION statement to remove all rows from a table partition with or without reclaiming space. If there are local indexes defined for this table, ALTER TABLE TRUNCATE PARTITION also truncates the matching partition from each local index.

Splitting Table Partitions

You can split a table partition by issuing the ALTER TABLE SPLIT PARTITION statement. If there are local indexes defined on the table, this statement also splits the matching partition in each local index. Because Oracle assigns system-generated names and default storage attributes to the new index partitions, you may wish to rename or alter these index partitions after splitting them.

If the partition you are splitting contains data, the ALTER TABLE SPLIT PARTITION statement marks the matching partitions (there are two) in each local index, as well as all global index partitions, as unusable. You must rebuild these index partitions after issuing the ALTER TABLE SPLIT PARTITION statement.

Exchanging Table Partitions

You can convert a partition into a non-partitioned table, and a table into a partition of a partitioned table by exchanging their data (and index) segments. Exchanging table partitions is most useful when you have an application using non-partitioned tables which you want to convert to partitions of a partitioned table.

Converting a Partition View into a Partitioned Table:

This scenario describes how to convert a partition view (also called "manual partition", usually Oracle7 databases) into a partitioned table. The partition view is defined as follows:

CREATE VIEW accounts
SELECT * FROM accounts_jan95
UNION ALL
SELECT * FROM accounts_feb95
UNION ALL
...
SELECT * FROM accounts_dec95;

To Incrementally Migrate the Partition View to a Partitioned Table (incrementally migrating reduces continuous non availability of the table):

Initially, only the two most recent partitions, ACCOUNTS_NOV95 and ACCOUNTS_DEC95, will be migrated from the view to the table by creating the partition table. Each partition gets a temporary segment of 2 blocks (as a placeholder).

CREATE TABLE accounts_new (...)
TABLESPACE ts_temp STORAGE (INITIAL 2)
PARTITION BY RANGE (opening_date)
(PARTITION jan95 VALUES LESS THAN ('950201'),
...
PARTITION dec95 VALUES LESS THAN ('960101'));

Use the EXCHANGE command to migrate the tables to the corresponding partitions.

ALTER TABLE accounts_new
EXCHANGE PARTITION nov95 WITH TABLE
accounts_95 WITH VALIDATION;

ALTER TABLE accounts_new
EXCHANGE PARTITION dec95 WITH TABLE
accounts_dec95 WITH VALIDATION;

So now the placeholder data segments associated with the NOV95 and DEC95 partitions have been exchanged with the data segments associated with the ACCOUNTS_NOV95 and ACCOUNTS_DEC95 tables.

Redefine the ACCOUNTS view.
CREATE OR REPLACE VIEW accounts
SELECT * FROM accounts_jan95
UNION ALL
SELECT * FROM accounts_feb_95
UNION ALL
...
UNION ALL
SELECT * FROM accounts_new PARTITION (nov95)
UNION ALL
SELECT * FROM accounts_new PARTITION (dec95);

Drop the ACCOUNTS_NOV95 and ACCOUNTS_DEC95 tables, which own the placeholder segments that were originally attached to the NOV95 and DEC95 partitions. After all the tables in the UNIONALL view are converted into partitions, drop the view and rename the partitioned table as the view.

DROP VIEW accounts;
RENAME accounts_new TO accounts;

Rebuilding Index Partitions

Some operations, such as ALTER TABLE DROP PARTITION, mark all partitions of a global index unusable. You can rebuild global index partitions in two ways:

1. Rebuild each partition by issuing the ALTER INDEX REBUILD PARTITION statement (you can run the rebuilds concurrently).
2. Drop the index and re-create it.

Merging partitions

Partition-level Export and Import provide a way to merge partitions in the same table, even though SQL does not explicitly support merging partitions. A DBA can use partition-level Import to merge a table partition into the next highest partition on the same table. To merge partitions, do an export of the partition you would like to merge, delete the partition and do an import.

For example, I have a table ORDERS, partitioned on ORDER_NO. The current partitions are P1 for 0 to 1000, P2 for 1001 to 2000, and P3 for 2001 to 3000. Now we want to merge the partitions P1 and P2. So we have partitions P2 and and P3 only. These are the steps:

1. export the partition P1

exp file=p1.dmp log=p1.log tables=x.orders:p1

2. Delete the partition from the table.

alter table orders drop partition P1;

3. Import data.

imp file=p1.dmp tables=orders:p1 ignore=y fromuser=x touser=x

Now the imported data goes to the partition P2, since the values of order number are less than 2001.

Data Dictionary Views

The following data dictionary views give information on Partitions.

Reference