3 Partition Administration

Creating a Range Partitioned Table

The following example creates a table of four partitions, one for each quarter of sales. The columns sale_year, sale_month, and sale_day are the partitioning columns, while their values constitute the partitioning key of a specific row. The VALUES LESS THAN clause determines the partition bound: rows with partitioning key values that compare less than the ordered list of values specified by the clause are stored in the partition. Each partition is given a name (sales_q1, sales_q2, ...), and each partition is contained in a separate tablespace (tsa, tsb, ...).

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

  )

 PARTITION BY RANGE (time_id)

 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

    TABLESPACE tsa

 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

    TABLESPACE tsb

 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

    TABLESPACE tsc

 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

    TABLESPACE tsd

 );

A row with time_id=17-MAR-2006 would be stored in partition sales_q1_2006.

In the following example, more complexity is added to the example presented earlier for a range-partitioned table. Storage parameters and a LOGGING attribute are specified at the table level. These replace the corresponding defaults inherited from the tablespace level for the table itself, and are inherited by the range partitions. However, because there was little business in the first quarter, the storage attributes for partition sales_q1_2006 are made smaller. The ENABLE ROW MOVEMENT clause is specified to allow the automatic migration of a row to a new partition if an update to a key value is made that would place the row in a different partition.

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

  )

 STORAGE (INITIAL 100K NEXT 50K) LOGGING

 PARTITION BY RANGE (time_id)

 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

    TABLESPACE tsa STORAGE (INITIAL 20K NEXT 10K)

 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

    TABLESPACE tsb

 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

    TABLESPACE tsc

 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

    TABLESPACE tsd

 )

 ENABLE ROW MOVEMENT;

Creating a Range-Partitioned Global Index

The rules for creating range-partitioned global indexes are similar to those for creating range-partitioned tables. The following is an example of creating a range-partitioned global index on sale_month for the tables created in the preceding examples. Each index partition is named but is stored in the default tablespace for the index.

CREATE INDEX amount_sold_ix ON sales(amount_sold)

   GLOBAL PARTITION BY RANGE(sale_month)

      ( PARTITION p_100 VALUES LESS THAN (100)

      , PARTITION p_1000 VALUES LESS THAN (1000)

      , PARTITION p_10000 VALUES LESS THAN (10000)

      , PARTITION p_100000 VALUES LESS THAN (100000)

      , PARTITION p_1000000 VALUES LESS THAN (1000000)

      , PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)

      );

Creating a Hash Partitioned Table

The following example creates a hash-partitioned table. The partitioning column is id, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (gear1, gear2, ...).

CREATE TABLE scubagear

     (id NUMBER,

      name VARCHAR2 (60))

   PARTITION BY HASH (id)

   PARTITIONS 4 

   STORE IN (gear1, gear2, gear3, gear4);

The following examples illustrate two methods of creating a hash-partitioned table named dept. In the first example the number of partitions is specified, but system generated names are assigned to them and they are stored in the default tablespace of the table.

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))

     PARTITION BY HASH(deptno) PARTITIONS 16;

In the following example, names of individual partitions, and tablespaces in which they are to reside, are specified. The initial extent size for each hash partition (segment) is also explicitly stated at the table level, and all partitions inherit this attribute.

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))

     STORAGE (INITIAL 10K)

     PARTITION BY HASH(deptno)

       (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,

        PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);

If you create a local index for this table, the database constructs the index so that it is equipartitioned with the underlying table. The database also ensures that the index is maintained automatically when maintenance operations are performed on the underlying table. The following is an example of creating a local index on the table dept:

CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL;

You can optionally name the hash partitions and tablespaces into which the local index partitions are to be stored, but if you do not do so, the database uses the name of the corresponding base partition as the index partition name, and stores the index partition in the same tablespace as the table partition.

Creating a Hash-Partitioned Global Index

Hash-partitioned global indexes can improve the performance of indexes where a small number of leaf blocks in the index have high contention in multiuser OLTP environments. Hash-partitioned global indexes can also limit the impact of index skew on monotonously increasing column values. Queries involving the equality and IN predicates on the index partitioning key can efficiently use hash-partitioned global indexes.

The syntax for creating a hash partitioned global index is similar to that used for a hash partitioned table. For example, the following statement creates a hash-partitioned global index:

CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL

     PARTITION BY HASH (c1,c2)

     (PARTITION p1  TABLESPACE tbs_1,

      PARTITION p2  TABLESPACE tbs_2,

      PARTITION p3  TABLESPACE tbs_3,

      PARTITION p4  TABLESPACE tbs_4);

Creating Composite Range-Hash Partitioned Tables

The following statement creates a range-hash partitioned table. In this example, four range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (ts1, ...,ts4).

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

  )

 PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)

  SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)

 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))

 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))

 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

 );

The partitions of a range-hash partitioned table are logical structures only, as their data is stored in the segments of their subpartitions. As with partitions, these subpartitions share the same logical attributes. Unlike range partitions in a range-partitioned table, the subpartitions cannot have different physical attributes from the owning partition, although they are not required to reside in the same tablespace.

Attributes specified for a range partition apply to all subpartitions of that partition. You can specify different attributes for each range partition, and you can specify a STORE IN clause at the partition level if the list of tablespaces across which the subpartitions of that partition should be spread is different from those of other partitions. All of this is illustrated in the following example.

CREATE TABLE emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER)   

     PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)

        SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)

    (PARTITION p1 VALUES LESS THAN (1000),

     PARTITION p2 VALUES LESS THAN (2000)

        STORE IN (ts2, ts4, ts6, ts8),

     PARTITION p3 VALUES LESS THAN (MAXVALUE)

       (SUBPARTITION p3_s1 TABLESPACE ts4,

        SUBPARTITION p3_s2 TABLESPACE ts5));

To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

The following statement is an example of creating a local index on the emp table where the index segments are spread across tablespaces ts7, ts8, and ts9.

CREATE INDEX emp_ix ON emp(deptno)

     LOCAL STORE IN (ts7, ts8, ts9);

This local index is equipartitioned with the base table as follows:

• It consists of as many partitions as the base table.

• Each index partition consists of as many subpartitions as the corresponding base table partition.

• Index entries for rows in a given subpartition of the base table are stored in the corresponding subpartition of the index.

Creating Composite Range-List Partitioned Tables

The range partitions of a range-list composite partitioned table are described as for non-composite range partitioned tables. This allows that optional subclauses of a PARTITION clause can specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

The list subpartition descriptions, in the SUBPARTITION clauses, are described as for non-composite list partitions, except the only physical attribute that can be specified is a tablespace (optional). Subpartitions inherit all other physical attributes from the partition description.

The following example illustrates how range-list partitioning might be used. The example tracks sales data of products by quarters and within each quarter, groups it by specified states.

CREATE TABLE quarterly_regional_sales

      (deptno number, item_no varchar2(20),

       txn_date date, txn_amount number, state varchar2(2))

  TABLESPACE ts4

  PARTITION BY RANGE (txn_date)

    SUBPARTITION BY LIST (state)

      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))

         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')

         ),

       PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))

         (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')

         ),

       PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))

         (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')

         ),

       PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))

         (SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')

         )

      );

A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying the subpartition whose descriptor value list contains a value matching the subpartition column value.

For example, some sample rows are inserted as follows:

• (10, 4532130, '23-Jan-1999', 8934.10, 'WA') maps to subpartition q1_1999_northwest

• (20, 5671621, '15-May-1999', 49021.21, 'OR') maps to subpartition q2_1999_northwest

• (30, 9977612, '07-Sep-1999', 30987.90, 'FL') maps to subpartition q3_1999_southeast

• (40, 9977612, '29-Nov-1999', 67891.45, 'TX') maps to subpartition q4_1999_southcentral

• (40, 4532130, '5-Jan-2000', 897231.55, 'TX') does not map to any partition in the table and raises an error

• (50, 5671621, '17-Dec-1999', 76123.35, 'CA') does not map to any subpartition in the table and raises an error

The partitions of a range-list partitioned table are logical structures only, as their data is stored in the segments of their subpartitions. The list subpartitions have the same characteristics as list partitions. You can specify a default subpartition, just as you specify a default partition for list partitioning.

The following example creates a table that specifies a tablespace at the partition and subpartition levels. The number of subpartitions within each partition varies, and default subpartitions are specified.

CREATE TABLE sample_regional_sales

      (deptno number, item_no varchar2(20),

       txn_date date, txn_amount number, state varchar2(2))

  PARTITION BY RANGE (txn_date)

    SUBPARTITION BY LIST (state)

      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))

          TABLESPACE tbs_1

         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q1_others VALUES (DEFAULT) TABLESPACE tbs_4

         ),

       PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))

          TABLESPACE tbs_2

         (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')

         ),

       PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))

          TABLESPACE tbs_3

         (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q3_others VALUES (DEFAULT) TABLESPACE tbs_4

         ),

       PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))

          TABLESPACE tbs_4

      );

This example results in the following subpartition descriptions:

• All subpartitions inherit their physical attributes, other than tablespace, from tablespace level defaults. This is because the only physical attribute that has been specified for partitions or subpartitions is tablespace. There are no table level physical attributes specified, thus tablespace level defaults are inherited at all levels.

• The first 4 subpartitions of partition q1_1999 are all contained in tbs_1, except for the subpartition q1_others, which is stored in tbs_4 and contains all rows that do not map to any of the other partitions.

• The 6 subpartitions of partition q2_1999 are all stored in tbs_2.

• The first 2 subpartitions of partition q3_1999 are all contained in tbs_3, except for the subpartition q3_others, which is stored in tbs_4 and contains all rows that do not map to any of the other partitions.

• There is no subpartition description for partition q4_1999. This results in one default subpartition being created and stored in tbs_4. The subpartition name is system generated in the form SYS_SUBPn.

To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

Creating Composite Range-Range Partitioned Tables

The range partitions of a range-range composite partitioned table are described as for non-composite range partitioned tables. This allows that optional subclauses of a PARTITION clause can specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

The range subpartition descriptions, in the SUBPARTITION clauses, are described as for non-composite range partitions, except the only physical attribute that can be specified is an optional tablespace. Subpartitions inherit all other physical attributes from the partition description.

The following example illustrates how range-range partitioning might be used. The example tracks shipments. The service level agreement with the customer states that every order will be delivered in the calendar month after the order was placed. The following types of orders are identified:

• E (EARLY): orders that are delivered before the the middle of the next month after the order was placed. These orders likely exceed customers' expectations.

• A (AGREED): orders that are delivered in the calendar month after the order was placed (but not early orders).

• L (LATE): orders that were only delivered starting the second calendar month after the order was placed.

CREATE TABLE shipments

( order_id      NUMBER NOT NULL

, order_date    DATE NOT NULL

, delivery_date DATE NOT NULL

, customer_id   NUMBER NOT NULL

, sales_amount  NUMBER NOT NULL

)

PARTITION BY RANGE (order_date)

SUBPARTITION BY RANGE (delivery_date)

( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-AUG-2006','dd-MON-yyyy'))

  ( SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-AUG-2006','dd-MON-yyyy'))

  , SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))

  , SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))

  ( SUBPARTITION p06_aug_e VALUES LESS THAN (TO_DATE('15-SEP-2006','dd-MON-yyyy'))

  , SUBPARTITION p06_aug_a VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

  , SUBPARTITION p06_aug_l VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))

  ( SUBPARTITION p06_sep_e VALUES LESS THAN (TO_DATE('15-OCT-2006','dd-MON-yyyy'))

  , SUBPARTITION p06_sep_a VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))

  , SUBPARTITION p06_sep_l VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))

  ( SUBPARTITION p06_oct_e VALUES LESS THAN (TO_DATE('15-NOV-2006','dd-MON-yyyy'))

  , SUBPARTITION p06_oct_a VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))

  , SUBPARTITION p06_oct_l VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))

  ( SUBPARTITION p06_nov_e VALUES LESS THAN (TO_DATE('15-DEC-2006','dd-MON-yyyy'))

  , SUBPARTITION p06_nov_a VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

  , SUBPARTITION p06_nov_l VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))

  ( SUBPARTITION p06_dec_e VALUES LESS THAN (TO_DATE('15-JAN-2007','dd-MON-yyyy'))

  , SUBPARTITION p06_dec_a VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy'))

  , SUBPARTITION p06_dec_l VALUES LESS THAN (MAXVALUE)

  )

);

A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying whether the value of the subpartitioning column falls within a specific range. For example, a shipment with an order date in September 2006 and a delivery date of October 28, 2006 falls in partition p06_oct_a.

To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

Creating Composite List-* Partitioned Tables

The concepts of list-hash, list-list, and list-range composite partitioning are similar to the concepts for range-hash, range-list, and range-range partitioning. This time, however, you specify PARTITION BY LIST to define the partitioning strategy.

The list partitions of a list-* composite partitioned table are described as for non-composite range partitioned tables. This allows that optional subclauses of a PARTITION clause can specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, then partitions inherit the attributes of their underlying table.

The subpartition descriptions, in the SUBPARTITION or SUBPARTITIONS clauses, are described as for range-* composite partitioning methods.

The following sections show examples for the different list-* composite partitioning methods.

CREATE TABLE accounts

( id             NUMBER

, account_number NUMBER

, customer_id    NUMBER

, balance        NUMBER

, branch_id      NUMBER

, region         VARCHAR(2)

, status         VARCHAR2(1)

)

PARTITION BY LIST (region)

SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 8

( PARTITION p_northwest VALUES ('OR', 'WA')

, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')

, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')

, PARTITION p_southeast VALUES ('FL', 'GA')

, PARTITION p_northcentral VALUES ('SD', 'WI')

, PARTITION p_southcentral VALUES ('OK', 'TX')

);

CREATE TABLE accounts

( id             NUMBER

, account_number NUMBER

, customer_id    NUMBER

, balance        NUMBER

, branch_id      NUMBER

, region         VARCHAR(2)

, status         VARCHAR2(1)

)

PARTITION BY LIST (region)

SUBPARTITION BY LIST (status)

( PARTITION p_northwest VALUES ('OR', 'WA')

  ( SUBPARTITION p_nw_bad VALUES ('B')

  , SUBPARTITION p_nw_average VALUES ('A')

  , SUBPARTITION p_nw_good VALUES ('G')

  )

, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')

  ( SUBPARTITION p_sw_bad VALUES ('B')

  , SUBPARTITION p_sw_average VALUES ('A')

  , SUBPARTITION p_sw_good VALUES ('G')

  )

, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')

  ( SUBPARTITION p_ne_bad VALUES ('B')

  , SUBPARTITION p_ne_average VALUES ('A')

  , SUBPARTITION p_ne_good VALUES ('G')

  )

, PARTITION p_southeast VALUES ('FL', 'GA')

  ( SUBPARTITION p_se_bad VALUES ('B')

  , SUBPARTITION p_se_average VALUES ('A')

  , SUBPARTITION p_se_good VALUES ('G')

  )

, PARTITION p_northcentral VALUES ('SD', 'WI')

  ( SUBPARTITION p_nc_bad VALUES ('B')

  , SUBPARTITION p_nc_average VALUES ('A')

  , SUBPARTITION p_nc_good VALUES ('G')

  )

, PARTITION p_southcentral VALUES ('OK', 'TX')

  ( SUBPARTITION p_sc_bad VALUES ('B')

  , SUBPARTITION p_sc_average VALUES ('A')

  , SUBPARTITION p_sc_good VALUES ('G')

  )

);

CREATE TABLE accounts

( id             NUMBER

, account_number NUMBER

, customer_id    NUMBER

, balance        NUMBER

, branch_id      NUMBER

, region         VARCHAR(2)

, status         VARCHAR2(1)

)

PARTITION BY LIST (region)

SUBPARTITION BY RANGE (balance)

( PARTITION p_northwest VALUES ('OR', 'WA')

  ( SUBPARTITION p_nw_low VALUES LESS THAN (1000)

  , SUBPARTITION p_nw_average VALUES LESS THAN (10000)

  , SUBPARTITION p_nw_high VALUES LESS THAN (100000)

  , SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')

  ( SUBPARTITION p_sw_low VALUES LESS THAN (1000)

  , SUBPARTITION p_sw_average VALUES LESS THAN (10000)

  , SUBPARTITION p_sw_high VALUES LESS THAN (100000)

  , SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')

  ( SUBPARTITION p_ne_low VALUES LESS THAN (1000)

  , SUBPARTITION p_ne_average VALUES LESS THAN (10000)

  , SUBPARTITION p_ne_high VALUES LESS THAN (100000)

  , SUBPARTITION p_ne_extraordinary VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_southeast VALUES ('FL', 'GA')

  ( SUBPARTITION p_se_low VALUES LESS THAN (1000)

  , SUBPARTITION p_se_average VALUES LESS THAN (10000)

  , SUBPARTITION p_se_high VALUES LESS THAN (100000)

  , SUBPARTITION p_se_extraordinary VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_northcentral VALUES ('SD', 'WI')

  ( SUBPARTITION p_nc_low VALUES LESS THAN (1000)

  , SUBPARTITION p_nc_average VALUES LESS THAN (10000)

  , SUBPARTITION p_nc_high VALUES LESS THAN (100000)

  , SUBPARTITION p_nc_extraordinary VALUES LESS THAN (MAXVALUE)

  )

, PARTITION p_southcentral VALUES ('OK', 'TX')

  ( SUBPARTITION p_sc_low VALUES LESS THAN (1000)

  , SUBPARTITION p_sc_average VALUES LESS THAN (10000)

  , SUBPARTITION p_sc_high VALUES LESS THAN (100000)

  , SUBPARTITION p_sc_extraordinary VALUES LESS THAN (MAXVALUE)

  )

) ENABLE ROW MOVEMENT;

Creating Composite Interval-* Partitioned Tables

The concepts of interval-* composite partitioning are similar to the concepts for range-* partitioning. However, you extend the PARTITION BY RANGE clause to include the INTERVAL definition. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point.

The subpartitions for intervals in an interval-* partitioned table will be created when the database creates the interval. You can specify the definition of future subpartitions only through the use of a subpartition template. To learn more about how to use a subpartition template, see "Using Subpartition Templates to Describe Composite Partitioned Tables".

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

  )

 PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

 SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4

 ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))

PARALLEL;

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

  )

 PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

 SUBPARTITION BY hash(cust_id)

   SUBPARTITION template

   ( SUBPARTITION p1 TABLESPACE ts1

   , SUBPARTITION p2 TABLESPACE ts2

   , SUBPARTITION p3 TABLESPACE ts3

   , SUBPARTITION P4 TABLESPACE ts4

   )

 ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))

) PARALLEL;

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

  )

 PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))

SUBPARTITION BY RANGE(amount_sold)

   SUBPARTITION TEMPLATE

   ( SUBPARTITION p_low VALUES LESS THAN (1000)

   , SUBPARTITION p_medium VALUES LESS THAN (4000)

   , SUBPARTITION p_high VALUES LESS THAN (8000)

   , SUBPARTITION p_ultimate VALUES LESS THAN (maxvalue)

   )

 ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))

PARALLEL;

CREATE TABLE sales

  ( prod_id       NUMBER(6)

  , cust_id       NUMBER

  , time_id       DATE

  , channel_id    CHAR(1)

  , promo_id      NUMBER(6)

  , quantity_sold NUMBER(3)

  , amount_sold   NUMBER(10,2)

  )

 PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))

 SUBPARTITION BY LIST (channel_id)

   SUBPARTITION TEMPLATE

   ( SUBPARTITION p_catalog VALUES ('C')

   , SUBPARTITION p_internet VALUES ('I')

   , SUBPARTITION p_partners VALUES ('P')

   , SUBPARTITION p_direct_sales VALUES ('S')

   , SUBPARTITION p_tele_sales VALUES ('T')

   )

 ( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))

PARALLEL;

Specifying a Subpartition Template for a *-Hash Partitioned Table

In the case of [range | interval | list]-hash partitioned tables, the subpartition template can describe the subpartitions in detail, or it can specify just the number of hash subpartitions.

The following example creates a range-hash partitioned table using a subpartition template:

CREATE TABLE emp_sub_template (deptno NUMBER, empname VARCHAR(32), grade NUMBER)

     PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)

     SUBPARTITION TEMPLATE

         (SUBPARTITION a TABLESPACE ts1,

          SUBPARTITION b TABLESPACE ts2,

          SUBPARTITION c TABLESPACE ts3,

          SUBPARTITION d TABLESPACE ts4

         )

    (PARTITION p1 VALUES LESS THAN (1000),

     PARTITION p2 VALUES LESS THAN (2000),

     PARTITION p3 VALUES LESS THAN (MAXVALUE)

    );

This example produces the following table description:

• Every partition has four subpartitions as described in the subpartition template.

• Each subpartition has a tablespace specified. It is required that if a tablespace is specified for one subpartition in a subpartition template, then one must be specified for all.

• The names of the subpartitions, unless you use interval-* subpartitioning, are generated by concatenating the partition name with the subpartition name in the form:

  partition name_subpartition name

  For interval-* subpartitioning, the subpartition names are system-generated in the form:

  SYS_SUBPn

The following query displays the subpartition names and tablespaces:

SQL> SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME

  2  FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP_SUB_TEMPLATE'

  3  ORDER BY TABLESPACE_NAME;


TABLESPACE_NAME PARTITION_NAME  SUBPARTITION_NAME

--------------- --------------- ------------------

TS1             P1              P1_A

TS1             P2              P2_A

TS1             P3              P3_A

TS2             P1              P1_B

TS2             P2              P2_B

TS2             P3              P3_B

TS3             P1              P1_C

TS3             P2              P2_C

TS3             P3              P3_C

TS4             P1              P1_D

TS4             P2              P2_D

TS4             P3              P3_D


12 rows selected.

Specifying a Subpartition Template for a *-List Partitioned Table

The following example, for a range-list partitioned table, illustrates how using a subpartition template can help you stripe data across tablespaces. In this example a table is created where the table subpartitions are vertically striped, meaning that subpartition n from every partition is in the same tablespace.

CREATE TABLE stripe_regional_sales

            ( deptno number, item_no varchar2(20),

              txn_date date, txn_amount number, state varchar2(2))

   PARTITION BY RANGE (txn_date)

   SUBPARTITION BY LIST (state)

   SUBPARTITION TEMPLATE 

      (SUBPARTITION northwest VALUES ('OR', 'WA') TABLESPACE tbs_1,

       SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE tbs_2,

       SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE tbs_3,

       SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE tbs_4,

       SUBPARTITION midwest VALUES ('SD', 'WI') TABLESPACE tbs_5,

       SUBPARTITION south VALUES ('AL', 'AK') TABLESPACE tbs_6,

       SUBPARTITION others VALUES (DEFAULT ) TABLESPACE tbs_7

      )

  (PARTITION q1_1999 VALUES LESS THAN ( TO_DATE('01-APR-1999','DD-MON-YYYY')),

   PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-JUL-1999','DD-MON-YYYY')),

   PARTITION q3_1999 VALUES LESS THAN ( TO_DATE('01-OCT-1999','DD-MON-YYYY')),

   PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))

  );

If you specified the tablespaces at the partition level (for example, tbs_1 for partition q1_1999, tbs_2 for partition q2_1999, tbs_3 for partition q3_1999, and tbs_4 for partition q4_1999) and not in the subpartition template, then the table would be horizontally striped. All subpartitions would be in the tablespace of the owning partition.

Creating Range-Partitioned Index-Organized Tables

You can partition index-organized tables, and their secondary indexes, by the range method. In the following example, a range-partitioned index-organized table sales is created. The INCLUDING clause specifies that all columns after week_no are to be stored in an overflow segment. There is one overflow segment for each partition, all stored in the same tablespace (overflow_here). Optionally, OVERFLOW TABLESPACE could be specified at the individual partition level, in which case some or all of the overflow segments could have separate TABLESPACE attributes.

CREATE TABLE sales(acct_no NUMBER(5), 

                   acct_name CHAR(30), 

                   amount_of_sale NUMBER(6), 

                   week_no INTEGER,

                   sale_details VARCHAR2(1000),

             PRIMARY KEY (acct_no, acct_name, week_no)) 

     ORGANIZATION INDEX 

             INCLUDING week_no

             OVERFLOW TABLESPACE overflow_here

     PARTITION BY RANGE (week_no)

            (PARTITION VALUES LESS THAN (5) 

                   TABLESPACE ts1,

             PARTITION VALUES LESS THAN (9) 

                   TABLESPACE ts2 OVERFLOW TABLESPACE overflow_ts2,

             ...

             PARTITION VALUES LESS THAN (MAXVALUE) 

                   TABLESPACE ts13);

Creating Hash-Partitioned Index-Organized Tables

Another option for partitioning index-organized tables is to use the hash method. In the following example, the sales index-organized table is partitioned by the hash method.

CREATE TABLE sales(acct_no NUMBER(5), 

                   acct_name CHAR(30), 

                   amount_of_sale NUMBER(6), 

                   week_no INTEGER,

                   sale_details VARCHAR2(1000),

             PRIMARY KEY (acct_no, acct_name, week_no)) 

     ORGANIZATION INDEX 

             INCLUDING week_no

     OVERFLOW

          PARTITION BY HASH (week_no)

             PARTITIONS 16

             STORE IN (ts1, ts2, ts3, ts4)

             OVERFLOW STORE IN (ts3, ts6, ts9);

Creating List-Partitioned Index-Organized Tables

The other option for partitioning index-organized tables is to use the list method. In the following example, the sales index-organized table is partitioned by the list method. This example uses the example tablespace, which is part of the sample schemas in your seed database. Normally you would specify different tablespace storage for different partitions.

CREATE TABLE sales(acct_no NUMBER(5), 

                   acct_name CHAR(30), 

                   amount_of_sale NUMBER(6), 

                   week_no INTEGER,

                   sale_details VARCHAR2(1000),

             PRIMARY KEY (acct_no, acct_name, week_no)) 

     ORGANIZATION INDEX 

             INCLUDING week_no

             OVERFLOW TABLESPACE example

     PARTITION BY LIST (week_no)

            (PARTITION VALUES (1, 2, 3, 4) 

                   TABLESPACE example,

             PARTITION VALUES (5, 6, 7, 8) 

                   TABLESPACE example OVERFLOW TABLESPACE example,

             PARTITION VALUES (DEFAULT) 

                   TABLESPACE example);

Adding a Partition to a Range-Partitioned Table

Use the ALTER TABLE ... ADD PARTITION statement to add a new partition to the "high" end (the point after the last existing partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.

For example, consider the table, sales, which contains data for the current month in addition to the previous 12 months. On January 1, 1999, you add a partition for January, which is stored in tablespace tsx.

ALTER TABLE sales

      ADD PARTITION jan99 VALUES LESS THAN ( '01-FEB-1999' )

      TABLESPACE tsx;

Local and global indexes associated with the range-partitioned table remain usable.

Adding a Partition to a Hash-Partitioned Table

When you add a partition to a hash-partitioned table, the database populates the new partition with rows rehashed from an existing partition (selected by the database) as determined by the hash function. As a result, if the table contains data, then it may take some time to add a hash partition.

The following statements show two ways of adding a hash partition to table scubagear. Choosing the first statement adds a new hash partition whose partition name is system generated, and which is placed in the default tablespace. The second statement also adds a new hash partition, but that partition is explicitly named p_named and is created in tablespace gear5.

ALTER TABLE scubagear ADD PARTITION;


ALTER TABLE scubagear

      ADD PARTITION p_named TABLESPACE gear5;

Indexes may be marked UNUSABLE as explained in the following table:

Adding a Partition to a List-Partitioned Table

The following statement illustrates how to add a new partition to a list-partitioned table. In this example physical attributes and NOLOGGING are specified for the partition being added.

ALTER TABLE q1_sales_by_region 

   ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')

      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3

      NOLOGGING;

Any value in the set of literal values that describe the partition being added must not exist in any of the other partitions of the table.

You cannot add a partition to a list-partitioned table that has a default partition, but you can split the default partition. By doing so, you effectively create a new partition defined by the values that you specify, and a second partition that remains the default partition.

Local and global indexes associated with the list-partitioned table remain usable.

Adding a Partition to an Interval-Partitioned Table

You cannot explicitly add a partition to an interval-partitioned table unless you first lock the partition, which triggers the creation of the partition. The database automatically creates a partition for an interval when data for that interval is inserted. In general, you only need to explicitly create interval partitions for a partition exchange load scenario.

To change the interval for future partitions, use the SET INTERVAL clause of the ALTER TABLE statement. This clause changes the interval for partitions beyond the current highest boundary of all materialized interval partitions.

You also use the SET INTERVAL clause to migrate an existing range partitioned or range-* composite partitioned table into an interval or interval-* partitioned table. If you want to disable the creation of future interval partitions, and effectively revert back to a range-partitioned table, then use an empty value in the SET INTERVAL clause. Created interval partitions will then be transformed into range partitions with their current high values.

To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:

ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');


ORA-14767: Cannot specify this interval with existing high bounds

You need to create another daily partition with a high bound of February 1, 2007 in order to successfully change to a monthly interval:

LOCK TABLE transactions PARTITION FOR(TO_DATE('31-JAN-2007','dd-MON-yyyy') IN SHARE MODE;


ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');

The lower partitions of an interval-partitioned table are range partitions. You can split range partitions in order to add more partitions in the range portion of the interval-partitioned table.

In order to disable interval partitioning on the transactions table, use:

ALTER TABLE transactions SET INTERVAL ();

Adding Partitions to a Composite [Range | List | Interval]-Hash Partitioned Table

Partitions can be added at both the partition level and at the hash subpartition level.

ALTER TABLE sales ADD PARTITION q1_2000

      VALUES LESS THAN (2000, 04, 01) COMPRESS

      SUBPARTITIONS 8 STORE IN tbs5;

ALTER TABLE diving MODIFY PARTITION locations_us

      ADD SUBPARTITION us_locs5 TABLESPACE us1;

Adding Partitions to a Composite [Range | List | Interval]-List Partitioned Table

Partitions can be added at both the partition level and at the list subpartition level.

ALTER TABLE quarterly_regional_sales 

   ADD PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))

      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE ts3 NOLOGGING

         (

          SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),

          SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),

          SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),

          SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),

          SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),

          SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')

         );

ALTER TABLE quarterly_regional_sales

   MODIFY PARTITION q1_1999 

      ADD SUBPARTITION q1_1999_south

         VALUES ('AR','MS','AL') tablespace ts2;

Adding Partitions to a Composite [Range | List | Interval]-Range Partitioned Table

Partitions can be added at both the partition level and at the range subpartition level.

ALTER TABLE shipments

   ADD PARTITION p_2007_jan

      VALUES LESS THAN (TO_DATE('01-FEB-2007','dd-MON-yyyy')) COMPRESS

      ( SUBPARTITION p07_jan_e VALUES LESS THAN (TO_DATE('15-FEB-2007','dd-MON-yyyy'))

      , SUBPARTITION p07_jan_a VALUES LESS THAN (TO_DATE('01-MAR-2007','dd-MON-yyyy'))

      , SUBPARTITION p07_jan_l VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy'))

      ) ;

ALTER TABLE shipments

   MODIFY PARTITION p_2007_jan

      ADD SUBPARTITION p07_jan_vl VALUES LESS THAN (MAXVALUE) ;

Adding a Partition or Subpartition to a Reference-Partitioned Table

A partition or subpartition can be added to a parent table in a reference partition definition just as partitions and subpartitions can be added to a range, hash, list, or composite partitioned table. The add operation will automatically cascade to any descendant reference partitioned tables. The DEPENDENT TABLES clause can be used to set specific properties for dependent tables when you add partitions or subpartitions to a master table.

Adding Index Partitions

You cannot explicitly add a partition to a local index. Instead, a new partition is added to a local index only when you add a partition to the underlying table. Specifically, when there is a local index defined on a table and you issue the ALTER TABLE statement to add a partition, a matching partition is also added to the local index. The database assigns names and default physical storage attributes to the new index partitions, but you can rename or alter them after the ADD PARTITION operation is complete.

You can effectively specify a new tablespace for an index partition in an ADD PARTITION operation by first modifying the default attributes for the index. For example, assume that a local index, q1_sales_by_region_locix, was created for list partitioned table q1_sales_by_region. If before adding the new partition q1_nonmainland, as shown in "Adding a Partition to a List-Partitioned Table", you had issued the following statement, then the corresponding index partition would be created in tablespace tbs_4.

ALTER INDEX q1_sales_by_region_locix

   MODIFY DEFAULT ATTRIBUTES TABLESPACE tbs_4;

Otherwise, it would be necessary for you to use the following statement to move the index partition to tbs_4 after adding it:

ALTER INDEX q1_sales_by_region_locix 

   REBUILD PARTITION q1_nonmainland TABLESPACE tbs_4;

 

You can add a partition to a hash-partitioned global index using the ADD PARTITION syntax of ALTER INDEX. The database adds hash partitions and populates them with index entries rehashed from an existing hash partition of the index, as determined by the hash function. The following statement adds a partition to the index hgidx shown in "Creating a Hash-Partitioned Global Index":

ALTER INDEX hgidx ADD PARTITION p5;

You cannot add a partition to a range-partitioned global index, because the highest partition always has a partition bound of MAXVALUE. If you want to add a new highest partition, use the ALTER INDEX ... SPLIT PARTITION statement.

Coalescing a Partition in a Hash-Partitioned Table

The ALTER TABLE ... COALESCE PARTITION statement is used to coalesce a partition in a hash-partitioned table. The following statement reduces by one the number of partitions in a table by coalescing a partition.

ALTER TABLE ouu1

     COALESCE PARTITION;

Coalescing a Subpartition in a *-Hash Partitioned Table

The following statement distributes the contents of a subpartition of partition us_locations into one or more remaining subpartitions (determined by the hash function) of the same partition. Note that for an interval-partitioned table, you can only coalesce hash subpartitions of materialized range or interval partitions. Basically, this operation is the inverse of the MODIFY PARTITION ... ADD SUBPARTITION clause discussed in "Adding a Subpartition to a [Range | List | Interval]-Hash Partitioned Table".

ALTER TABLE diving MODIFY PARTITION us_locations

     COALESCE SUBPARTITION;

Coalescing Hash-partitioned Global Indexes

You can instruct the database to reduce by one the number of index partitions in a hash-partitioned global index using the COALESCE PARTITION clause of ALTER INDEX. The database selects the partition to coalesce based on the requirements of the hash partition. The following statement reduces by one the number of partitions in the hgidx index, created in "Creating a Hash-Partitioned Global Index":

ALTER INDEX hgidx COALESCE PARTITION;

Dropping Table Partitions

Use one of the following statements to drop a table partition or subpartition:

• ALTER TABLE ... DROP PARTITION to drop a table partition

• ALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a composite *-[range | list] partitioned table

If you want to preserve the data in the partition, then use the MERGE PARTITION statement instead of the DROP PARTITION statement.

If local indexes are defined for the table, then this statement also drops the matching partition or subpartitions from the local index. All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE unless either of the following is true:

• You specify UPDATE INDEXES (Cannot be specified for index-organized tables. Use UPDATE GLOBAL INDEXES instead.)

• The partition being dropped or its subpartitions are empty

  Note:

  • You cannot drop the only partition in a table. Instead, you must drop the table.

  • You cannot drop the highest range partition in the range-partitioned section of an interval-partitioned or interval-* composite partitioned table.

The following sections contain some scenarios for dropping table partitions.

ALTER TABLE sales DROP PARTITION dec98;

ALTER INDEX sales_area_ix REBUILD;

ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;

ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix;

ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix;

...

ALTER INDEX sales_area_ix REBUILD PARTITION dec99_ix;

DELETE FROM sales partition (dec98);

ALTER TABLE sales DROP PARTITION dec98;

ALTER TABLE sales DROP PARTITION dec98

     UPDATE INDEXES;

DELETE FROM sales partition (dec94);

ALTER TABLE sales DROP PARTITION dec94;

Dropping Interval Partitions

You can drop interval partitions in an interval-partitioned table. This operation will drop the data for the interval only and leave the interval definition in tact. If data is inserted in the interval just dropped, then the database will again create an interval partition.

You can also drop range partitions in an interval-partitioned table. The rules for dropping a range partition in an interval-partitioned table follow the rules for dropping a range partition in a range-partitioned table. If you drop a range partition in the middle of a set of range partitions, then the lower boundary for the next range partition shifts to the lower boundary of the range partition you just dropped. You cannot drop the highest range partition in the range-partitioned section of an interval-partitioned table.

The following example drops the September 2007 interval partition from the sales table. There are only local indexes so no indexes will be invalidated.

ALTER TABLE sales DROP PARTITION FOR(TO_DATE('01-SEP-2007','dd-MON-yyyy'));

Dropping Index Partitions

You cannot explicitly drop a partition of a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

If a global index partition is empty, then you can explicitly drop it by issuing the ALTER INDEX ... DROP PARTITION statement. But, if a global index partition contains data, then dropping the partition causes the next highest partition to be marked UNUSABLE. For example, you would like to drop the index partition P1, and P2 is the next highest partition. You must issue the following statements:

ALTER INDEX npr DROP PARTITION P1;

ALTER INDEX npr REBUILD PARTITION P2;

Exchanging a Range, Hash, or List Partition

To exchange a partition of a range, hash, or list-partitioned table with a non-partitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a non-partitioned table follows. In this example, table stocks can be range, hash, or list partitioned.

ALTER TABLE stocks

    EXCHANGE PARTITION p3 WITH TABLE stock_table_3;

Exchanging a Partition of an Interval Partitioned Table

You can exchange interval partitions in an interval-partitioned table. However, you have to make sure the interval partition has been created before you can exchange the partition. You can let the database create the partition by locking the interval partition.

The following example shows a partition exchange for the interval_sales table, interval-partitioned using monthly partitions as of January 1, 2004. This example shows how to add data for June 2007 to the table using partition exchange load. Assume there are only local indexes on the interval_sales table, and equivalent indexes have been created on the interval_sales_june_2007 table.

LOCK TABLE interval_sales

PARTITION FOR (TO_DATE('01-JUN-2007','dd-MON-yyyy'))

IN SHARE MODE;


ALTER TABLE interval_sales

EXCHANGE PARTITION FOR (TO_DATE('01-JUN-2007','dd-MON-yyyy'))

WITH TABLE interval_sales_jun_2007

INCLUDING INDEXES;

Note the use of the FOR syntax to identify a partition that was system-generated. The partition name can be used by querying the *_TAB_PARTITIONS data dictionary view to find out the system-generated partition name.

Exchanging a Partition of a Reference Partitioned Table

You can exchange partitions in a reference-partitioned table, but you have to make sure that data you reference is available in the respective partition in the parent table.

The following example shows a partition exchange load scenario for the range-partitioned orders table, and the reference partitioned order_items table. Note that the data in the order_items_dec_2006 table only contains order item data for orders with an order_date in December 2006.

ALTER TABLE orders

EXCHANGE PARTITION p_2006_dec

WITH TABLE orders_dec_2006

UPDATE GLOBAL INDEXES;


ALTER TABLE order_items_dec_2006

ADD CONSTRAINT order_items_dec_2006_fk

FOREIGN KEY (order_id)

REFERENCES orders(order_id) ;


ALTER TABLE order_items

EXCHANGE PARTITION p_2006_dec

WITH TABLE order_items_dec_2006;

Note that you have to use the UPDATE GLOBAL INDEXES or UPDATE INDEXES on the exchange partition of the parent table in order for the primary key index to remain usable. Note also that you have to create or enable the foreign key constraint on the order_items_dec_2006 table in order for the partition exchange on the reference-partitioned table to succeed.

Exchanging a Partition of a Table with Virtual Columns

You can exchange partitions in the presence of virtual columns. In order for a partition exchange on a partitioned table with virtual columns to succeed, you have to create a table that matches the definition of all non-virtual columns in a single partition of the partitioned table. You do not need to include the virtual column definitions, unless constraints or indexes have been defined on the virtual column.

In this case, you have to include the virtual column definition in order to match the partitioned table's constraint and index definitions. This scenario also applies to virtual column-based partitioned tables.

Exchanging a Hash-Partitioned Table with a *-Hash Partition

In this example, you are exchanging a whole hash-partitioned table, with all of its partitions, with the partition of a *-hash partitioned table and all of its hash subpartitions. The following example illustrates this concept for a range-hash partitioned table.

First, create a hash-partitioned table:

CREATE TABLE t1 (i NUMBER, j NUMBER)

     PARTITION BY HASH(i)

       (PARTITION p1, PARTITION p2);

Populate the table, then create a range-hash partitioned table as shown:

CREATE TABLE t2 (i NUMBER, j NUMBER)

     PARTITION BY RANGE(j)

     SUBPARTITION BY HASH(i)

        (PARTITION p1 VALUES LESS THAN (10)

            SUBPARTITION t2_pls1

            SUBPARTITION t2_pls2,

         PARTITION p2 VALUES LESS THAN (20)

            SUBPARTITION t2_p2s1

            SUBPARTITION t2_p2s2));

It is important that the partitioning key in table t1 is the same as the subpartitioning key in table t2.

To migrate the data in t1 to t2, and validate the rows, use the following statement:

ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1

     WITH VALIDATION;

Exchanging a Subpartition of a *-Hash Partitioned Table

Use the ALTER TABLE ... EXCHANGE SUBPARTITION statement to convert a hash subpartition of a *-hash partitioned table into a non-partitioned table, or the reverse. The following example converts the subpartition q3_1999_s1 of table sales into the non-partitioned table q3_1999. Local index partitions are exchanged with corresponding indexes on q3_1999.

ALTER TABLE sales EXCHANGE SUBPARTITION q3_1999_s1

      WITH TABLE q3_1999 INCLUDING INDEXES;

Exchanging a List-Partitioned Table with a *-List Partition

The semantics of the ALTER TABLE ... EXCHANGE PARTITION statement are the same as described previously in "Exchanging a Hash-Partitioned Table with a *-Hash Partition". The example below shows an exchange partition scenario for a list-list partitioned table.

CREATE TABLE customers_apac

( id            NUMBER

, name          VARCHAR2(50)

, email         VARCHAR2(100)

, region        VARCHAR2(4)

, credit_rating VARCHAR2(1)

)

PARTITION BY LIST (credit_rating)

( PARTITION poor VALUES ('P')

, PARTITION mediocre VALUES ('C')

, PARTITION good VALUES ('G')

, PARTITION excellent VALUES ('E')

);

Populate the table with APAC customers. Then create a list-list partitioned table:

CREATE TABLE customers

( id            NUMBER

, name          VARCHAR2(50)

, email         VARCHAR2(100)

, region        VARCHAR2(4)

, credit_rating VARCHAR2(1)

)

PARTITION BY LIST (region)

SUBPARTITION BY LIST (credit_rating)

SUBPARTITION TEMPLATE

( SUBPARTITION poor VALUES ('P')

, SUBPARTITION mediocre VALUES ('C')

, SUBPARTITION good VALUES ('G')

, SUBPARTITION excellent VALUES ('E')

)

(PARTITION americas VALUES ('AMER')

, PARTITION emea VALUES ('EMEA')

, PARTITION apac VALUES ('APAC')

);

It is important that the partitioning key in the customers_apac table matches the subpartitioning key in the customers table.

Next, exchange the apac partition.

ALTER TABLE customers

EXCHANGE PARTITION apac

WITH TABLE customers_apac

WITH VALIDATION;

Exchanging a Subpartition of a *-List Partitioned Table

The semantics of the ALTER TABLE ... EXCHANGE SUBPARTITION are the same as described previously in "Exchanging a Subpartition of a *-Hash Partitioned Table".

Exchanging a Range-Partitioned Table with a *-Range Partition

The semantics of the ALTER TABLE ... EXCHANGE PARTITION statement are the same as described previously in "Exchanging a Hash-Partitioned Table with a *-Hash Partition". The example below shows the orders table, which is interval partitioned by order_date, and subpartitioned by range on order_total. The example shows how to exchange a single monthly interval with a range-partitioned table.

CREATE TABLE orders_mar_2007

( id          NUMBER

, cust_id     NUMBER

, order_date  DATE

, order_total NUMBER

)

PARTITION BY RANGE (order_total)

( PARTITION p_small VALUES LESS THAN (1000)

, PARTITION p_medium VALUES LESS THAN (10000)

, PARTITION p_large VALUES LESS THAN (100000)

, PARTITION p_extraordinary VALUES LESS THAN (MAXVALUE)

);

Populate the table with orders for March 2007. Then create an interval-range partitioned table:

CREATE TABLE orders

( id          NUMBER

, cust_id     NUMBER

, order_date  DATE

, order_total NUMBER

)

PARTITION BY RANGE (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

  SUBPARTITION BY RANGE (order_total)

  SUBPARTITION TEMPLATE

  ( SUBPARTITION p_small VALUES LESS THAN (1000)

  , SUBPARTITION p_medium VALUES LESS THAN (10000)

  , SUBPARTITION p_large VALUES LESS THAN (100000)

  , SUBPARTITION p_extraordinary VALUES LESS THAN (MAXVALUE)

  )

(PARTITION p_before_2007 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-

MON-yyyy')));

It is important that the partitioning key in the orders_mar_2007 table matches the subpartitioning key in the orders table.

Next, exchange the partition. Note that since an interval partition is to be exchanged, the partition is first locked to ensure that the partition is created.

LOCK TABLE orders PARTITION FOR (TO_DATE('01-MAR-2007','dd-MON-yyyy')) 

IN SHARE MODE;


ALTER TABLE orders

EXCHANGE PARTITION

FOR (TO_DATE('01-MAR-2007','dd-MON-yyyy'))

WITH TABLE orders_mar_2007

WITH VALIDATION;

Exchanging a Subpartition of a *-Range Partitioned Table

The semantics of the ALTER TABLE ... EXCHANGE SUBPARTITION are the same as described previously in "Exchanging a Subpartition of a *-Hash Partitioned Table".

Merging Range Partitions

You are allowed to merge the contents of two adjacent range partitions into one partition. Nonadjacent range partitions cannot be merged. The resulting partition inherits the higher upper bound of the two merged partitions.

One reason for merging range partitions is to keep historical data online in larger partitions. For example, you can have daily partitions, with the oldest partition rolled up into weekly partitions, which can then be rolled up into monthly partitions, and so on.

The following scripts create an example of merging range partitions.

First, create a partitioned table and create local indexes.

-- Create a Table with four partitions each on its own tablespace

-- Partitioned by range on the data column.

--

CREATE TABLE four_seasons

( 

        one DATE,

        two VARCHAR2(60),

        three NUMBER

)

PARTITION  BY RANGE ( one ) 

(

PARTITION quarter_one 

   VALUES LESS THAN ( TO_DATE('01-apr-1998','dd-mon-yyyy'))

   TABLESPACE quarter_one,

PARTITION quarter_two 

   VALUES LESS THAN ( TO_DATE('01-jul-1998','dd-mon-yyyy'))

   TABLESPACE quarter_two,

PARTITION quarter_three

   VALUES LESS THAN ( TO_DATE('01-oct-1998','dd-mon-yyyy'))

   TABLESPACE quarter_three,

PARTITION quarter_four

   VALUES LESS THAN ( TO_DATE('01-jan-1999','dd-mon-yyyy'))

   TABLESPACE quarter_four

);

-- 

-- Create local PREFIXED index on Four_Seasons

-- Prefixed because the leftmost columns of the index match the

-- Partitioning key 

--

CREATE INDEX i_four_seasons_l ON four_seasons ( one,two ) 

LOCAL ( 

PARTITION i_quarter_one TABLESPACE i_quarter_one,

PARTITION i_quarter_two TABLESPACE i_quarter_two,

PARTITION i_quarter_three TABLESPACE i_quarter_three,

PARTITION i_quarter_four TABLESPACE i_quarter_four

);

Next, merge partitions.

--

-- Merge the first two partitions 

--

ALTER TABLE four_seasons 

MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two

UPDATE INDEXES;

If you omit the UPDATE INDEXES clause from the preceding statement, then you must rebuild the local index for the affected partition.

-- Rebuild index for quarter_two, which has been marked unusable 

-- because it has not had all of the data from Q1 added to it.

-- Rebuilding the index will correct this.

--

ALTER TABLE four_seasons MODIFY PARTITION 

quarter_two REBUILD UNUSABLE LOCAL INDEXES;

Merging Interval Partitions

The contents of two adjacent interval partitions can be merged into one partition. Nonadjacent interval partitions cannot be merged. The first interval partition can also be merged with the highest range partition. The resulting partition inherits the higher upper bound of the two merged partitions.

Merging interval partitions always results in the transition point being moved to the higher upper bound of the two merged partitions. This means that the range section of the interval-partitioned table will be extended to the upper bound of the two merged partitions. Any materialized interval partitions with boundaries lower than the newly merged partition will automatically be converted into range partitions, with their upper boundaries defined by the upper boundaries of their intervals.

For example, consider the following interval-partitioned table transactions:

CREATE TABLE transactions

( id               NUMBER

, transaction_date DATE

, value            NUMBER

)

PARTITION BY RANGE (transaction_date)

INTERVAL (NUMTODSINTERVAL(1,'DAY'))

( PARTITION p_before_2007 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')));

Insert data into the interval section of the table. This will create the interval partitions for these days. Note that January 15, 2007 and January 16, 2007 are stored in adjacent interval partitions.

INSERT INTO transactions VALUES (1,TO_DATE('15-JAN-2007','dd-MON-yyyy'),100);

INSERT INTO transactions VALUES (2,TO_DATE('16-JAN-2007','dd-MON-yyyy'),600); 

INSERT INTO transactions VALUES (3,TO_DATE('30-JAN-2007','dd-MON-yyyy'),200);

Next, merge the two adjacent interval partitions. The new partition will again have a system-generated name.

ALTER TABLE transactions

MERGE PARTITIONS FOR(TO_DATE('15-JAN-2007','dd-MON-yyyy'))

, FOR(TO_DATE('16-JAN-2007','dd-MON-yyyy'));

The transition point for the transactions table has now moved to January 17, 2007. The range section of the interval-partitioned table contains two range partitions: values less than January 1, 2007 and values less than January 17, 2007. Values greater than January 17, 2007 fall in the interval portion of the interval-partitioned table.

Merging List Partitions

When you merge list partitions, the partitions being merged can be any two partitions. They do not need to be adjacent, as for range partitions, because list partitioning does not assume any order for partitions. The resulting partition consists of all of the data from the original two partitions. If you merge a default list partition with any other partition, the resulting partition will be the default partition.

The following statement merges two partitions of a table partitioned using the list method into a partition that inherits all of its attributes from the table-level default attributes. MAXEXTENTS is specified in the statement.

ALTER TABLE q1_sales_by_region 

   MERGE PARTITIONS q1_northcentral, q1_southcentral 

   INTO PARTITION q1_central 

      STORAGE(MAXEXTENTS 20);

The value lists for the two original partitions were specified as:

PARTITION q1_northcentral VALUES ('SD','WI')

PARTITION q1_southcentral VALUES ('OK','TX')

The resulting sales_west partition value list comprises the set that represents the union of these two partition value lists, or specifically:

('SD','WI','OK','TX')

Merging *-Hash Partitions

When you merge *-hash partitions, the subpartitions are rehashed into the number of subpartitions specified by SUBPARTITIONS n or the SUBPARTITION clause. If neither is included, table-level defaults are used.

Note that the inheritance of properties is different when a *-hash partition is split (discussed in "Splitting a *-Hash Partition"), as opposed to when two *-hash partitions are merged. When a partition is split, the new partitions can inherit properties from the original partition because there is only one parent. However, when partitions are merged, properties must be inherited from the table level.

For interval-hash partitioned tables, you can only merge two adjacent interval partitions, or the highest range partition with the first interval partition. As described in "Merging Interval Partitions", the transition point will move when you merge intervals in an interval-hash partitioned table.

The following example merges two range-hash partitions:

ALTER TABLE all_seasons

   MERGE PARTITIONS quarter_1, quarter_2 INTO PARTITION quarter_2

   SUBPARTITIONS 8;

Merging *-List Partitions

Partitions can be merged at the partition level and subpartitions can be merged at the list subpartition level.

ALTER TABLE stripe_regional_sales

   MERGE PARTITIONS q1_1999, q2_1999 INTO PARTITION q1_q2_1999

      STORAGE(MAXEXTENTS 20);

ALTER TABLE quarterly_regional_sales

   MERGE SUBPARTITIONS q1_1999_northwest, q1_1999_southwest

      INTO SUBPARTITION q1_1999_west

         TABLESPACE ts4;

Merging *-Range Partitions

Partitions can be merged at the partition level and subpartitions can be merged at the range subpartition level.

ALTER TABLE orders

MERGE PARTITIONS FOR(TO_DATE('01-MAR-2007','dd-MON-yyyy')), 

FOR(TO_DATE('01-APR-2007','dd-MON-yyyy'))

INTO PARTITION p_pre_may_2007;

Modifying Default Attributes of a Table

You can modify the default attributes that will be inherited for range, hash, list, interval, or reference partitions using the MODIFY DEFAULT ATTRIBUTES clause of ALTER TABLE.

For hash-partitioned tables, only the TABLESPACE attribute can be modified.

Modifying Default Attributes of a Partition

To modify the default attributes inherited when creating subpartitions, use the ALTER TABLE ... MODIFY DEFAULT ATTRIBUTES FOR PARTITION. The following statement modifies the TABLESPACE in which future subpartitions of partition p1 in range-hash partitioned table emp will reside.

ALTER TABLE emp

     MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE ts1;

Because all subpartitions of a range-hash partitioned table must share the same attributes, except TABLESPACE, it is the only attribute that can be changed.

You cannot modify default attributes of interval partitions that have not yet been created. If you want to change the way in which future subpartitions in an interval-partitioned table are created, then you have to modify the subpartition template.

Modifying Default Attributes of Index Partitions

In similar fashion to table partitions, you can alter the default attributes that will be inherited by partitions of a range-partitioned global index, or local index partitions of partitioned tables. For this you use the ALTER INDEX ... MODIFY DEFAULT ATTRIBUTES statement. Use the ALTER INDEX ... MODIFY DEFAULT ATTRIBUTES FOR PARTITION statement if you are altering default attributes to be inherited by subpartitions of a composite partitioned table.

Modifying Real Attributes for a Range or List Partition

Use the ALTER TABLE ... MODIFY PARTITION statement to modify existing attributes of a range partition or list partition. You can modify segment attributes (except TABLESPACE), or you can allocate and deallocate extents, mark local index partitions UNUSABLE, or rebuild local indexes that have been marked UNUSABLE.

If this is a range partition of a *-hash partitioned table, then note the following:

• If you allocate or deallocate an extent, this action is performed for every subpartition of the specified partition.

• Likewise, changing any other attributes results in corresponding changes to those attributes of all the subpartitions for that partition. The partition level default attributes are changed as well. To avoid changing attributes of existing subpartitions, use the FOR PARTITION clause of the MODIFY DEFAULT ATTRIBUTES statement.

The following are some examples of modifying the real attributes of a partition.

This example modifies the MAXEXTENTS storage attribute for the range partition sales_q1 of table sales:

ALTER TABLE sales MODIFY PARTITION sales_q1

     STORAGE (MAXEXTENTS 10); 

All of the local index subpartitions of partition ts1 in range-hash partitioned table scubagear are marked UNUSABLE in the following example:

ALTER TABLE scubagear MODIFY PARTITION ts1 UNUSABLE LOCAL INDEXES;

For an interval-partitioned table you can only modify real attributes of range partitions or interval partitions that have been created.

Modifying Real Attributes for a Hash Partition

You also use the ALTER TABLE ... MODIFY PARTITION statement to modify attributes of a hash partition. However, because the physical attributes of individual hash partitions must all be the same (except for TABLESPACE), you are restricted to:

• Allocating a new extent

• Deallocating an unused extent

• Marking a local index subpartition UNUSABLE

• Rebuilding local index subpartitions that are marked UNUSABLE

The following example rebuilds any unusable local index partitions associated with hash partition P1 of table dept:

ALTER TABLE dept MODIFY PARTITION p1

     REBUILD UNUSABLE LOCAL INDEXES;

Modifying Real Attributes of a Subpartition

With the MODIFY SUBPARTITION clause of ALTER TABLE you can perform the same actions as listed previously for partitions, but at the specific composite partitioned table subpartition level. For example:

ALTER TABLE emp MODIFY SUBPARTITION p3_s1

     REBUILD UNUSABLE LOCAL INDEXES;

Modifying Real Attributes of Index Partitions

The MODIFY PARTITION clause of ALTER INDEX lets you modify the real attributes of an index partition or its subpartitions. The rules are very similar to those for table partitions, but unlike the MODIFY PARTITION clause for ALTER INDEX, there is no subclause to rebuild an unusable index partition, but there is a subclause to coalesce an index partition or its subpartitions. In this context, coalesce means to merge index blocks where possible to free them for reuse.

You can also allocate or deallocate storage for a subpartition of a local index, or mark it UNUSABLE, using the MODIFY PARTITION clause.

Adding Values for a List Partition

Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global indexes, or global or local index partitions, remain usable.

The following statement adds a new set of state codes ('OK', 'KS') to an existing partition list.

ALTER TABLE sales_by_region

   MODIFY PARTITION region_south

      ADD VALUES ('OK', 'KS');

The existence of a default partition can have a performance impact when adding values to other partitions. This is because in order to add values to a list partition, the database must check that the values being added do not already exist in the default partition. If any of the values do exist in the default partition, then an error is raised.

You cannot add values to a default list partition.

Adding Values for a List Subpartition

This operation is essentially the same as described for "Modifying List Partitions: Adding Values", however, you use a MODIFY SUBPARTITION clause instead of the MODIFY PARTITION clause. For example, to extend the range of literal values in the value list for subpartition q1_1999_southeast, use the following statement:

ALTER TABLE quarterly_regional_sales

   MODIFY SUBPARTITION q1_1999_southeast

      ADD VALUES ('KS');

Literal values being added must not have been included in any other subpartition value list within the owning partition. However, they can be duplicates of literal values in the subpartition value lists of other partitions within the table.

For an interval-list composite partitioned table, you can only add values to subpartitions of range partitions or interval partitions that have been created. If you want to add values to subpartitions of interval partitions that have not yet been created, then you have to modify the subpartition template.

Dropping Values from a List Partition

Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that corresponds to the set of values being dropped. If any such rows are found then the database returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.

The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.

The following statement drops a set of state codes ('OK' and 'KS') from an existing partition value list.

ALTER TABLE sales_by_region

   MODIFY PARTITION region_south

      DROP VALUES ('OK', 'KS');

You cannot drop values from a default list partition.

Dropping Values from a List Subpartition

This operation is essentially the same as described for "Modifying List Partitions: Dropping Values", however, you use a MODIFY SUBPARTITION clause instead of the MODIFY PARTITION clause. For example, to remove a set of literal values in the value list for subpartition q1_1999_southeast, use the following statement:

ALTER TABLE quarterly_regional_sales

   MODIFY SUBPARTITION q1_1999_southeast

      DROP VALUES ('KS');

For an interval-list composite partitioned table, you can only drop values from subpartitions of range partitions or interval partitions that have been created. If you want to drop values from subpartitions of interval partitions that have not yet been created, then you have to modify the subpartition template.

Moving Table Partitions

Use the MOVE PARTITION clause to move a partition. For example, to move the most active partition to a tablespace that resides on its own set of disks (in order to balance I/O), not log the action, and compress the data, issue the following statement:

ALTER TABLE parts MOVE PARTITION depot2

     TABLESPACE ts094 NOLOGGING COMPRESS;

This statement always drops the old partition segment and creates a new segment, even if you do not specify a new tablespace.

If you are moving a partition of a partitioned index-organized table, then you can specify the MAPPING TABLE clause as part of the MOVE PARTITION clause, and the mapping table partition will be moved to the new location along with the table partition.

For an interval or interval-* partitioned table, you can only move range partitions or interval partitions that have been created. A partition move operation does not move the transition point in an interval or interval-* partitioned table.

You can move a partition in a reference-partitioned table independent of the partition in the master table.

Moving Subpartitions

The following statement shows how to move data in a subpartition of a table. In this example, a PARALLEL clause has also been specified.

ALTER TABLE scuba_gear MOVE SUBPARTITION bcd_types 

     TABLESPACE tbs23 PARALLEL (DEGREE 2);

You can move a subpartition in a reference-partitioned table independent of the subpartition in the master table.

Moving Index Partitions

The ALTER TABLE ... MOVE PARTITION statement for regular tables, marks all partitions of a global index UNUSABLE. You can rebuild the entire index by rebuilding each partition individually using the ALTER INDEX ... REBUILD PARTITION statement. You can perform these rebuilds concurrently.

You can also simply drop the index and re-create it.

Rebuilding Global Index Partitions

You can rebuild global index partitions in two ways:

• Rebuild each partition by issuing the ALTER INDEX ... REBUILD PARTITION statement (you can run the rebuilds concurrently).

• Drop the entire global index and re-create it. This method is more efficient because the table is scanned only once.

For most maintenance operations on partitioned tables with indexes, you can optionally avoid the need to rebuild the index by specifying UPDATE INDEXES on your DDL statement.

Rebuilding Local Index Partitions

Rebuild local indexes using either ALTER INDEX or ALTER TABLE as follows:

• ALTER INDEX ... REBUILD PARTITION/SUBPARTITION

  This statement rebuilds an index partition or subpartition unconditionally.

• ALTER TABLE ... MODIFY PARTITION/SUBPARTITION ... REBUILD UNUSABLE LOCAL INDEXES

  This statement finds all of the unusable indexes for the given table partition or subpartition and rebuilds them. It only rebuilds an index partition if it has been marked UNUSABLE.

ALTER INDEX scuba

   REBUILD SUBPARTITION bcd_types

   TABLESPACE tbs23 PARALLEL (DEGREE 2);

ALTER TABLE scubagear

   MODIFY PARTITION p1 REBUILD UNUSABLE LOCAL INDEXES;

Renaming a Table Partition

Rename a range, hash, or list partition, using the ALTER TABLE ... RENAME PARTITION statement. For example:

ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks;

Renaming a Table Subpartition

Likewise, you can assign new names to subpartitions of a table. In this case you would use the ALTER TABLE ... RENAME SUBPARTITION syntax.

Renaming Index Partitions

Index partitions and subpartitions can be renamed in similar fashion, but the ALTER INDEX syntax is used.

ALTER INDEX scuba RENAME SUBPARTITION sys_subp3254 TO bcd_types;

Splitting a Partition of a Range-Partitioned Table

You split a range partition using the ALTER TABLE ... SPLIT PARTITION statement. You specify a value of the partitioning key column within the range of the partition at which to split the partition. The first of the resulting two new partitions includes all rows in the original partition whose partitioning key column values map lower that the specified value. The second partition contains all rows whose partitioning key column values map greater than or equal to the specified value.

You can optionally specify new attributes for the two partitions resulting from the split. If there are local indexes defined on the table, this statement also splits the matching partition in each local index.

In the following example fee_katy is a partition in the table vet_cats, which has a local index, jaf1. There is also a global index, vet on the table. vet contains two partitions, vet_parta, and vet_partb.

To split the partition fee_katy, and rebuild the index partitions, issue the following statements:

ALTER TABLE vet_cats SPLIT PARTITION 

      fee_katy at (100) INTO ( PARTITION

      fee_katy1, PARTITION fee_katy2);

ALTER INDEX JAF1 REBUILD PARTITION fee_katy1;

ALTER INDEX JAF1 REBUILD PARTITION fee_katy2;

ALTER INDEX VET REBUILD PARTITION vet_parta;

ALTER INDEX VET REBUILD PARTITION vet_partb;

Splitting a Partition of a List-Partitioned Table

You split a list partition by using the ALTER TABLE ... SPLIT PARTITION statement. The SPLIT PARTITION clause enables you to specify a list of literal values that define a partition into which rows with corresponding partitioning key values are inserted. The remaining rows of the original partition are inserted into a second partition whose value list contains the remaining values from the original partition.

You can optionally specify new attributes for the two partitions that result from the split.

The following statement splits the partition region_east into two partitions:

ALTER TABLE sales_by_region 

   SPLIT PARTITION region_east VALUES ('CT', 'MA', 'MD') 

   INTO 

    ( PARTITION region_east_1 

         TABLESPACE tbs2,

      PARTITION region_east_2

        STORAGE (NEXT 2M PCTINCREASE 25)) 

   PARALLEL 5;

The literal value list for the original region_east partition was specified as:

PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')

The two new partitions are:

• region_east_1 with a literal value list of ('CT','MA','MD')

• region_east_2 inheriting the remaining literal value list of ('NY','NH','ME','VA','PA','NJ')

The individual partitions have new physical attributes specified at the partition level. The operation is executed with parallelism of degree 5.

You can split a default list partition just like you split any other list partition. This is also the only means of adding a partition to a list-partitioned table that contains a default partition. When you split the default partition, you create a new partition defined by the values that you specify, and a second partition that remains the default partition.

The following example splits the default partition of sales_by_region, thereby creating a new partition:

ALTER TABLE sales_by_region 

   SPLIT PARTITION region_unknown VALUES ('MT', 'WY', 'ID') 

   INTO 

    ( PARTITION region_wildwest,

      PARTITION region_unknown);

Splitting a Partition of an Interval-Partitioned Table

You split a range or a materialized interval partition using the ALTER TABLE ... SPLIT PARTITION statement in an interval-partitioned table. Splitting a range partition in the interval-partitioned table is the same as described in "Splitting a Partition of a Range-Partitioned Table".

To split a materialized interval partition, you specify a value of the partitioning key column within the interval partition at which to split the partition. The first of the resulting two new partitions includes all rows in the original partition whose partitioning key column values map lower than the specified value. The second partition contains all rows whose partitioning key column values map greater than or equal to the specified value. The split partition operation will move the transition point up to the higher boundary of the partition you just split, and all materialized interval partitions lower than the newly split partitions are implicitly converted into range partitions, with their upper boundaries defined by the upper boundaries of the intervals.

You can optionally specify new attributes for the two range partitions resulting from the split. If there are local indexes defined on the table, then this statement also splits the matching partition in each local index. You cannot split interval partitions that have not yet been created.

The following example shows splitting the May 2007 partition in the monthly interval partitioned table transactions.

ALTER TABLE transactions

SPLIT PARTITION FOR(TO_DATE('01-MAY-2007','dd-MON-yyyy'))

AT (TO_DATE('15-MAY-2007','dd-MON-yyyy'));

Splitting a *-Hash Partition

This is the opposite of merging *-hash partitions. When you split *-hash partitions, the new subpartitions are rehashed into either the number of subpartitions specified in a SUBPARTITIONS or SUBPARTITION clause. Or, if no such clause is included, the new partitions inherit the number of subpartitions (and tablespaces) from the partition being split.

Note that the inheritance of properties is different when a *-hash partition is split, versus when two *-hash partitions are merged. When a partition is split, the new partitions can inherit properties from the original partition because there is only one parent. However, when partitions are merged, properties must be inherited from table level defaults because there are two parents and the new partition cannot inherit from either at the expense of the other.

The following example splits a range-hash partition:

ALTER TABLE all_seasons SPLIT PARTITION quarter_1 

     AT (TO_DATE('16-dec-1997','dd-mon-yyyy'))

     INTO (PARTITION q1_1997_1 SUBPARTITIONS 4 STORE IN (ts1,ts3),

           PARTITION q1_1997­_2);

The rules for splitting an interval-hash partitioned table follow the rules for splitting an interval-partitioned table. As described in "Splitting a Partition of an Interval-Partitioned Table", the transition point will be changed to the higher boundary of the split partition.

Splitting Partitions in a *-List Partitioned Table

Partitions can be split at both the partition level and at the list subpartition level.

ALTER TABLE quarterly_regional_sales SPLIT PARTITION q1_1999

   AT (TO_DATE('15-Feb-1999','dd-mon-yyyy'))

   INTO ( PARTITION q1_1999_jan_feb

             TABLESPACE ts1,

          PARTITION q1_1999_feb_mar

             STORAGE (NEXT 2M PCTINCREASE 25) TABLESPACE ts2) 

   PARALLEL 5;

SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME

  FROM DBA_TAB_SUBPARTITIONS

  WHERE TABLE_NAME='QUARTERLY_REGIONAL_SALES'

  ORDER BY PARTITION_NAME;


PARTITION_NAME       SUBPARTITION_NAME              TABLESPACE_NAME

-------------------- ------------------------------ ---------------

Q1_1999_FEB_MAR      Q1_1999_FEB_MAR_WEST           TS2

Q1_1999_FEB_MAR      Q1_1999_FEB_MAR_NORTHEAST      TS2

Q1_1999_FEB_MAR      Q1_1999_FEB_MAR_SOUTHEAST      TS2

Q1_1999_FEB_MAR      Q1_1999_FEB_MAR_NORTHCENTRAL   TS2

Q1_1999_FEB_MAR      Q1_1999_FEB_MAR_SOUTHCENTRAL   TS2

Q1_1999_FEB_MAR      Q1_1999_FEB_MAR_SOUTH          TS2

Q1_1999_JAN_FEB      Q1_1999_JAN_FEB_WEST           TS1

Q1_1999_JAN_FEB      Q1_1999_JAN_FEB_NORTHEAST      TS1

Q1_1999_JAN_FEB      Q1_1999_JAN_FEB_SOUTHEAST      TS1

Q1_1999_JAN_FEB      Q1_1999_JAN_FEB_NORTHCENTRAL   TS1

Q1_1999_JAN_FEB      Q1_1999_JAN_FEB_SOUTHCENTRAL   TS1

Q1_1999_JAN_FEB      Q1_1999_JAN_FEB_SOUTH          TS1

Q1_2000              Q1_2000_NORTHWEST              TS3

Q1_2000              Q1_2000_SOUTHWEST              TS3

Q1_2000              Q1_2000_NORTHEAST              TS3

Q1_2000              Q1_2000_SOUTHEAST              TS3

Q1_2000              Q1_2000_NORTHCENTRAL           TS3

Q1_2000              Q1_2000_SOUTHCENTRAL           TS3

Q2_1999              Q2_1999_NORTHWEST              TS4

Q2_1999              Q2_1999_SOUTHWEST              TS4

Q2_1999              Q2_1999_NORTHEAST              TS4

Q2_1999              Q2_1999_SOUTHEAST              TS4

Q2_1999              Q2_1999_NORTHCENTRAL           TS4

Q2_1999              Q2_1999_SOUTHCENTRAL           TS4

Q3_1999              Q3_1999_NORTHWEST              TS4

Q3_1999              Q3_1999_SOUTHWEST              TS4

Q3_1999              Q3_1999_NORTHEAST              TS4

Q3_1999              Q3_1999_SOUTHEAST              TS4

Q3_1999              Q3_1999_NORTHCENTRAL           TS4

Q3_1999              Q3_1999_SOUTHCENTRAL           TS4

Q4_1999              Q4_1999_NORTHWEST              TS4

Q4_1999              Q4_1999_SOUTHWEST              TS4

Q4_1999              Q4_1999_NORTHEAST              TS4

Q4_1999              Q4_1999_SOUTHEAST              TS4

Q4_1999              Q4_1999_NORTHCENTRAL           TS4

Q4_1999              Q4_1999_SOUTHCENTRAL           TS4


36 rows selected.

ALTER TABLE quarterly_regional_sales SPLIT SUBPARTITION q2_1999_southwest

   VALUES ('UT') INTO

      ( SUBPARTITION q2_1999_utah

           TABLESPACE ts2,

        SUBPARTITION q2_1999_southwest

           TABLESPACE ts3

      ) 

   PARALLEL;

Splitting a *-Range Partition

Splitting a partition of a *-range partitioned table is similar to what is described in "Splitting a Partition of a Range-Partitioned Table". No subpartition range values can be specified for either of the new partitions. The new partitions inherit the subpartition descriptions from the original partition being split.

The following example splits the May 2007 interval partition of the interval-range partitioned orders table:

ALTER TABLE orders

SPLIT PARTITION FOR(TO_DATE('01-MAY-2007','dd-MON-yyyy'))

AT (TO_DATE('15-MAY-2007','dd-MON-yyyy'))

INTO (PARTITION p_fh_may07,PARTITION p_sh_may2007);

This operation splits the interval partition FOR('01-MAY-2007') into two resulting partitions: p_fh_may07 and p_sh_may_2007. Both partitions inherit their subpartition descriptions from the original partition. Any interval partitions before the June 2007 partition have been converted into range partitions, as described in "Merging Interval Partitions".

The ALTER TABLE ... SPLIT PARTITION statement provides no means of specifically naming subpartitions resulting from the split of a partition in a composite partitioned table. However, for those subpartitions in the parent partition with names of the form partition name_subpartition name, the database generates corresponding names in the newly created subpartitions using the new partition names. All other subpartitions are assigned system generated names of the form SYS_SUBPn. System generated names are also assigned for the subpartitions of any partition resulting from the split for which a name is not specified. Unnamed partitions are assigned a system generated partition name of the form SYS_Pn.

The following query displays the subpartition names and high values resulting from the previous split partition operation on table orders. It also reflects the results of other operations performed on this table in preceding sections of this chapter since its creation.

BREAK ON partition_name


SELECT partition_name, subpartition_name, high_value

FROM user_tab_subpartitions

WHERE table_name = 'ORCERS'

ORDER BY partition_name, subpartition_position;


PARTITION_NAME            SUBPARTITION_NAME              HIGH_VALUE

------------------------- ------------------------------ ---------------

P_BEFORE_2007             P_BEFORE_2007_P_SMALL          1000

                          P_BEFORE_2007_P_MEDIUM         10000

                          P_BEFORE_2007_P_LARGE          100000

                          P_BEFORE_2007_P_EXTRAORDINARY  MAXVALUE

P_FH_MAY07                SYS_SUBP2985                   1000

                          SYS_SUBP2986                   10000

                          SYS_SUBP2987                   100000

                          SYS_SUBP2988                   MAXVALUE

P_PRE_MAY_2007            P_PRE_MAY_2007_P_SMALL         1000

                          P_PRE_MAY_2007_P_MEDIUM        10000

                          P_PRE_MAY_2007_P_LARGE         100000

                          P_PRE_MAY_2007_P_EXTRAORDINARY MAXVALUE

P_SH_MAY2007              SYS_SUBP2989                   1000

                          SYS_SUBP2990                   10000

                          SYS_SUBP2991                   100000

                          SYS_SUBP2992                   MAXVALUE

ALTER TABLE orders

SPLIT SUBPARTITION p_pre_may_2007_p_large AT (50000)

INTO (SUBPARTITION p_pre_may_2007_med_large TABLESPACE TS4

     , SUBPARTITION p_pre_may_2007_large_large TABLESPACE TS5

     );

Splitting Index Partitions

You cannot explicitly split a partition in a local index. A local index partition is split only when you split a partition in the underlying table. However, you can split a global index partition as is done in the following example:

ALTER INDEX quon1 SPLIT 

    PARTITION canada AT ( 100 ) INTO 

    PARTITION canada1 ..., PARTITION canada2 ...);

ALTER INDEX quon1 REBUILD PARTITION canada1;

ALTER INDEX quon1 REBUILD PARTITION canada2;

The index being split can contain index data, and the resulting partitions do not require rebuilding, unless the original partition was previously marked UNUSABLE.

Optimizing SPLIT PARTITION and SPLIT SUBPARTITION Operations

Oracle Database implements a SPLIT PARTITION operation by creating two new partitions and redistributing the rows from the partition being split into the two new partitions. This is an expensive operation because it is necessary to scan all the rows of the partition being split and then insert them one-by-one into the new partitions. Further if you do not use the UPDATE INDEXES clause, both local and global indexes also require rebuilding.

Sometimes after a split operation, one of the new partitions contains all of the rows from the partition being split, while the other partition contains no rows. This is often the case when splitting the first partition of a table. The database can detect such situations and can optimize the split operation. This optimization results in a fast split operation that behaves like an add partition operation.

Specifically, the database can optimize and speed up SPLIT PARTITION operations if all of the following conditions are met:

• One of the two resulting partitions must be empty.

• The non-empty resulting partition must have storage characteristics identical to those of the partition being split. Specifically:

  • If the partition being split is composite, then the storage characteristics of each subpartition in the new non-empty resulting partition must be identical to those of the subpartitions of the partition being split.

  • If the partition being split contains a LOB column, then the storage characteristics of each LOB (sub)partition in the new non-empty resulting partition must be identical to those of the LOB (sub)partitions of the partition being split.

  • If a partition of an index-organized table with overflow is being split, then the storage characteristics of each overflow (sub)partition in the new nonempty resulting partition must be identical to those of the overflow (sub)partitions of the partition being split.

  • If a partition of an index-organized table with mapping table is being split, then the storage characteristics of each mapping table (sub)partition in the new nonempty resulting partition must be identical to those of the mapping table (sub)partitions of the partition being split.

If these conditions are met after the split, then all global indexes remain usable, even if you did not specify the UPDATE INDEXES clause. Local index (sub)partitions associated with both resulting partitions remain usable if they were usable before the split. Local index (sub)partition(s) corresponding to the non-empty resulting partition will be identical to the local index (sub)partition(s) of the partition that was split.

The same optimization holds for SPLIT SUBPARTITION operations.

Truncating a Table Partition

Use the ALTER TABLE ... TRUNCATE PARTITION statement to remove all rows from a table partition, with or without reclaiming space. Truncating a partition in an interval-partitioned table does not move the transition point. You can truncate partitions and subpartitions in a reference-partitioned table.

ALTER TABLE sales TRUNCATE PARTITION dec98;

ALTER INDEX sales_area_ix REBUILD;

DELETE FROM sales PARTITION (dec98);

ALTER TABLE sales TRUNCATE PARTITION dec98;

ALTER TABLE sales TRUNCATE PARTITION dec98

     UPDATE INDEXES;

DELETE FROM sales partition (dec94);

ALTER TABLE sales TRUNCATE PARTITION dec94;

Truncating a Subpartition

You use the ALTER TABLE ... TRUNCATE SUBPARTITION statement to remove all rows from a subpartition of a composite partitioned table. Corresponding local index subpartitions are also truncated.

The following statement shows how to truncate data in a subpartition of a table. In this example, the space occupied by the deleted rows is made available for use by other schema objects in the tablespace.

ALTER TABLE diving

   TRUNCATE SUBPARTITION us_locations

      DROP STORAGE;