Partition Administration

4 Partition Administration

Partition administration is an important task when working with partitioned tables and indexes.

This chapter describes various aspects of creating and maintaining partitioned tables and indexes.

This chapter contains the following sections:

Note:

Before you attempt to create a partitioned table or index, or perform maintenance operations on any partitioned table, it is recommended that you review the information in Partitioning Concepts.

See Also:

Oracle Database SQL Language Reference for general restrictions on partitioning, the exact syntax of the partitioning clauses for creating and altering partitioned tables and indexes, any restrictions on their use, and specific privileges required for creating and altering tables

4.1 Specifying Partitioning When Creating Tables and Indexes

Creating a partitioned table or index is very similar to creating a nonpartitioned table or index.

When creating a partitioned table or index, you include a partitioning clause in the CREATE TABLE statement. The partitioning clause, and subclauses, that you include depend upon the type of partitioning you want to achieve.

Partitioning is possible on both regular (heap organized) tables and index-organized tables, except for those containing LONG or LONG RAW columns. You can create nonpartitioned global indexes, range or hash partitioned global indexes, and local indexes on partitioned tables.

When you create (or alter) a partitioned table, a row movement clause (either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT) can be specified. This clause either enables or disables the migration of a row to a new partition if its key is updated. The default is DISABLE ROW MOVEMENT.

You can specify up to a total of 1024K-1 partitions for a single-level partitioned tables, or subpartitions for a composite partitioned table.

Creating automatic list composite partitioned tables and interval subpartitions can save space because these methods only create subpartitions in the presence of data. Deferring subpartition segment creation when creating new partitions on demand ensures that a subpartition segment is only created when the first matching row is inserted.

The following topics present details and examples of creating partitions for the various types of partitioned tables and indexes:

See Also:

Oracle Database Administrator's Guide for information about managing tables
Oracle Database SQL Language Reference for the exact syntax of the partitioning clauses for creating and altering partitioned tables and indexes, any restrictions on their use, and specific privileges required for creating and altering tables
Oracle Database SecureFiles and Large Objects Developer's Guide for information specific to creating partitioned tables containing columns with LOBs or other objects stored as LOBs
Oracle Database Object-Relational Developer's Guide for information specific to creating tables with object types, nested tables, or VARRAYs

4.1.1 About Creating Range-Partitioned Tables and Global Indexes

The PARTITION BY RANGE clause of the CREATE TABLE statement specifies that the table or index is to be range-partitioned.

The PARTITION clauses identify the individual partition ranges, and the optional subclauses of a PARTITION clause can specify physical and other attributes specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

The following topics are discussed:

4.1.1.1 Creating a Range-Partitioned Table

Use the PARTITION BY RANGE clause of the CREATE TABLE statement to create a range-partitioned table.

Example 4-1 creates a table of four partitions, one for each quarter of sales. time_id is the partitioning column, while its 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_2006, sales_q2_2006, sales_q3_2006, sales_q4_2006), and each partition is contained in a separate tablespace (tsa, tsb, tsc, tsd). A row with time_id=17-MAR-2006 would be stored in partition sales_q1_2006.

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Range Partitioned Table.

Example 4-1 Creating a range-partitioned table

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
 );

4.1.1.2 Creating a Range-Partitioned Table With More Complexity

With attributes and storage parameters, more complexity can be added to the creation of a range-partitioned table.

In Example 4-2, 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.

Example 4-2 Creating a range-partitioned table with LOGGING and ENABLE ROW MOVEMENT

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;

4.1.1.3 Creating a Range-Partitioned Global Index

The rules for creating range-partitioned global indexes are similar to those for creating range-partitioned tables.

Example 4-3 creates a range-partitioned global index on sale_month for the tables created in the previous examples. Each index partition is named but is stored in the default tablespace for the index.

Example 4-3 Creating a range-partitioned global index table

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)
      );

Note:

If your enterprise has databases using different character sets, use caution when partitioning on character columns, because the sort sequence of characters is not identical in all character sets. For more information, refer to Oracle Database Globalization Support Guide

4.1.2 Creating Range-Interval-Partitioned Tables

The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table.

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 lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

For example, if you create an interval partitioned table with monthly intervals and the transition point is at January 1, 2010, then the lower boundary for the January 2010 interval is January 1, 2010. The lower boundary for the July 2010 interval is July 1, 2010, regardless of whether the June 2010 partition was previously created. Note, however, that using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.

The optional STORE IN clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.

For interval partitioning, you can specify only one partitioning key column and the datatype is restricted.

The following example specifies four partitions with varying interval widths. It also specifies that above the transition point of January 1, 2010, partitions are created with an interval width of one month. The high bound of partition p3 represents the transition point. p3 and all partitions below it (p0, p1, and p2 in this example) are in the range section while all partitions above it fall into the interval section.

CREATE TABLE interval_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'))
    ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
      PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
      PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
      PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

See Also:

Oracle Database SQL Language Reference for restrictions on partitioning keys, the exact syntax of the partitioning clauses for creating and altering partitioned tables and indexes, any restrictions on their use, and specific privileges required for creating and altering tables.

4.1.3 About Creating Hash Partitioned Tables and Global Indexes

The PARTITION BY HASH clause of the CREATE TABLE statement identifies that the table is to be hash partitioned.

The PARTITIONS clause can then be used to specify the number of partitions to create, and optionally, the tablespaces to store them in. Alternatively, you can use PARTITION clauses to name the individual partitions and their tablespaces.

The only attribute you can specify for hash partitions is TABLESPACE. All of the hash partitions of a table must share the same segment attributes (except TABLESPACE), which are inherited from the table level.

The following topics are discussed:

4.1.3.1 Creating a Hash Partitioned Table

The example in this topic shows how to create 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, gear3, gear4).

CREATE TABLE scubagear
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4 
   STORE IN (gear1, gear2, gear3, gear4);

In the following example, the number of partitions is specified when creating a hash partitioned table, but system generated names are assigned to them and they are stored in the default tablespace of the table.

CREATE TABLE departments_hash (department_id NUMBER(4) NOT NULL, 
             department_name VARCHAR2(30))
     PARTITION BY HASH(department_id) 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 departments_hash (department_id NUMBER(4) NOT NULL, 
             department_name VARCHAR2(30))
     STORAGE (INITIAL 10K)
     PARTITION BY HASH(department_id)
       (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 a table:

CREATE INDEX loc_dept_ix ON departments_hash(department_id) 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, then 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.

See Also:

Specifying Partitioning on Key Columns for more information about partitioning on key columns

4.1.3.2 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 statement in Example 4-4 creates a hash partitioned global index:

Example 4-4 Creating 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);

4.1.4 About Creating List-Partitioned Tables

The semantics for creating list partitions are very similar to those for creating range partitions.

However, to create list partitions, you specify a PARTITION BY LIST clause in the CREATE TABLE statement, and the PARTITION clauses specify lists of literal values, which are the discrete values of the partitioning columns that qualify rows to be included in the partition. For list partitioning, the partitioning key can be one or multiple column names from the table.

Available only with list partitioning, you can use the keyword DEFAULT to describe the value list for a partition. This identifies a partition that accommodates rows that do not map into any of the other partitions.

As with range partitions, optional subclauses of a PARTITION clause can specify physical and other attributes specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their parent table.

The following topics are discussed:

4.1.4.1 Creating a List-Partitioned Table

The example in this topic show how to create a list-partitioned table.

Example 4-5creates table q1_sales_by_region which is partitioned by regions consisting of groups of US states. A row is mapped to a partition by checking whether the value of the partitioning column for a row matches a value in the value list that describes the partition. For example, the following list describes how some sample rows are inserted into the table.

(10, 'accounting', 100, 'WA') maps to partition q1_northwest
(20, 'R&D', 150, 'OR') maps to partition q1_northwest
(30, 'sales', 100, 'FL') maps to partition q1_southeast
(40, 'HR', 10, 'TX') maps to partition q1_southwest
(50, 'systems engineering', 10, 'CA') does not map to any partition in the table and raises an error

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a List Partitioned Table.

Example 4-5 Creating a list-partitioned table

CREATE TABLE q1_sales_by_region
      (deptno number, 
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

4.1.4.2 Creating a List-Partitioned Table With a Default Partition

Unlike range partitioning, with list partitioning, there is no apparent sense of order between partitions.

You can also specify a default partition into which rows that do not map to any other partition are mapped. If a default partition were specified in the preceding example, the state CA would map to that partition.

Example 4-6 creates table sales_by_region and partitions it using the list method. The first two PARTITION clauses specify physical attributes, which override the table-level defaults. The remaining PARTITION clauses do not specify attributes and those partitions inherit their physical attributes from table-level defaults. A default partition is also specified.

Example 4-6 Creating a list-partitioned table with a default partition

CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER, 
             store_name VARCHAR(30), state_code VARCHAR(2),
             sale_date DATE)
     STORAGE(INITIAL 10K NEXT 20K) TABLESPACE tbs5 
     PARTITION BY LIST (state_code) 
     (
     PARTITION region_east
        VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
        STORAGE (INITIAL 8M) 
        TABLESPACE tbs8,
     PARTITION region_west
        VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
        NOLOGGING,
     PARTITION region_south
        VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
     PARTITION region_central 
        VALUES ('OH','ND','SD','MO','IL','MI','IA'),
     PARTITION region_null
        VALUES (NULL),
     PARTITION region_unknown
        VALUES (DEFAULT)
     );

4.1.4.3 Creating an Automatic List-Partitioned Table

The automatic list partitioning method enables list partition creation on demand.

An auto-list partitioned table is similar to a regular list partitioned table, except that this partitioned table is easier to manage. You can create an auto-list partitioned table using only the partitioning key values that are known. As data is loaded into the table, the database automatically creates a new partition if the loaded partitioning key value does not correspond to any of the existing partitions. Because partitions are automatically created on demand, the auto-list partitioning method is conceptually similar to the existing interval partitioning method.

Automatic list partitioning on data types whose value changes very frequently are less suitable for this method unless you can adjust the data. For example, a SALES_DATE field with a date value, when the format is not stripped, would increase every second. Each of the SALES_DATE values, such as 05-22-2016 08:00:00, 05-22-2016 08:00:01, and so on, would generate its own partition. To avoid the creation of a very large number of partitions, you must be aware of the data that would be entered and adjust accordingly. As an example, you can truncate the SALES_DATE date value to a day or some other time period, depending on the number of partitions required.

The CREATE and ALTER TABLE SQL statements are updated with an additional clause to specify AUTOMATIC or MANUAL list partitioning. An automatic list-partitioned table must have at least one partition when created. Because new partitions are automatically created for new, and unknown, partition key values, an automatic list partition cannot have a DEFAULT partition.

You can check the AUTOLIST column of the *_PART_TABLES view to determine whether a table is automatic list-partitioned.

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating an Automatic List-Partitioned Table.

Example 4-7 is an example of the CREATE TABLE statement using the AUTOMATIC keyword for auto-list partitioning on the sales_state field. The CREATE TABLE SQL statement creates at least one partition as required. As additional rows are inserted, the number of partitions increases when a new sales_state value is added.

Example 4-7 Creating an automatic list partitioned table

CREATE TABLE sales_auto_list
(
   salesperson_id   NUMBER(5)    NOT NULL,
   salesperson_name VARCHAR2(30),
   sales_state   VARCHAR2(20) NOT NULL,
   sales_amount  NUMBER(10),
   sales_date    DATE         NOT NULL
)
  PARTITION BY LIST (sales_state) AUTOMATIC
 (PARTITION P_CAL VALUES ('CALIFORNIA')
);

SELECT TABLE_NAME, PARTITIONING_TYPE, AUTOLIST, PARTITION_COUNT FROM USER_PART_TABLES WHERE TABLE_NAME ='SALES_AUTO_LIST';
TABLE_NAME        PARTITIONING_TYPE  AUTOLIST  PARTITION_COUNT
----------------  -----------------  --------  ---------------
SALES_AUTO_LIST   LIST               YES                     1

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_AUTO_LIST';

TABLE_NAME        PARTITION_NAME    HIGH_VALUE
–---------------  –--------------   –---------------
SALES_AUTO_LIST     P_CAL           'CALIFORNIA'

INSERT INTO SALES_AUTO_LIST VALUES(021, 'Mary Smith', 'FLORIDA', 41000, TO_DATE ('21-DEC-2018','DD-MON-YYYY'));
1 row inserted.

INSERT INTO SALES_AUTO_LIST VALUES(032, 'Luis Vargas', 'MICHIGAN', 42000, TO_DATE ('31-DEC-2018','DD-MON-YYYY'));
1 row inserted.

SELECT TABLE_NAME, PARTITIONING_TYPE, AUTOLIST, PARTITION_COUNT FROM USER_PART_TABLES WHERE TABLE_NAME ='SALES_AUTO_LIST';
TABLE_NAME        PARTITIONING_TYPE  AUTOLIST  PARTITION_COUNT
----------------  -----------------  --------  ---------------
SALES_AUTO_LIST   LIST               YES                     3

INSERT INTO SALES_AUTO_LIST VALUES(015, 'Simone Blair', 'CALIFORNIA', 45000, TO_DATE ('11-JAN-2019','DD-MON-YYYY'));
1 row inserted.

INSERT INTO SALES_AUTO_LIST VALUES(015, 'Simone Blair', 'OREGON', 38000, TO_DATE ('18-JAN-2019','DD-MON-YYYY'));
1 row inserted.

SELECT TABLE_NAME, PARTITIONING_TYPE, AUTOLIST,PARTITION_COUNT FROM USER_PART_TABLES WHERE TABLE_NAME ='SALES_AUTO_LIST';
TABLE_NAME        PARTITIONING_TYPE  AUTOLIST  PARTITION_COUNT
----------------  -----------------  --------  ---------------
SALES_AUTO_LIST   LIST               YES                     4


SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_AUTO_LIST';
TABLE_NAME        PARTITION_NAME    HIGH_VALUE
–---------------  –--------------   –---------------
SALES_AUTO_LIST     P_CAL           'CALIFORNIA'
SALES_AUTO_LIST     SYS_P478        'FLORIDA'
SALES_AUTO_LIST     SYS_P479        'MICHIGAN'
SALES_AUTO_LIST     SYS_P480        'OREGON'

See Also:

Oracle Database Reference for information about *_PART_TABLES view

4.1.4.4 Creating a Multi-column List-Partitioned Table

Multi-column list partitioning enables you to partition a table based on list values of multiple columns.

Similar to single-column list partitioning, individual partitions can contain sets containing lists of values.

Multi-column list partitioning is supported on a table using the PARTITION BY LIST clause on multiple columns of a table. For example:

PARTITION BY LIST (column1,column2)

A multi-column list-partitioned table can only have one DEFAULT partition.

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Multicolumn List-Partitioned Table.

The following is an example of the CREATE TABLE statement using multi-column partitioning on the state and channel columns.

Example 4-8 Creating a multicolumn list-partitioned table

CREATE TABLE sales_by_region_and_channel
 (dept_number      NUMBER NOT NULL, 
  dept_name        VARCHAR2(20),
  quarterly_sales  NUMBER(10,2),
  state            VARCHAR2(2),
  channel          VARCHAR2(1)
 )
 PARTITION BY LIST (state, channel)
 (
  PARTITION yearly_west_direct VALUES (('OR','D'),('UT','D'),('WA','D')),
  PARTITION yearly_west_indirect VALUES (('OR','I'),('UT','I'),('WA','I')),
  PARTITION yearly_south_direct VALUES (('AZ','D'),('TX','D'),('GA','D')),
  PARTITION yearly_south_indirect VALUES (('AZ','I'),('TX','I'),('GA','I')),
  PARTITION yearly_east_direct VALUES (('PA','D'), ('NC','D'), ('MA','D')),
  PARTITION yearly_east_indirect VALUES (('PA','I'), ('NC','I'), ('MA','I')),
  PARTITION yearly_north_direct VALUES (('MN','D'),('WI','D'),('MI','D')),
  PARTITION yearly_north_indirect VALUES (('MN','I'),('WI','I'),('MI','I')),
  PARTITION yearly_ny_direct VALUES ('NY','D'),
  PARTITION yearly_ny_indirect VALUES ('NY','I'),
  PARTITION yearly_ca_direct VALUES ('CA','D'),
  PARTITION yearly_ca_indirect VALUES ('CA','I'),
  PARTITION rest VALUES (DEFAULT)
 );

SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_BY_REGION_AND_CHANNEL';
PARTITION_NAME            HIGH_VALUE
---------------------     -------------------------------------------
REST                      DEFAULT
YEARLY_CA_DIRECT          ( 'CA', 'D' )
YEARLY_CA_INDIRECT        ( 'CA', 'I' )
YEARLY_EAST_DIRECT        ( 'PA', 'D' ), ( 'NC', 'D' ), ( 'MA', 'D' )
YEARLY_EAST_INDIRECT      ( 'PA', 'I' ), ( 'NC', 'I' ), ( 'MA', 'I' )
YEARLY_NORTH_DIRECT       ( 'MN', 'D' ), ( 'WI', 'D' ), ( 'MI', 'D' )
YEARLY_NORTH_INDIRECT     ( 'MN', 'I' ), ( 'WI', 'I' ), ( 'MI', 'I' )
YEARLY_NY_DIRECT          ( 'NY', 'D' )
YEARLY_NY_INDIRECT        ( 'NY', 'I' )
YEARLY_SOUTH_DIRECT       ( 'AZ', 'D' ), ( 'TX', 'D' ), ( 'GA', 'D' )
YEARLY_SOUTH_INDIRECT     ( 'AZ', 'I' ), ( 'TX', 'I' ), ( 'GA', 'I' )
YEARLY_WEST_DIRECT        ( 'OR', 'D' ), ( 'UT', 'D' ), ( 'WA', 'D' )
YEARLY_WEST_INDIRECT      ( 'OR', 'I' ), ( 'UT', 'I' ), ( 'WA', 'I' )
13 rows selected.

INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (005, 'AUTO DIRECT', 701000, 'OR', 'D' );
INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (006, 'AUTO INDIRECT', 1201000, 'OR', 'I' );
INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (005, 'AUTO DIRECT', 625000, 'WA', 'D' );
INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (006, 'AUTO INDIRECT', 945000, 'WA', 'I' );
INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (005, 'AUTO DIRECT', 595000, 'UT', 'D' );
INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (006, 'AUTO INDIRECT', 825000, 'UT', 'I' );
INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (003, 'AUTO DIRECT', 1950000, 'CA', 'D' );
INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (004, 'AUTO INDIRECT', 5725000, 'CA', 'I' );
INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (010, 'AUTO DIRECT', 925000, 'IL', 'D' );
INSERT INTO SALES_BY_REGION_AND_CHANNEL VALUES (010, 'AUTO INDIRECT', 3250000, 'IL', 'I' );

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(yearly_west_direct);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST C
----------- -------------------- --------------- -- -
          5 AUTO DIRECT                   701000 OR D
          5 AUTO DIRECT                   625000 WA D
          5 AUTO DIRECT                   595000 UT D

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(yearly_west_indirect);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST C
----------- -------------------- --------------- -- -
          6 AUTO INDIRECT                1201000 OR I
          6 AUTO INDIRECT                 945000 WA I
          6 AUTO INDIRECT                 825000 UT I

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(yearly_ca_direct);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST C
----------- -------------------- --------------- -- -
          3 AUTO DIRECT                  1950000 CA D

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(yearly_ca_indirect);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST C
----------- -------------------- --------------- -- -
          4 AUTO INDIRECT                5725000 CA I

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(rest);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST C
----------- -------------------- --------------- -- -
         10 AUTO DIRECT                   925000 IL D
         10 AUTO INDIRECT                3250000 IL I

4.1.5 Creating Reference-Partitioned Tables

To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement.

The PARTITION BY REFERENCE clause specifies the name of a referential constraint and this constraint becomes the partitioning referential constraint that is used as the basis for reference partitioning in the table. The referential constraint must be enabled and enforced.

As with other partitioned tables, you can specify object-level default attributes, and you can optionally specify partition descriptors that override the object-level defaults on a per-partition basis.

Example 4-9 creates a parent table orders which is range-partitioned on order_date. The reference-partitioned child table order_items is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition.

If partition descriptors are provided, then the number of partitions described must exactly equal the number of partitions or subpartitions in the referenced table. If the parent table is a composite partitioned table, then the table has one partition for each subpartition of its parent; otherwise the table has one partition for each partition of its parent.

Partition bounds cannot be specified for the partitions of a reference-partitioned table.

The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it inherits its name from the corresponding partition in the parent table, unless this inherited name conflicts with an existing explicit name. In this case, the partition has a system-generated name.

Partitions of a reference-partitioned table collocate with the corresponding partition of the parent table, if no explicit tablespace is specified for the reference-partitioned table's partition.

Example 4-9 Creating reference-partitioned tables

CREATE TABLE orders
    ( order_id           NUMBER(12),
      order_date         DATE,
      order_mode         VARCHAR2(8),
      customer_id        NUMBER(6),
      order_status       NUMBER(2),
      order_total        NUMBER(8,2),
      sales_rep_id       NUMBER(6),
      promotion_id       NUMBER(6),
      CONSTRAINT orders_pk PRIMARY KEY(order_id)
    )
  PARTITION BY RANGE(order_date)
    ( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
      PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
      PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
      PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
    );

CREATE TABLE order_items
    ( order_id           NUMBER(12) NOT NULL,
      line_item_id       NUMBER(3)  NOT NULL,
      product_id         NUMBER(6)  NOT NULL,
      unit_price         NUMBER(8,2),
      quantity           NUMBER(8),
      CONSTRAINT order_items_fk
      FOREIGN KEY(order_id) REFERENCES orders(order_id)
    )
    PARTITION BY REFERENCE(order_items_fk);

4.1.6 Creating Interval-Reference Partitioned Tables

You can use interval partitioned tables as parent tables for reference partitioning. Partitions in a reference-partitioned table corresponding to interval partitions in the parent table are created when inserting records into the reference partitioned table.

When creating an interval partition in a child table, the partition name is inherited from the associated parent table fragment. If the child table has a table-level default tablespace, then it is used as tablespace for the new interval partition; otherwise, the tablespace is inherited from the parent table fragment.

The SQL ALTER TABLE SET INTERVAL statement is not allowed for reference-partitioned tables, but can be run on tables that have reference-partitioned children. In particular, ALTER TABLE SET INTERVAL removes the interval property from the targeted table and converts any interval-reference children to ordinary reference-partitioned tables. Also, the SQL ALTER TABLE SET STORE IN statement is not allowed for reference-partitioned tables, but can be run on tables that have reference-partitioned children.

Operations that transform interval partitions to conventional partitions in the parent table, such as ALTER TABLE SPLIT PARTITION on an interval partition, construct the corresponding transformation in the child table, creating partitions in the child table as necessary.

For example, the following SQL statements provides three interval partitions in the parent table and none in the child table:

CREATE TABLE par(pk INT CONSTRAINT par_pk PRIMARY KEY, i INT)
 PARTITION BY RANGE(i) INTERVAL (10)
 (PARTITION p1 VALUES LESS THAN (10));

CREATE TABLE chi(fk INT NOT NULL, i INT,
 CONSTRAINT chi_fk FOREIGN KEY(fk) REFERENCES par(pk))
 PARTITION BY REFERENCE(chi_fk);

INSERT INTO par VALUES(15, 15);
INSERT INTO par VALUES(25, 25);
INSERT INTO par VALUES(35, 35);

You can display information about partitions with the USER_TAB_PARTITIONS view:

SELECT table_name, partition_position, high_value, interval
   FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI')
   ORDER BY 1, 2;

TABLE_NAME       PARTITION_POSITION HIGH_VALUE INT
---------------- ------------------ ---------- ---
CHI                               1            NO
PAR                               1         10 NO
PAR                               2         20 YES
PAR                               3         30 YES
PAR                               4         40 YES

If the interval partition is split in the parent table, then some interval partitions are converted to conventional partitions for all tables in the hierarchy, creating conventional partitions in the child table in the process. For example:

ALTER TABLE par SPLIT PARTITION FOR (25) AT (25)
   INTO (partition x, partition y);

SELECT table_name, partition_position, high_value, interval
   FROM USER_TAB_PARTITIONS WHERE table_name IN ('PAR', 'CHI')
   ORDER BY 1, 2;

TABLE_NAME       PARTITION_POSITION HIGH_VALUE INT
---------------- ------------------ ---------- ---
CHI                               1            NO
CHI                               2            NO
CHI                               3            NO
CHI                               4            NO
PAR                               1         10 NO
PAR                               2         20 NO
PAR                               3         25 NO
PAR                               4         30 NO
PAR                               5         40 YES

Interval-reference functionality requires that the database compatibility level (Oracle Database COMPATIBLE initialization parameter setting) be set to greater than or equal to 12.0.0.0.

4.1.7 Creating a Table Using In-Memory Column Store With Partitioning

You can create a partitioned table using the In-Memory Column Store with the INMEMORY clause.

The following example specifies that individual partitions are loaded into the In-Memory Column Store using the INMEMORY clause with the partitioning clauses of the CREATE TABLE SQL statements.

CREATE TABLE list_customers 
   ( customer_id             NUMBER(6)
   , cust_first_name         VARCHAR2(20) 
   , cust_last_name          VARCHAR2(20)
   , cust_address            CUST_ADDRESS_TYP
   , nls_territory           VARCHAR2(30)
   , cust_email              VARCHAR2(40))
   PARTITION BY LIST (nls_territory) (
   PARTITION asia VALUES ('CHINA', 'THAILAND') 
         INMEMORY MEMCOMPRESS FOR CAPACITY HIGH,
   PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND') 
         INMEMORY MEMCOMPRESS FOR CAPACITY LOW,
   PARTITION west VALUES ('AMERICA') 
         INMEMORY MEMCOMPRESS FOR CAPACITY LOW,
   PARTITION east VALUES ('INDIA') 
         INMEMORY MEMCOMPRESS FOR CAPACITY HIGH,
   PARTITION rest VALUES (DEFAULT);

See Also:

Oracle Database In-Memory Guide for overview information about In-Memory Column Store
Oracle Database In-Memory Guide for information about enabling objects for population in the In-Memory Column Store and ADO support
Oracle Database SQL Language Reference for information about SQL syntax related to In-Memory Column Store

4.1.8 Creating a Table with Read-Only Partitions or Subpartitions

You can set tables, partitions, and subpartitions to read-only status to protect data from unintentional DML operations by any user or trigger.

Any attempt to update data in a partition or subpartition that is set to read only results in an error, while updating data in partitions or subpartitions that are set to read write succeeds.

The CREATE TABLE and ALTER TABLE SQL statements provide a read-only clause for partitions and subpartitions. The values of the read-only clause can be READ ONLY or READ WRITE. READ WRITE is the default value. A higher level setting of the read-only clause is applied to partitions and subpartitions unless the read-only clause has been explicitly set for a partition or subpartition.

The following is an example of a creating a composite range-list partitioned table with both read-only and read-write status. The orders_read_write_only is explicitly specified as READ WRITE, so the default attribute of the table is read write. The default attribute of partition order_p1 is specified as read only, so the subpartitions ord_p1_northwest and order_p1_southwest inherit read only status from partition order_p1. Subpartitions ord_p2_southwest and order_p3_northwest are explicitly specified as read only, overriding the default read write status.

Example 4-10 Creating a table with read-only and read-write partitions

CREATE TABLE orders_read_write_only (
   order_id NUMBER (12),
   order_date DATE CONSTRAINT order_date_nn NOT NULL,
   state VARCHAR2(2)
   ) READ WRITE
     PARTITION BY RANGE (order_date)
     SUBPARTITION BY LIST (state)
     ( PARTITION order_p1 VALUES LESS THAN (TO_DATE ('01-DEC-2015','DD-MON-YYYY')) READ ONLY
     ( SUBPARTITION order_p1_northwest VALUES ('OR', 'WA'),
       SUBPARTITION order_p1_southwest VALUES ('AZ', 'UT', 'NM')
      ),
     PARTITION order_p2 VALUES LESS THAN (TO_DATE ('01-MAR-2016','DD-MON-YYYY'))
     ( SUBPARTITION order_p2_northwest VALUES ('OR', 'WA'),
       SUBPARTITION order_p2_southwest VALUES ('AZ', 'UT', 'NM') READ ONLY
      ),
     PARTITION order_p3 VALUES LESS THAN (TO_DATE ('01-JUL-2016','DD-MON-YYYY'))
     (
     SUBPARTITION order_p3_northwest VALUES ('OR', 'WA') READ ONLY,
     SUBPARTITION order_p3_southwest VALUES ('AZ', 'UT', 'NM')
     )
 );

You can check the read-only status with the DEF_READ_ONLY column of the *_PART_TABLES view, the READ_ONLY column of the *_TAB_PARTITIONS view, and the READ_ONLY column of the *_TAB_SUBPARTITIONS view. Note that only physical segments, partitions for single-level partitioning and subpartitions for composite partitioning, have a status. All other levels are logical and only have a default status.

SQL> SELECT PARTITION_NAME, READ_ONLY FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='ORDERS_READ_WRITE_ONLY';
PARTITION_NAME                  READ
------------------------------- ----
ORDER_P1                        YES
ORDER_P2                        NONE
ORDER_P3                        NONE

SQL> SELECT PARTITION_NAME, SUBPARTITION_NAME, READ_ONLY FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='ORDERS_READ_WRITE_ONLY';
PARTITION_NAME                 SUBPARTITION_NAME             REA
------------------------------ ----------------------------- ---
ORDER_P1                       ORDER_P1_NORTHWEST            YES
ORDER_P1                       ORDER_P1_SOUTHWEST            YES
ORDER_P2                       ORDER_P2_NORTHWEST            NO
ORDER_P2                       ORDER_P2_SOUTHWEST            YES
ORDER_P3                       ORDER_P3_NORTHWEST            YES
ORDER_P3                       ORDER_P3_SOUTHWEST            NO

See Also:

Oracle Database Reference for information about *_PART_TABLES, *_TAB_PARTITIONS, and *_TAB_SUBPARTITIONS views

4.1.9 Creating a Partitioned External Table

You can create partitions for an external table.

The organization external clause identifies the table as external table, followed by the specification and access parameters of the external table. While parameters, such as the default directory; can be overridden on a partition or subpartition level, the external table type and its access parameters are table-level attributes and applicable to all partitions or subpartitions.

The table created in Example 4-11 has three partitions for external data accessed from different locations. Partition p1 stores customer data for California, located in the default directory of the table. Partition p2 points to a file storing data for Washington. Partition p3 does not have a file descriptor and is empty.

Example 4-11 Creating a Partitioned External Table

CREATE TABLE sales (loc_id number, prod_id number, cust_id number, amount_sold number, quantity_sold number)
 ORGANIZATION EXTERNAL
 (TYPE oracle_loader
  DEFAULT DIRECTORY load_d1
  ACCESS PARAMETERS
  ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    NOBADFILE
    LOGFILE log_dir:'sales.log'
    FIELDS TERMINATED BY ","
   )
 )
  REJECT LIMIT UNLIMITED
 PARTITION BY RANGE (loc_id)
 (PARTITION p1 VALUES LESS THAN (1000) LOCATION ('california.txt'),
  PARTITION p2 VALUES LESS THAN (2000) DEFAULT DIRECTORY load_d2 LOCATION ('washington.txt'),
  PARTITION p3 VALUES LESS THAN (3000))
;

Note:

Hive column names should be limited to [a-zA-Z0-9_]+ on external partitioned tables. If you use any Oracle column names that do not follow this rule, then use the two access parameters com.oracle.bigdata.fields and com.oracle.bigdata.colmap together to remap the “extended” Oracle column name to a simple Hive identifer.

See Also:

Oracle Database Administrator’s Guide for information about partitioning external tables

4.1.10 Specifying Partitioning on Key Columns

For range-partitioned and hash partitioned tables, you can specify up to 16 partitioning key columns.

Use multicolumn partitioning when the partitioning key is composed of several columns and subsequent columns define a higher granularity than the preceding ones. The most common scenario is a decomposed DATE or TIMESTAMP key, consisting of separated columns, for year, month, and day.

In evaluating multicolumn partitioning keys, the database uses the second value only if the first value cannot uniquely identify a single target partition, and uses the third value only if the first and second do not determine the correct partition, and so forth. A value cannot determine the correct partition only when a partition bound exactly matches that value and the same bound is defined for the next partition. The n^th column is investigated only when all previous (n-1) values of the multicolumn key exactly match the (n-1) bounds of a partition. A second column, for example, is evaluated only if the first column exactly matches the partition boundary value. If all column values exactly match all of the bound values for a partition, then the database determines that the row does not fit in this partition and considers the next partition for a match.

For nondeterministic boundary definitions (successive partitions with identical values for at least one column), the partition boundary value becomes an inclusive value, representing a "less than or equal to" boundary. This is in contrast to deterministic boundaries, where the values are always regarded as "less than" boundaries.

The following topics are discussed:

4.1.10.1 Creating a Multicolumn Range-Partitioned Table By Date

The example in this topic shows how to create a multicolumn range-partitioned table by date.

Example 4-12 illustrates the column evaluation for a multicolumn range-partitioned table, storing the actual DATE information in three separate columns: year, month, and day. The partitioning granularity is a calendar quarter. The partitioned table being evaluated is created as follows:

The year value for 12-DEC-2000 satisfied the first partition, before2001, so no further evaluation is needed:

SELECT * FROM sales_demo PARTITION(before2001);

      YEAR      MONTH        DAY AMOUNT_SOLD
---------- ---------- ---------- -----------
      2000         12         12        1000

The information for 17-MAR-2001 is stored in partition q1_2001. The first partitioning key column, year, does not by itself determine the correct partition, so the second partitioning key column, month, must be evaluated.

SELECT * FROM sales_demo PARTITION(q1_2001);

      YEAR      MONTH        DAY AMOUNT_SOLD
---------- ---------- ---------- -----------
      2001          3         17        2000

Following the same determination rule as for the previous record, the second column, month, determines partition q4_2001 as correct partition for 1-NOV-2001:

SELECT * FROM sales_demo PARTITION(q4_2001);

      YEAR      MONTH        DAY AMOUNT_SOLD
---------- ---------- ---------- -----------
      2001         11          1        5000

The partition for 01-JAN-2002 is determined by evaluating only the year column, which indicates the future partition:

SELECT * FROM sales_demo PARTITION(future);

      YEAR      MONTH        DAY AMOUNT_SOLD
---------- ---------- ---------- -----------
      2002          1          1        4000

If the database encounters MAXVALUE in a partitioning key column, then all other values of subsequent columns become irrelevant. That is, a definition of partition future in the preceding example, having a bound of (MAXVALUE,0) is equivalent to a bound of (MAXVALUE,100) or a bound of (MAXVALUE,MAXVALUE).

Example 4-12 Creating a multicolumn range-partitioned table

CREATE TABLE sales_demo (
   year          NUMBER, 
   month         NUMBER,
   day           NUMBER,
   amount_sold   NUMBER) 
PARTITION BY RANGE (year,month) 
  (PARTITION before2001 VALUES LESS THAN (2001,1),
   PARTITION q1_2001    VALUES LESS THAN (2001,4),
   PARTITION q2_2001    VALUES LESS THAN (2001,7),
   PARTITION q3_2001    VALUES LESS THAN (2001,10),
   PARTITION q4_2001    VALUES LESS THAN (2002,1),
   PARTITION future     VALUES LESS THAN (MAXVALUE,0));

REM  12-DEC-2000
INSERT INTO sales_demo VALUES(2000,12,12, 1000);
REM  17-MAR-2001
INSERT INTO sales_demo VALUES(2001,3,17, 2000);
REM  1-NOV-2001
INSERT INTO sales_demo VALUES(2001,11,1, 5000);
REM  1-JAN-2002
INSERT INTO sales_demo VALUES(2002,1,1, 4000);

4.1.10.2 Creating a Multicolumn Range-Partitioned Table to Enforce Equal-Sized Partitions

The example in this topic shows how to create a multicolumn range-partitioned table to enforce equal-sized partitions.

The following example illustrates the use of a multicolumn partitioned approach for table supplier_parts, storing the information about which suppliers deliver which parts. To distribute the data in equal-sized partitions, it is not sufficient to partition the table based on the supplier_id, because some suppliers might provide hundreds of thousands of parts, while others provide only a few specialty parts. Instead, you partition the table on (supplier_id, partnum) to manually enforce equal-sized partitions.

Every row with supplier_id < 10 is stored in partition p1, regardless of the partnum value. The column partnum is evaluated only if supplier_id =10, and the corresponding rows are inserted into partition p1, p2, or even into p3 when partnum >=200. To achieve equal-sized partitions for ranges of supplier_parts, you could choose a composite range-hash partitioned table, range partitioned by supplier_id, hash subpartitioned by partnum.

Defining the partition boundaries for multicolumn partitioned tables must obey some rules. For example, consider a table that is range partitioned on three columns a, b, and c. The individual partitions have range values represented as follows:

P0(a0, b0, c0)
P1(a1, b1, c1)
P2(a2, b2, c2)
...
Pn(an, bn, cn)

The range values you provide for each partition must follow these rules:

a0 must be less than or equal to a1, and a1 must be less than or equal to a2, and so on.
If a0=a1, then b0 must be less than or equal to b1. If a0 < a1, then b0 and b1 can have any values. If a0=a1 and b0=b1, then c0 must be less than or equal to c1. If b0<b1, then c0 and c1 can have any values, and so on.
If a1=a2, then b1 must be less than or equal to b2. If a1<a2, then b1 and b2 can have any values. If a1=a2 and b1=b2, then c1 must be less than or equal to c2. If b1<b2, then c1 and c2 can have any values, and so on.

CREATE TABLE supplier_parts (
   supplier_id      NUMBER, 
   partnum          NUMBER,
   price            NUMBER)
PARTITION BY RANGE (supplier_id, partnum)
  (PARTITION p1 VALUES LESS THAN  (10,100),
   PARTITION p2 VALUES LESS THAN (10,200),
   PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE));

The following three records are inserted into the table:

INSERT INTO supplier_parts VALUES (5,5, 1000);
INSERT INTO supplier_parts VALUES (5,150, 1000);
INSERT INTO supplier_parts VALUES (10,100, 1000);

The first two records are inserted into partition p1, uniquely identified by supplier_id. However, the third record is inserted into partition p2; it matches all range boundary values of partition p1 exactly and the database therefore considers the following partition for a match. The value of partnum satisfies the criteria < 200, so it is inserted into partition p2.

SELECT * FROM supplier_parts PARTITION (p1);

SUPPLIER_ID    PARTNUM      PRICE
----------- ---------- ----------
          5          5       1000
          5        150       1000

SELECT * FROM supplier_parts PARTITION (p2);

SUPPLIER_ID    PARTNUM      PRICE
----------- ---------- ----------
          10       100       1000

4.1.11 Using Virtual Column-Based Partitioning

With partitioning, a virtual column can be used as any regular column.

All partition methods are supported when using virtual columns, including interval partitioning and all different combinations of composite partitioning. A virtual column used as the partitioning column cannot use calls to a PL/SQL function.

The following example shows the sales table partitioned by range-range using a virtual column for the subpartitioning key. The virtual column calculates the total value of a sale by multiplying amount_sold and quantity_sold. As the example shows, row movement is also supported with virtual columns. If row movement is enabled, then a row migrates from one partition to another partition if the virtual column evaluates to a value that belongs to another partition.

CREATE TABLE sales
  ( prod_id       NUMBER(6) NOT NULL
  , cust_id       NUMBER NOT NULL
  , time_id       DATE NOT NULL
  , channel_id    CHAR(1) NOT NULL
  , promo_id      NUMBER(6) NOT NULL
  , quantity_sold NUMBER(3) NOT NULL
  , amount_sold   NUMBER(10,2) NOT NULL
  , total_amount AS (quantity_sold * amount_sold)
  )
 PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 SUBPARTITION BY RANGE(total_amount)
 SUBPARTITION TEMPLATE
   ( SUBPARTITION p_small VALUES LESS THAN (1000)
   , SUBPARTITION p_medium VALUES LESS THAN (5000)
   , SUBPARTITION p_large VALUES LESS THAN (10000)
   , SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
   )
 (PARTITION sales_before_2007 VALUES LESS THAN
        (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
)
ENABLE ROW MOVEMENT
PARALLEL NOLOGGING;

See Also:

Oracle Database SQL Language Reference for the syntax on how to create a virtual column

4.1.12 Using Table Compression with Partitioned Tables

For heap-organized partitioned tables, you can compress some or all partitions using table compression.

The compression attribute can be declared for a tablespace, a table, or a partition of a table. Whenever the compress attribute is not specified, it is inherited like any other storage attribute.

Example 4-13 creates a range-partitioned table with one compressed partition costs_old. The compression attribute for the table and all other partitions is inherited from the tablespace level.

Example 4-13 Creating a range-partitioned table with a compressed partition

CREATE TABLE costs_demo (
   prod_id     NUMBER(6),    time_id     DATE, 
   unit_cost   NUMBER(10,2), unit_price  NUMBER(10,2))
PARTITION BY RANGE (time_id)
   (PARTITION costs_old 
       VALUES LESS THAN (TO_DATE('01-JAN-2003', 'DD-MON-YYYY')) COMPRESS,
    PARTITION costs_q1_2003 
       VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
    PARTITION costs_q2_2003
       VALUES LESS THAN (TO_DATE('01-JUN-2003', 'DD-MON-YYYY')),
    PARTITION costs_recent VALUES LESS THAN (MAXVALUE));

4.1.13 Using Key Compression with Partitioned Indexes

You can compress some or all partitions of a B-tree index using key compression.

Key compression is applicable only to B-tree indexes. Bitmap indexes are stored in a compressed manner by default. An index using key compression eliminates repeated occurrences of key column prefix values, thus saving space and I/O.

The following example creates a local partitioned index with all partitions except the most recent one compressed:

CREATE INDEX i_cost1 ON costs_demo (prod_id) COMPRESS LOCAL
   (PARTITION costs_old, PARTITION costs_q1_2003, 
    PARTITION costs_q2_2003, PARTITION costs_recent NOCOMPRESS);

You cannot specify COMPRESS (or NOCOMPRESS) explicitly for an index subpartition. All index subpartitions of a given partition inherit the key compression setting from the parent partition.

To modify the key compression attribute for all subpartitions of a given partition, you must first issue an ALTER INDEX...MODIFY PARTITION statement and then rebuild all subpartitions. The MODIFY PARTITION clause marks all index subpartitions as UNUSABLE.

4.1.14 Specifying Partitioning with Segments

Partitioning with segments is introduced in this topic.

These topics discuss the functionality when using partitioning with segments.

4.1.14.1 Deferred Segment Creation for Partitioning

You can defer the creation of segments when creating a partitioned table until the first row is inserted into a partition.

When the first row is inserted, segments are created for the base table partition, LOB columns, all global indexes, and local index partitions. Deferred segment creation can be controlled by the following:

Setting the DEFERRED_SEGMENT_CREATION initialization parameter to TRUE or FALSE in the initialization parameter file.
Setting the initialization parameter DEFERRED_SEGMENT_CREATION to TRUE or FALSE with the ALTER SESSION or ALTER SYSTEM SQL statements.
Specifying the keywords SEGMENT CREATION IMMEDIATE or SEGMENT CREATION DEFERRED with the partition clause when issuing the CREATE TABLE SQL statement.

You can force the creation of segments for an existing created partition with the ALTER TABLE MODIFY PARTITION ALLOCATE EXTENT SQL statement. This statement allocates one extent more than the initial number of extents specified during the CREATE TABLE.

Serializable transactions are not supported with deferred segment creation. Inserting data into an empty table with no segment created, or into a partition of an interval partitioned table that does not have a segment yet, can cause an error.

See Also:

Oracle Database Reference for more information about the DEFERRED_SEGMENT_CREATION initialization parameter
Oracle Database SQL Language Reference for more information about the ALTER SESSION and ALTER SYSTEM SQL statements
Oracle Database SQL Language Reference for more information about the keywords SEGMENT CREATION IMMEDIATE and SEGMENT CREATION DEFERRED of the CREATE TABLE SQL statement

4.1.14.2 Truncating Segments That Are Empty

You can drop empty segments in tables and table fragments with the DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS procedure.

In addition, if a partition or subpartition has a segment, then the truncate feature drops the segment if the DROP ALL STORAGE clause is specified with the ALTER TABLE TRUNCATE PARTITION SQL statement.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SPACE_ADMIN package
Oracle Database SQL Language Reference for more information about the DROP ALL STORAGE clause of ALTER TABLE

4.1.14.3 Maintenance Procedures for Segment Creation on Demand

You can use the MATERIALIZE_DEFERRED_SEGMENTS procedure in the DBMS_SPACE_ADMIN package to create segments for tables and dependent objects for tables with the deferred segment property.

You can also force the creation of segments for an existing created table and table fragment with the DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS procedure. The MATERIALIZE_DEFERRED_SEGMENTS procedure differs from the ALTER TABLE MODIFY PARTITION ALLOCATE EXTENT SQL statement because it does not allocate one additional extent for the table or table fragment.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SPACE_ADMIN package

4.1.15 Specifying Partitioning When Creating Index-Organized Tables

For index-organized tables, you can use the range, list, or hash partitioning method.

The semantics for creating partitioned index-organized tables are similar to that for regular tables with these differences:

When you create the table, you specify the ORGANIZATION INDEX clause, and INCLUDING and OVERFLOW clauses as necessary.
The PARTITION clause can have OVERFLOW subclauses that allow you to specify attributes of the overflow segments at the partition level.

Specifying an OVERFLOW clause results in the overflow data segments themselves being equipartitioned with the primary key index segments. Thus, for partitioned index-organized tables with overflow, each partition has an index segment and an overflow data segment.

For index-organized tables, the set of partitioning columns must be a subset of the primary key columns. Because rows of an index-organized table are stored in the primary key index for the table, the partitioning criterion affects the availability. By choosing the partitioning key to be a subset of the primary key, an insert operation must only verify uniqueness of the primary key in a single partition, thereby maintaining partition independence.

Support for secondary indexes on index-organized tables is similar to the support for regular tables. Because of the logical nature of the secondary indexes, global indexes on index-organized tables remain usable for certain operations where they would be marked UNUSABLE for regular tables.

The following topics are discussed:

See Also:

Maintenance Operations for Partitioned Tables and Indexes for information about maintenance operations on index-organized tables
Oracle Database Administrator’s Guide for more information about managing index-organized tables
Oracle Database Concepts for more information about index-organized tables

4.1.15.1 Creating Range-Partitioned Index-Organized Tables

You can partition index-organized tables, and their secondary indexes, by the range method.

In Example 4-14, 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.

Example 4-14 Creating a range-partitioned index-organized table

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);

4.1.15.2 Creating Hash Partitioned Index-Organized Tables

Another option for partitioning index-organized tables is to use the hash method.

In Example 4-15, the sales index-organized table is partitioned by the hash method.

Note:

A well-designed hash function is intended to distribute rows in a well-balanced fashion among the partitions. Therefore, updating the primary key column(s) of a row is very likely to move that row to a different partition. Oracle recommends that you explicitly specify the ENABLE ROW MOVEMENT clause when creating a hash partitioned index-organized table with a changeable partitioning key. The default is that ENABLE ROW MOVEMENT is disabled.

Example 4-15 Creating a hash partitioned index-organized table

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);

4.1.15.3 Creating List-Partitioned Index-Organized Tables

The other option for partitioning index-organized tables is to use the list method.

In Example 4-16, the sales index-organized table is partitioned by the list method.

Example 4-16 Creating a list-partitioned index-organized table

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 ts1
     PARTITION BY LIST (week_no)
            (PARTITION VALUES (1, 2, 3, 4) 
                   TABLESPACE ts2,
             PARTITION VALUES (5, 6, 7, 8) 
                   TABLESPACE ts3 OVERFLOW TABLESPACE ts4,
             PARTITION VALUES (DEFAULT) 
                   TABLESPACE ts5);

4.1.16 Partitioning Restrictions for Multiple Block Sizes

Use caution when creating partitioned objects in a database with tablespaces of different block sizes.

The storage of partitioned objects in such tablespaces is subject to some restrictions. Specifically, all partitions of the following entities must reside in tablespaces of the same block size:

Conventional tables
Indexes
Primary key index segments of index-organized tables
Overflow segments of index-organized tables
LOB columns stored out of line

Therefore:

For each conventional table, all partitions of that table must be stored in tablespaces with the same block size.
For each index-organized table, all primary key index partitions must reside in tablespaces of the same block size, and all overflow partitions of that table must reside in tablespaces of the same block size. However, index partitions and overflow partitions can reside in tablespaces of different block size.
For each index (global or local), each partition of that index must reside in tablespaces of the same block size. However, partitions of different indexes defined on the same object can reside in tablespaces of different block sizes.
For each LOB column, each partition of that column must be stored in tablespaces of equal block sizes. However, different LOB columns can be stored in tablespaces of different block sizes.

When you create or alter a partitioned table or index, all tablespaces you explicitly specify for the partitions and subpartitions of each entity must be of the same block size. If you do not explicitly specify tablespace storage for an entity, then the tablespaces the database uses by default must be of the same block size. Therefore, you must be aware of the default tablespaces at each level of the partitioned object.

4.1.17 Partitioning of Collections in XMLType and Objects

Partitioning when using XMLType or object tables and columns follows the basic rules for partitioning.

For the purposes of this discussion, the term Collection Tables is used for the following two categories: (1) ordered collection tables inside XMLType tables or columns, and (2) nested tables inside object tables or columns.

When you partition Collection Tables, Oracle Database uses the partitioning scheme of the base table. Also, Collection Tables are automatically partitioned when the base table is partitioned. DML against a partitioned nested table behaves in a similar manner to that of a reference partitioned table.

Oracle Database provides a LOCAL keyword to equipartition a Collection Table with a partitioned base table. This is the default behavior in this release. The default in earlier releases was not to equipartition the Collection Table with the partitioned base table. Now you must specify the GLOBAL keyword to store an unpartitioned Collection Table with a partitioned base table.

Out-of-line (OOL) table partitioning is supported. However, you cannot create two tables of the same XML schema that has out-of-line tables. This restriction means that exchange partitioning cannot be performed for schemas with OOL tables because it is not possible to have two tables of the same schema.

The statement in the following example creates a nested table partition.

CREATE TABLE print_media_part (
   product_id NUMBER(6),
   ad_id NUMBER(6),
   ad_composite BLOB,
   ad_sourcetext CLOB,
   ad_finaltext CLOB,
   ad_fltextn NCLOB,
   ad_textdocs_ntab TEXTDOC_TAB,
   ad_photo BLOB,
   ad_graphic BFILE,
   ad_header ADHEADER_TYP)
NESTED TABLE ad_textdocs_ntab STORE AS textdoc_nt
PARTITION BY RANGE (product_id)
  (PARTITION p1 VALUES LESS THAN (100),
   PARTITION p2 VALUES LESS THAN (200));

See Also:

Performing PMOs on Partitions that Contain Collection Tables and Partitioning of XMLIndex for Binary XML Tables for additional related examples
Collection Tables for an example of issuing a query against a partitioned nested table and using the EXPLAIN PLAN to improve performance
Changing a Nonpartitioned Table into a Partitioned Table for information about using online redefinition to convert your existing nonpartitioned collection tables to partitioned tables
Oracle Database SQL Language Reference for details about CREATE TABLE syntax

4.1.17.1 Performing PMOs on Partitions that Contain Collection Tables

Whether a partition contains Collection Tables or not does not significantly affect your ability to perform partition maintenance operations (PMOs).

Usually, maintenance operations on Collection Tables are carried out on the base table. The following example illustrates a typical ADD PARTITION operation based on the preceding nested table partition:

ALTER TABLE print_media_part 
   ADD PARTITION p4 VALUES LESS THAN (400)
   LOB(ad_photo, ad_composite) STORE AS (TABLESPACE omf_ts1)
   LOB(ad_sourcetext, ad_finaltext) STORE AS (TABLESPACE omf_ts1)
   NESTED TABLE ad_textdocs_ntab STORE AS nt_p3;

The storage table for nested table storage column ad_textdocs_ntab is named nt_p3 and inherits all other attributes from the table-level defaults and then from the tablespace defaults.

You must directly invoke the following partition maintenance operations on the storage table corresponding to the collection column:

modify partition
move partition
rename partition
modify the default attributes of a partition

See Also:

Oracle Database SQL Language Reference for ADD PARTITION syntax
Maintenance Operations Supported on Partitions for a list of partition maintenance operations that can be performed on partitioned tables and composite partitioned tables

4.1.17.2 Partitioning of XMLIndex for Binary XML Tables

For binary XML tables, XMLIndex is equipartitioned with the base table for range, hash, list, interval, and reference partitions.

In the following example, an XMLIndex is created on a range-partitioned table.

CREATE TABLE purchase_order 
   (id NUMBER, doc XMLTYPE)
    PARTITION BY RANGE (id)
    (PARTITION p1 VALUES LESS THAN (10),
     PARTITION p2 VALUES LESS THAN (MAXVALUE));
 
CREATE INDEX purchase_order_idx ON purchase_order(doc) 
       INDEXTYPE IS XDB.XMLINDEX LOCAL;

See Also:

Oracle Database Data Cartridge Developer's Guide for information about Oracle XML DB and partitioning of XMLIndex for binary XML tables
Oracle XML DB Developer’s Guide for information about XMLIndex
Oracle XML DB Developer’s Guide for information about partitioning XMLType tables and columns

4.2 Specifying Composite Partitioning When Creating Tables

When creating a composite partitioned table, you use the PARTITION and SUBPARTITION clauses of the CREATE TABLE SQL statement.

To create a composite partitioned table, you start by using the PARTITION BY {HASH | RANGE [INTERVAL]| LIST} clause of a CREATE TABLE statement. Next, you specify a SUBPARTITION BY clause that follows similar syntax and rules as the PARTITION BY clause.

The following topics are discussed:

4.2.1 Creating Composite Hash-* Partitioned Tables

Composite hash-* partitioning enables hash partitioning along two dimensions.

The composite hash-hash partitioning strategy has the most business value of the composite hash-* partitioned tables. This technique is beneficial to enable partition-wise joins along two dimensions.

In the following example, the number of subpartitions is specified when creating a composite hash-hash partitioned table; however, names are not specified. System generated names are assigned to partitions and subpartitions, which are stored in the default tablespace of the table.

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Composite Hash-Hash Partition Table.

Example 4-17 Creating a composite hash-hash partitioned table

CREATE TABLE departments_courses_hash (
             department_id NUMBER(4) NOT NULL, 
             department_name VARCHAR2(30),
             course_id NUMBER(4) NOT NULL)
     PARTITION BY HASH(department_id)
     SUBPARTITION BY HASH (course_id) SUBPARTITIONS 32 PARTITIONS 16;

See Also:

Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table

4.2.2 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 are created when the database creates the interval. You can specify the definition of future subpartitions only with a subpartition template.

The following topics show examples for the different interval-* composite partitioning methods.

See Also:

Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table

4.2.2.1 Creating Composite Interval-Hash Partitioned Tables

You can create an interval-hash partitioned table with multiple hash partitions by specifying multiple hash partitions in the PARTITION clause or by using a subpartition template.

If you do not use either of these methods, then future interval partitions get only a single hash subpartition.

The following example shows the sales table, interval partitioned using monthly intervals on time_id, with hash subpartitions by cust_id. This example specifies multiple hash partitions, without any specific tablespace assignment to the individual hash partitions.

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Composite Interval-Hash Partitioned Table.

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;

This next example shows the same sales table, interval partitioned using monthly intervals on time_id, again with hash subpartitions by cust_id. This time, however, individual hash partitions are stored in separate tablespaces. The subpartition template is used to define the tablespace assignment for future hash subpartitions.

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;

4.2.2.2 Creating Composite Interval-List Partitioned Tables

To define list subpartitions for future interval-list partitions, you must use the subpartition template.

If you do not use the subpartitioning template, then the only subpartition that are created for every interval partition is a DEFAULT subpartition.

Example 4-18 shows the sales_interval_list table, interval partitioned using monthly intervals on sales_date, with list subpartitions by channel_id.

Example 4-18 Creating a composite interval-list partitioned table

CREATE TABLE sales_interval_list
  ( product_id       NUMBER(6)
  , customer_id      NUMBER
  , channel_id       CHAR(1)
  , promo_id         NUMBER(6)
  , sales_date       DATE
  , quantity_sold    INTEGER
  , amount_sold      NUMBER(10,2)
  )
 PARTITION BY RANGE (sales_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
 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_2017 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy'))
  )
PARALLEL;

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='SALES_INTERVAL_LIST';

4.2.2.3 Creating Composite Interval-Range Partitioned Tables

To define range subpartitions for future interval-range partitions, you must use the subpartition template.

If you do not use the subpartition template, then the only subpartition that is created for every interval partition is a range subpartition with the MAXVALUE upper boundary.

Example 4-19 shows the sales table, interval partitioned using daily intervals on time_id, with range subpartitions by amount_sold.

Example 4-19 Creating a composite interval-range partitioned table

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;

4.2.3 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.

However, for list-* composite partitioning you specify PARTITION BY LIST to define the partitioning strategy.

The list partitions of a list-* composite partitioned table are similar to non-composite range partitioned tables. This organization enables optional subclauses of a PARTITION clause to 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 similar to range-* composite partitioning methods.

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

See Also:

Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table
About Creating Composite Range-Hash Partitioned Tables for more information about the subpartition definition of a list-hash composite partitioning method
About Creating Composite Range-List Partitioned Tables for more information about the subpartition definition of a list-list composite partitioning method
Creating Composite Range-Range Partitioned Tables for more information about the subpartition definition of a list-range composite partitioning method

4.2.3.1 Creating Composite List-Hash Partitioned Tables

The example in this topic shows how to create a composite list-hash partitioned table.

Example 4-20 shows an accounts table that is list partitioned by region and subpartitioned using hash by customer identifier.

Example 4-20 Creating a composite list-hash partitioned table

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')
);

4.2.3.2 Creating Composite List-List Partitioned Tables

The example in this topic shows how to create a composite list-list partitioned table.

Example 4-21 shows an accounts table that is list partitioned by region and subpartitioned using list by account status.

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Composite List-List Partitioned Table.

Example 4-21 Creating a composite list-list partitioned table

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')
  )
);

4.2.3.3 Creating Composite List-Range Partitioned Tables

The example in this topic shows how to create a composite list-range partitioned table.

Example 4-22 shows an accounts table that is list partitioned by region and subpartitioned using range by account balance, and row movement is enabled. Subpartitions for different list partitions could have different ranges specified.

Example 4-22 Creating a composite list-range partitioned table

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;

4.2.4 Creating Composite Range-* Partitioned Tables

The methods for creating composite range-* partitioned tables are introduced in this topic.

The following topics show examples of the different range-* composite partitioning methods.

See Also:

Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table

4.2.4.1 About Creating Composite Range-Hash Partitioned Tables

The partitions of a range-hash partitioned table are logical structures only, because 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.

The following topics are discussed:

See Also:

Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table

4.2.4.1.1 Creating a Composite Range-Hash Partitioned Table With the Same Tablespaces

The example in this topic shows how to create a composite range-hash partitioned table using the same tablespaces.

The statement in Example 4-23 creates a range-hash partitioned table. 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, ts2, ts3,ts4).

Example 4-23 Creating a composite range-hash partitioned table using one STORE IN clause

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'))
 );

4.2.4.1.2 Creating a Composite Range-Hash Partitioned Table With Varying Tablespaces

The example in this topic shows how to create a composite range-hash partitioned table using varying tablespaces.

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. This is illustrated in the following example.

CREATE TABLE employees_range_hash 
         (department_id NUMBER(4) NOT NULL, 
          last_name VARCHAR2(25), 
          job_id VARCHAR2(10))   
     PARTITION BY RANGE(department_id) SUBPARTITION BY HASH(last_name)
        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));

4.2.4.1.3 Creating a Local Index Across Multiple Tablespaces

The example in this topic shows how to create a local index across multiple tablespaces.

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

CREATE INDEX employee_ix ON employees_range_hash(department_id)
     LOCAL STORE IN (ts7, ts8, ts9);

This local index is equipartitioned with the base table so that 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.

4.2.4.2 About Creating Composite Range-List Partitioned Tables

The range partitions of a range-list composite partitioned table are described as the same for non-composite range partitioned tables.

This organization enables optional subclauses of a PARTITION clause to 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 topics are discussed:

See Also:

Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table

4.2.4.2.1 Creating a Composite Range-List Partitioned Table

The example in this topic shows how to create a composite range-list partitioned table.

Example 4-24 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.

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, the following list describes how some sample rows are inserted.

(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 displays an error
(50, 5671621, '17-Dec-1999', 76123.35, 'CA') does not map to any subpartition in the table and displays an error

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a Composite Range-List Partitioned Table.

Example 4-24 Creating a composite range-list partitioned table

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')
         )
      );

4.2.4.2.2 Creating a Composite Range-List Partitioned Table Specifying Tablespaces

The example in this topic shows how to create a composite range-list partitioned table while specifying tablespaces.

The partitions of a range-list partitioned table are logical structures only, because 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. 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.

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
      );

4.2.4.3 Creating Composite Range-Range Partitioned Tables

The range partitions of a range-range composite partitioned table are similar to non-composite range partitioned tables.

This organization enables optional subclauses of a PARTITION clause to 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 range subpartition descriptions, in the SUBPARTITION clauses, are similar to 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 is delivered in the calendar month after the order was placed. The following types of orders are identified:

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.

E (EARLY): orders that are delivered before 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)
  )
);

See Also:

Specifying Subpartition Templates to Describe Composite Partitioned Tables to learn how using a subpartition template can simplify the specification of a composite partitioned table

4.2.5 Specifying Subpartition Templates to Describe Composite Partitioned Tables

You can create subpartitions in a composite partitioned table using a subpartition template.

A subpartition template simplifies the specification of subpartitions by not requiring that a subpartition descriptor be specified for every partition in the table. Instead, you describe subpartitions only one time in a template, then apply that subpartition template to every partition in the table. For interval-* composite partitioned tables, the subpartition template is the only way to define subpartitions for interval partitions.

The subpartition template is used whenever a subpartition descriptor is not specified for a partition. If a subpartition descriptor is specified, then it is used instead of the subpartition template for that partition. If no subpartition template is specified, and no subpartition descriptor is supplied for a partition, then a single default subpartition is created.

The following topics are discussed:

4.2.5.1 Specifying a Subpartition Template for a *-Hash Partitioned Table

For range-hash, interval-hash, and list-hash partitioned tables, the subpartition template can describe the subpartitions in detail, or it can specify just the number of hash subpartitions.

Example 4-25 creates a range-hash partitioned table using a subpartition template and displays the subpartition names and tablespaces.

The example produces a table with the following 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

Example 4-25 Creating a range-hash partitioned table with a subpartition template

CREATE TABLE employees_sub_template (department_id NUMBER(4) NOT NULL, 
             last_name VARCHAR2(25), job_id VARCHAR2(10))   
     PARTITION BY RANGE(department_id) SUBPARTITION BY HASH(last_name)
     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)
    );

SQL> SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME
  2  FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMPLOYEEES_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.

4.2.5.2 Specifying a Subpartition Template for a *-List Partitioned Table

For -list partitioned tables, the subpartition template can describe the subpartitions in detail.

Example 4-26, 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.

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.

Example 4-26 Creating a range-list partitioned table with a subpartition template

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'))
  );

4.3 Maintenance Operations Supported on Partitions

There are various maintenance operations that can be performed on partitions, subpartitions, and index partitions.

The maintenance operations that are supported on partitions, subpartitions, and index partitions are described in the following tables and topics.

Table 4-1 lists partition maintenance operations that can be performed on partitioned tables and composite partitioned tables
Table 4-2 lists subpartition maintenance operations that can be performed on composite partitioned tables
Table 4-3 lists maintenance operations that can be performed on index partitions, and on which type of index (global or local) they can be performed
Updating Indexes Automatically
Asynchronous Global Index Maintenance for Dropping and Truncating Partitions
Modifying a Subpartition Template
Filtering Maintenance Operations

For each type of partitioning and subpartitioning in Table 4-1 and Table 4-2, the specific clause of the ALTER TABLE statement that is used to perform that maintenance operation is listed.

Note:

Partition maintenance operations on multiple partitions are not supported on tables with domain indexes.

Table 4-1 ALTER TABLE Maintenance Operations for Table Partitions

Maintenance Operation	RangeComposite Range-*	IntervalComposite Interval-*	Hash	ListComposite List-*	Reference
Adding Partitions, refer to About Adding Partitions and Subpartitions	`ADD PARTITION`, single and multiple partitions	N/A	`ADD PARTITION`	`ADD PARTITION`, single and multiple partitions	N/A. (These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.)
Coalescing Partitions, refer to About Coalescing Partitions and Subpartitions	N/A	N/A	`COALESCE PARTITION`	N/A	N/A (These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.)
Dropping Partitions, refer to About Dropping Partitions and Subpartitions	`DROP PARTITION`, single and multiple partitions	`DROP PARTITION`, single and multiple partitions	N/A	`DROP PARTITION`, single and multiple partitions	N/A (These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.)
Exchanging Partitions, refer to About Exchanging Partitions and Subpartitions	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`	`EXCHANGE PARTITION`
Merging Partitions, refer to About Merging Partitions and Subpartitions	`MERGE PARTITIONS`, single and multiple partitions	`MERGE PARTITIONS`, single and multiple partitions	N/A	`MERGE PARTITIONS`, single and multiple partitions	N/A (These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.)
About Modifying Default Attributes	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`
About Modifying Real Attributes of Partitions	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION`
About Modifying List Partitions: Adding Values	N/A	N/A	N/A	`MODIFY PARTITION ADD VALUES`	N/A
About Modifying List Partitions: Dropping Values	N/A	N/A	N/A	`MODIFY PARTITION DROP VALUES`	N/A
Moving Partitions, refer to About Moving Partitions and Subpartitions	`MOVE SUBPARTITION`	`MOVE SUBPARTITION`	`MOVE PARTITION`	`MOVE SUBPARTITION`	`MOVE PARTITION`
Renaming Partitions, refer to About Renaming Partitions and Subpartitions	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION`
Splitting Partitions, refer to About Splitting Partitions and Subpartitions	`SPLIT PARTITION`, single and multiple partitions	`SPLIT PARTITION`, single and multiple partitions	N/A	`SPLIT PARTITION`, single and multiple partitions	N/A (These operations cannot be performed on reference-partitioned tables. If performed on a parent table, then these operations cascade to all descendant tables.)
Truncating Partitions, refer to About Truncating Partitions and Subpartitions	`TRUNCATE PARTITION`, single and multiple partitions	`TRUNCATE PARTITION`, single and multiple partitions	`TRUNCATE PARTITION`, single and multiple partitions	`TRUNCATE PARTITION`, single and multiple partitions	`TRUNCATE PARTITION`, single and multiple partitions

Table 4-2 ALTER TABLE Maintenance Operations for Table Subpartitions

Maintenance Operation	Composite *-Range	Composite *-Hash	Composite *-List
Adding Subpartitions, refer to About Adding Partitions and Subpartitions	`MODIFY PARTITION ADD SUBPARTITION`, single and multiple subpartitions	`MODIFY PARTITION ADD SUBPARTITION`	`MODIFY PARTITION ADD SUBPARTITION`, single and multiple subpartitions
Coalescing Subpartitions, refer to About Coalescing Partitions and Subpartitions	N/A	`MODIFY PARTITION COALESCE SUBPARTITION`	N/A
Dropping Subpartitions, refer to About Dropping Partitions and Subpartitions	`DROP SUBPARTITION`, single and multiple subpartitions	N/A	`DROP SUBPARTITION`, single and multiple subpartitions
Exchanging Subpartitions, refer to About Exchanging Partitions and Subpartitions	`EXCHANGE SUBPARTITION`	N/A	`EXCHANGE SUBPARTITION`
Merging Subpartitions, refer to About Merging Partitions and Subpartitions	`MERGE SUBPARTITIONS`, single and multiple subpartitions	N/A	`MERGE SUBPARTITIONS`, single and multiple subpartitions
About Modifying Default Attributes	`MODIFY DEFAULT ATTRIBUTES FOR PARTITION`	`MODIFY DEFAULT ATTRIBUTES FOR PARTITION`	`MODIFY DEFAULT ATTRIBUTES FOR PARTITION`
Modifying Real Attributes of Subpartitions, refer to About Modifying Real Attributes of Partitions	`MODIFY SUBPARTITION`	`MODIFY SUBPARTITION`	`MODIFY SUBPARTITION`
Modifying List Subpartitions, refer to About Modifying List Partitions: Adding Values	N/A	N/A	`MODIFY SUBPARTITION ADD VALUES`
Modifying List Subpartitions, refer to About Modifying List Partitions: Dropping Values	N/A	N/A	`MODIFY SUBPARTITION DROP VALUES`
Modifying a Subpartition Template	`SET SUBPARTITION TEMPLATE`	`SET SUBPARTITION TEMPLATE`	`SET SUBPARTITION TEMPLATE`
Moving Subpartitions, refer to About Moving Partitions and Subpartitions	`MOVE SUBPARTITION`	`MOVE SUBPARTITION`	`MOVE SUBPARTITION`
Renaming Subpartitions, refer to About Renaming Partitions and Subpartitions	`RENAME SUBPARTITION`	`RENAME SUBPARTITION`	`RENAME SUBPARTITION`
Splitting Subpartitions, refer to About Splitting Partitions and Subpartitions	`SPLIT SUBPARTITION`, single and multiple subpartitions	N/A	`SPLIT SUBPARTITION`, single and multiple subpartitions
Truncating Subpartitions, refer to About Truncating Partitions and Subpartitions	`TRUNCATE SUBPARTITION`, single and multiple subpartitions	`TRUNCATE SUBPARTITION`, single and multiple subpartitions	`TRUNCATE SUBPARTITION`, single and multiple subpartitions

Note:

The first time you use table compression to introduce a compressed partition into a partitioned table that has bitmap indexes and that currently contains only uncompressed partitions, you must do the following:

Either drop all existing bitmap indexes and bitmap index partitions, or mark them UNUSABLE.
Set the table compression attribute.
Rebuild the indexes.

These actions are independent of whether any partitions contain data and of the operation that introduces the compressed partition.

This does not apply to partitioned tables with B-tree indexes or to partitioned index-organized tables.

Table 4-3 lists maintenance operations that can be performed on index partitions, and indicates on which type of index (global or local) they can be performed. The ALTER INDEX clause used for the maintenance operation is shown.

Global indexes do not reflect the structure of the underlying table. If partitioned, they can be partitioned by range or hash.

Because local indexes reflect the underlying structure of the table, partitioning is maintained automatically when table partitions and subpartitions are affected by maintenance activity. Therefore, partition maintenance on local indexes is less necessary and there are fewer options.

Table 4-3 ALTER INDEX Maintenance Operations for Index Partitions

Maintenance Operation	Type of Index	Type of Index Partitioning
Maintenance Operation	Type of Index	Range	Hash and List	Composite
Adding Index Partitions	Global	`-`	`ADD PARTITION` (hash only)	-
Adding Index Partitions	Local	N/A	N/A	N/A
Dropping Index Partitions	Global	`DROP PARTITION`	-	-
Dropping Index Partitions	Local	N/A	N/A	N/A
Modifying Default Attributes of Index Partitions	Global	`MODIFY DEFAULT ATTRIBUTES`	-	-
Modifying Default Attributes of Index Partitions	Local	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES`	`MODIFY DEFAULT ATTRIBUTES` `MODIFY DEFAULT ATTRIBUTES FOR PARTITION`
Modifying Real Attributes of Index Partitions	Global	`MODIFY PARTITION`	-	-
Modifying Real Attributes of Index Partitions	Local	`MODIFY PARTITION`	`MODIFY PARTITION`	`MODIFY PARTITION` `MODIFY SUBPARTITION`
About Rebuilding Index Partitions	Global	`REBUILD PARTITION`	-	-
About Rebuilding Index Partitions	Local	`REBUILD PARTITION`	`REBUILD PARTITION`	`REBUILD SUBPARTITION`
About Renaming Index Partitions	Global	`RENAME PARTITION`	-	-
About Renaming Index Partitions	Local	`RENAME PARTITION`	`RENAME PARTITION`	`RENAME PARTITION` `RENAME SUBPARTITION`
Splitting Index Partitions	Global	`SPLIT PARTITION`	-	-
Splitting Index Partitions	Local	N/A	N/A	N/A

4.3.1 Updating Indexes Automatically

Before discussing the individual maintenance operations for partitioned tables and indexes, it is important to discuss the effects of the UPDATE INDEXES clause that can be specified in the ALTER TABLE statement.

By default, many table maintenance operations on partitioned tables invalidate (mark UNUSABLE) the corresponding indexes or index partitions. You must then rebuild the entire index or, for a global index, each of its partitions. The database lets you override this default behavior if you specify UPDATE INDEXES in your ALTER TABLE statement for the maintenance operation. Specifying this clause tells the database to update the indexes at the time it executes the maintenance operation DDL statement. This provides the following benefits:

The indexes are updated with the base table operation. You are not required to update later and independently rebuild the indexes.
The global indexes are more highly available, because they are not marked UNUSABLE. These indexes remain available even while the partition DDL is executing and can access unaffected partitions in the table.
You need not look up the names of all invalid indexes to rebuild them.

Optional clauses for local indexes let you specify physical and storage characteristics for updated local indexes and their partitions.

You can specify physical attributes, tablespace storage, and logging for each partition of each local index. Alternatively, you can specify only the PARTITION keyword and let the database update the partition attributes as follows:
- For operations on a single table partition (such as MOVE PARTITION and SPLIT PARTITION), the corresponding index partition inherits the attributes of the affected table partition. The database does not generate names for new index partitions, so any new index partitions resulting from this operation inherit their names from the corresponding new table partition.
- For MERGE PARTITION operations, the resulting local index partition inherits its name from the resulting table partition and inherits its attributes from the local index.
For a composite-partitioned index, you can specify tablespace storage for each subpartition.

The following operations support the UPDATE INDEXES clause:

ADD PARTITION | SUBPARTITION
COALESCE PARTITION | SUBPARTITION
DROP PARTITION | SUBPARTITION
EXCHANGE PARTITION | SUBPARTITION
MERGE PARTITION | SUBPARTITION
MOVE PARTITION | SUBPARTITION
SPLIT PARTITION | SUBPARTITION
TRUNCATE PARTITION | SUBPARTITION

SKIP_UNUSABLE_INDEXES Initialization Parameter

SKIP_UNUSABLE_INDEXES is an initialization parameter with a default value of TRUE. This setting disables error reporting of indexes and index partitions marked UNUSABLE. If you do not want the database to choose an alternative execution plan to avoid the unusable elements, then you should set this parameter to FALSE.

Considerations when Updating Indexes Automatically

The following implications are worth noting when you specify UPDATE INDEXES:

The partition DDL statement can take longer to execute, because indexes that were previously marked UNUSABLE are updated. However, you must compare this increase with the time it takes to execute DDL without updating indexes, and then rebuild all indexes. A rule of thumb is that it is faster to update indexes if the size of the partition is less that 5% of the size of the table.
The EXCHANGE operation is no longer a fast operation. Again, you must compare the time it takes to do the DDL and then rebuild all indexes.
When you update a table with a global index:
- The index is updated in place. The updates to the index are logged, and redo and undo records are generated. In contrast, if you rebuild an entire global index, you can do so in NOLOGGING mode.
- Rebuilding the entire index manually creates a more efficient index, because it is more compact with better space utilization.
The UPDATE INDEXES clause is not supported for index-organized tables. However, the UPDATE GLOBAL INDEXES clause may be used with DROP PARTITION, TRUNCATE PARTITION, and EXCHANGE PARTITION operations to keep the global indexes on index-organized tables usable. For the remaining operations in the above list, global indexes on index-organized tables remain usable. In addition, local index partitions on index-organized tables remain usable after a MOVE PARTITION operation.

See Also:

Oracle Database SQL Language Reference for information about the update_all_indexes_clause of ALTER TABLE in for the syntax for updating indexes

4.3.2 Asynchronous Global Index Maintenance for Dropping and Truncating Partitions

The partition maintenance operations DROP PARTITION and TRUNCATE PARTITION are optimized by making the index maintenance for metadata only.

Asynchronous global index maintenance for DROP and TRUNCATE is performed by default; however, the UPDATE INDEXES clause is still required for backward compatibility.

The following list summarizes the limitations of asynchronous global index maintenance:

Only performed on heap tables
No support for tables with object types
No support for tables with domain indexes
Not performed for the user SYS

Maintenance operations on indexes can be performed with the automatic scheduler job SYS.PMO_DEFERRED_GIDX_MAINT_JOB to clean up all global indexes. This job is scheduled to run on a regular basis by default. You can run this job at any time using DBMS_SCHEDULER.RUN_JOB if you want to proactively clean up the indexes. You can also modify the job to run with a schedule based on your specific requirements. Oracle recommends that you do not drop the job.

You can also force cleanup of an index needing maintenance using one of the following options:

DBMS_PART.CLEANUP_GIDX - This PL/SQL procedure gathers the list of global indexes in the system that may require cleanup and runs the operations necessary to restore the indexes to a clean state.
ALTER INDEX REBUILD [PARTITION] – This SQL statement rebuilds the entire index or index partition as is done in releases previous to Oracle Database 12c Release 1 (12.1). The resulting index (partition) does not contain any stale entries.
ALTER INDEX [PARTITION] COALESCE CLEANUP – This SQL statement cleans up any orphaned entries in index blocks.

See Also:

Oracle Database Administrator’s Guide for information about managing jobs with Oracle Scheduler

4.3.3 Modifying a Subpartition Template

You can modify a subpartition template of a composite partitioned table by replacing it with a new subpartition template.

Any subsequent operations that use the subpartition template (such as ADD PARTITION or MERGE PARTITIONS) now use the new subpartition template. Existing subpartitions remain unchanged.

If you modify a subpartition template of an interval-* composite partitioned table, then interval partitions that have not yet been created use the new subpartition template.

Use the ALTER TABLE SET SUBPARTITION TEMPLATE statement to specify a new subpartition template. For example:

ALTER TABLE employees_sub_template
   SET SUBPARTITION TEMPLATE
         (SUBPARTITION e TABLESPACE ts1,
          SUBPARTITION f TABLESPACE ts2,
          SUBPARTITION g TABLESPACE ts3,
          SUBPARTITION h TABLESPACE ts4
         );

You can drop a subpartition template by specifying an empty list:

ALTER TABLE employees_sub_template
   SET SUBPARTITION TEMPLATE ( );

4.3.4 Filtering Maintenance Operations

Partition maintenance operations support the addition of data filtering, enabling the combination of partition and data maintenance.

A filtered partition maintenance operation only preserves the data satisfying the data filtering as part of the partition maintenance. The capability of data filtering applies to MOVE PARTITION, MERGE PARTITION, and SPLIT PARTITION .

Example 4-27 shows the use of the ALTER TABLE statement to move a partition while removing all orders that are not open (closed orders).

The filtering predicate must be on the partitioned table. All partition maintenance operations that can be performed online (MOVE and SPLIT) can also be performed as filtered partition maintenance operations. With ONLINE specified, DML operations on the partitions being maintained are allowed.

Filtered partition maintenance operations performed in online mode do not enforce the filter predicate on concurrent ongoing DML operations. The filter condition is only applied one time at the beginning of the partition maintenance operation. Consequently, any subsequent DML succeeds, but is ignored from a filtering perspective. Records that do not match the filter condition when the partition maintenance started are not preserved, regardless of any DML operation. Newly inserted records are inserted if they match the partition key criteria, regardless of whether they satisfy the filter condition of the partition maintenance operation. Filter conditions are limited to the partitioned table itself and do not allow any reference to other tables, such as a join or subquery expression.

Consider the following scenarios when the keyword ONLINE is specified in the SQL statement of Example 4-27.

An existing order record in partition q1_2016 that is updated to status='open' after the partition maintenance operation has started is not be preserved in the partition.
A new order record with status='closed' can be inserted in partition q1_2016 after the partition maintenance operation has started and while the partition maintenance operation is ongoing.

Example 4-27 Using a filtering clause when performing maintenance operations

ALTER TABLE orders_move_part
  MOVE PARTITION q1_2016 TABLESPACE open_orders COMPRESS ONLINE
  INCLUDING ROWS WHERE order_state = 'open';

See Also:

Oracle Database SQL Language Reference for the exact syntax of the partitioning clauses for creating and altering partitioned tables and indexes, any restrictions on their use, and specific privileges required for creating and altering tables

4.4 Maintenance Operations for Partitioned Tables and Indexes

There are various maintenance operations that can be performed on partitioned tables and indexes.

The operations to perform partition and subpartition maintenance for both tables and indexes are discussed in the following topics.

Note:

Where the usability of indexes or index partitions affected by the maintenance operation is discussed, consider the following:
- Only indexes and index partitions that are not empty are candidates for being marked UNUSABLE. If they are empty, the USABLE/UNUSABLE status is left unchanged.
- Only indexes or index partitions with USABLE status are updated by subsequent DML.
SPLIT, MERGE, MOVE, TRUNCATE, and DROP operations on partitions will result in ORPHANED_ENTRIES AS YES. This is expected behavior.

See Also:

Oracle Database Administrator’s Guide for information about managing tables
Oracle Database SQL Language Reference for the exact syntax of the partitioning clauses for altering partitioned tables and indexes, any restrictions on their use, and specific privileges required for creating and altering tables

4.4.1 Restrictions and Special Requirements for Multi-Partition Operations on Tables with Domain Indexes

These constraints apply to multi-partition ADD, MERGE, SPLIT, DROP, and TRUNCATE operations on a table which has a domain index created on it.

About Tables With a Local Domain Index

If a table has a local domain index, then ADD, SPLIT, and DROP operations on multiple partitions or subpartitions in the same statement are not allowed. Each partition or subpartition can be operated on in a separate statement only.

For MERGE operations on a table with a local domain index, two partitions are allowed.

If a table has a local domain index, performing TRUNCATE on a single partition or the entire table is supported. Truncating multiple partitions in a single ALTER TABLE statement is not permitted if a local domain index is present.

About Tables With a Global Domain Index

The restrictions on multi-partition operations described above apply to local domain indexes only. For tables with global domain indexes, multi-partition operations are supported.

Note:

For a table that has a global index, when you use a single statement to operate on multiple partitions it is important to include an UPDATE INDEXES clause in the statement, otherwise the index is rendered unusable.

Checking for Indexes

Before performing ADD, SPLIT, DROP or MERGE operations on multiple partitions, you can check for indexes through the ALL_INDEXES view. For example:

SELECT owner, index_name FROM SYS.ALL_INDEXES WHERE index_type IN ('DOMAIN', 'FUNCTION-BASED DOMAIN');

The query can be narrowed down by adding specific TABLE_OWNER and TABLE_NAME predicates. For example:

SELECT owner, index_name FROM SYS.ALL_INDEXES WHERE index_type IN ('DOMAIN', 'FUNCTION-BASED DOMAIN') WHERE table_owner = 'MY_USER' AND table_name = 'MY_PART_TABLE';

4.4.2 About Adding Partitions and Subpartitions

This section introduces how to manually add new partitions to a partitioned table and explains why partitions cannot be specifically added to most partitioned indexes.

This section contains the following topics:

Note:

See Restrictions and Special Requirements for Multi-Partition Operations on Tables with Domain Indexes

4.4.2.1 Adding a Partition to a Range-Partitioned Table

You can add a partition after the last existing partition of a table or the beginning of a table or in the middle of a 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.

4.4.2.2 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.

Consequently, 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:

Table Type Index Behavior

Table Type	Index Behavior
Regular (Heap)	Unless you specify `UPDATE` `INDEXES` as part of the `ALTER` `TABLE` statement: The local indexes for the new partition, and for the existing partition from which rows were redistributed, are marked `UNUSABLE` and must be rebuilt. All global indexes, or all partitions of partitioned global indexes, are marked `UNUSABLE` and must be rebuilt.
Index-organized	For local indexes, the behavior is identical to heap tables. All global indexes remain usable.

Regular (Heap)

Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

The local indexes for the new partition, and for the existing partition from which rows were redistributed, are marked UNUSABLE and must be rebuilt.
All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

Index-organized

For local indexes, the behavior is identical to heap tables.
All global indexes remain usable.

4.4.2.3 Adding a Partition to a List-Partitioned Table

The example in this topic shows how to add 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.

4.4.2.4 Adding a Partition to an Interval-Partitioned Table

You cannot explicitly add a partition to an interval-partitioned table. The database automatically creates a partition for an interval when data for that interval is inserted.

However, exchanging a partition of an interval-partitioned table that has not been materialized in the data dictionary, meaning to have an explicit entry in the data dictionary beyond the interval definition, you must manually materialize the partition using the ALTER TABLE LOCK PARTITION command.

To change the interval for future partitions, use the SET INTERVAL clause of the ALTER TABLE statement. The SET INTERVAL clause converts existing interval partitions to range partitions, determines the high value of the defined range partitions, and automatically creates partitions of the specified interval as needed for data that is beyond that high value. As a side effect, an interval-partitioned table does not have the notation of MAXVALUES.

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. To disable the creation of future interval partitions, and effectively revert to a range-partitioned table, use an empty value in the SET INTERVAL clause. Created interval partitions are transformed into range partitions with their current high values.

To increase the interval for date ranges, you must 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 must create another daily partition with a high bound of February 1, 2007 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 to add more partitions in the range portion of the interval-partitioned table.

To disable interval partitioning on the transactions table, use:

ALTER TABLE transactions SET INTERVAL ();

4.4.2.5 About Adding Partitions to a Composite *-Hash Partitioned Table

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

4.4.2.5.1 Adding a Partition to a *-Hash Partitioned Table

The example in this topic shows how to add a new partition to a [range | list | interval]-hash partitioned table.

For an interval-hash partitioned table, interval partitions are automatically created. You can specify a SUBPARTITIONS clause that lets you add a specified number of subpartitions, or a SUBPARTITION clause for naming specific subpartitions. If no SUBPARTITIONS or SUBPARTITION clause is specified, then the partition inherits table level defaults for subpartitions. For an interval-hash partitioned table, you can only add subpartitions to range or interval partitions that have been materialized.

This example adds a range partition q1_2000 to the range-hash partitioned table sales, which is populated with data for the first quarter of the year 2000. There are eight subpartitions stored in tablespace tbs5. The subpartitions cannot be set explicitly to use table compression. Subpartitions inherit the compression attribute from the partition level and are stored in a compressed form in this example:

ALTER TABLE sales ADD PARTITION q1_2000
      VALUES LESS THAN (2000, 04, 01) COMPRESS
      SUBPARTITIONS 8 STORE IN tbs5;

4.4.2.5.2 Adding a Subpartition to a *-Hash Partitioned Table

Use the MODIFY PARTITION ADD SUBPARTITION clause of the ALTER TABLE statement to add a hash subpartition to a [range | list | interval]-hash partitioned table.

The newly added subpartition is populated with rows rehashed from other subpartitions of the same partition as determined by the hash function. For an interval-hash partitioned table, you can only add subpartitions to range or interval partitions that have been materialized.

In the following example, a new hash subpartition us_loc5, stored in tablespace us1, is added to range partition locations_us in table diving.

ALTER TABLE diving MODIFY PARTITION locations_us
      ADD SUBPARTITION us_locs5 TABLESPACE us1;

Index subpartitions corresponding to the added and rehashed subpartitions must be rebuilt unless you specify UPDATE INDEXES.

4.4.2.6 About Adding Partitions to a Composite *-List Partitioned Table

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

4.4.2.6.1 Adding a Partition to a *-List Partitioned Table

The example in this topic shows how to add a new partition to a [range | list | interval]-list partitioned table.

The database automatically creates interval partitions as data for a specific interval is inserted. You can specify SUBPARTITION clauses for naming and providing value lists for the subpartitions. If no SUBPARTITION clauses are specified, then the partition inherits the subpartition template. If there is no subpartition template, then a single default subpartition is created.

The statement in Example 4-28 adds a new partition to the quarterly_regional_sales table that is partitioned by the range-list method. Some new physical attributes are specified for this new partition while table-level defaults are inherited for those that are not specified.

Example 4-28 Adding partitions to a range-list partitioned table

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')
         );

4.4.2.6.2 Adding a Subpartition to a *-List Partitioned Table

Use the MODIFY PARTITION ADD SUBPARTITION clause of the ALTER TABLE statement to add a list subpartition to a [range | list | interval]-list partitioned table.

For an interval-list partitioned table, you can only add subpartitions to range or interval partitions that have been materialized.

The following statement adds a new subpartition to the existing set of subpartitions in the range-list partitioned table quarterly_regional_sales. The new subpartition is created in tablespace ts2.

ALTER TABLE quarterly_regional_sales
   MODIFY PARTITION q1_1999 
      ADD SUBPARTITION q1_1999_south
         VALUES ('AR','MS','AL') tablespace ts2;

4.4.2.7 About Adding Partitions to a Composite *-Range Partitioned Table

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

4.4.2.7.1 Adding a Partition to a *-Range Partitioned Table

The example in this topic shows how to add a new partition to a [range | list | interval]-range partitioned table.

The database automatically creates interval partitions for an interval-range partitioned table when data is inserted in a specific interval. You can specify a SUBPARTITION clause for naming and providing ranges for specific subpartitions. If no SUBPARTITION clause is specified, then the partition inherits the subpartition template specified at the table level. If there is no subpartition template, then a single subpartition with a maximum value of MAXVALUE is created.

Example 4-29 adds a range partition p_2007_jan to the range-range partitioned table shipments, which is populated with data for the shipments ordered in January 2007. There are three subpartitions. Subpartitions inherit the compression attribute from the partition level and are stored in a compressed form in this example:

Example 4-29 Adding partitions to a range-range partitioned table

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'))
      ) ;

4.4.2.7.2 Adding a Subpartition to a *-Range Partitioned Table

You use the MODIFY PARTITION ADD SUBPARTITION clause of the ALTER TABLE statement to add a range subpartition to a [range | list | interval]-range partitioned table.

For an interval-range partitioned table, you can only add partitions to range or interval partitions that have been materialized.

The following example adds a range subpartition to the shipments table that contains all values with an order_date in January 2007 and a delivery_date on or after April 1, 2007.

ALTER TABLE shipments
   MODIFY PARTITION p_2007_jan
      ADD SUBPARTITION p07_jan_vl VALUES LESS THAN (MAXVALUE) ;

4.4.2.8 About 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 automatically cascades to any descendant reference partitioned tables. The DEPENDENT TABLES clause can set specific properties for dependent tables when you add partitions or subpartitions to a parent table.

See Also:

Oracle Database SQL Language Reference

4.4.2.9 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. To add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement.

4.4.2.10 Adding Multiple Partitions

You can add multiple new partitions and subpartitions with the ADD PARTITION and ADD SUBPARTITION clauses of the same ALTER TABLE statement.

When adding multiple partitions, local and global index operations are the same as when adding a single partition. Adding multiple partitions and subpartitions is only supported for range, list, and system partitions and subpartitions.

You can add multiple range partitions that are listed in ascending order of their upper bound values to the high end (after the last existing partition) of a range-partitioned or composite range-partitioned table, provided the MAXVALUE partition is not defined. Similarly, you can add multiple list partitions to a table using new sets of partition values if the DEFAULT partition does not exist.

Multiple system partitions can be added using a single SQL statement by specifying the individual partitions. For example, the following SQL statement adds multiple partitions to the range-partitioned sales table created in Example 4-1:

ALTER TABLE sales ADD 
  PARTITION sales_q1_2007 VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy')),
  PARTITION sales_q2_2007 VALUES LESS THAN (TO_DATE('01-JUL-2007','dd-MON-yyyy')),
  PARTITION sales_q3_2007 VALUES LESS THAN (TO_DATE('01-OCT-2007','dd-MON-yyyy')),
  PARTITION sales_q4_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy'))
;

You can use the BEFORE clause to add multiple new system partitions in relation to only one existing partition. The following SQL statements provide an example of adding multiple individual partitions using the BEFORE clause:

CREATE TABLE system_part_tab1 (number1 integer, number2 integer) 
PARTITION BY SYSTEM
( PARTITION p1,
  PARTITION p2,
  PARTITION p3,
  PARTITION p_last);

ALTER TABLE system_part_tab1 ADD 
  PARTITION p4,
  PARTITION p5,
  PARTITION p6
  BEFORE PARTITION p_last;

SELECT SUBSTR(TABLE_NAME,1,18) table_name, TABLESPACE_NAME, 
   SUBSTR(PARTITION_NAME,1,16) partition_name 
   FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SYSTEM_PART_TAB1';
TABLE_NAME         TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ----------------
SYSTEM_PART_TAB1   USERS                          P_LAST
SYSTEM_PART_TAB1   USERS                          P6
SYSTEM_PART_TAB1   USERS                          P5
SYSTEM_PART_TAB1   USERS                          P4
SYSTEM_PART_TAB1   USERS                          P3
SYSTEM_PART_TAB1   USERS                          P2
SYSTEM_PART_TAB1   USERS                          P1

Note:

See Restrictions and Special Requirements for Multi-Partition Operations on Tables with Domain Indexes

4.4.3 About Coalescing Partitions and Subpartitions

Coalescing partitions is a way of reducing the number of partitions in a hash partitioned table or index, or the number of subpartitions in a *-hash partitioned table.

When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by the database, and is dropped after its contents have been redistributed. If you coalesce a hash partition or subpartition in the parent table of a reference-partitioned table definition, then the reference-partitioned table automatically inherits the new partitioning definition.

Index partitions may be marked UNUSABLE as explained in the following table:

Table Type Index Behavior

Table Type	Index Behavior
Regular (Heap)	Unless you specify `UPDATE` `INDEXES` as part of the `ALTER` `TABLE` statement: Any local index partition corresponding to the selected partition is also dropped. Local index partitions corresponding to the one or more absorbing partitions are marked `UNUSABLE` and must be rebuilt. All global indexes, or all partitions of partitioned global indexes, are marked `UNUSABLE` and must be rebuilt.
Index-organized	Some local indexes are marked `UNUSABLE` as noted for heap indexes. All global indexes remain usable.

Regular (Heap)

Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

Any local index partition corresponding to the selected partition is also dropped. Local index partitions corresponding to the one or more absorbing partitions are marked UNUSABLE and must be rebuilt.
All global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

Index-organized

Some local indexes are marked UNUSABLE as noted for heap indexes.
All global indexes remain usable.

This section contains the following topics:

4.4.3.1 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;

4.4.3.2 Coalescing a Subpartition in a *-Hash Partitioned Table

The ALTER TABLE COALESCE SUBPARTITION statement is used to coalesce 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. 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 *-Hash Partitioned Table.

ALTER TABLE diving MODIFY PARTITION us_locations
     COALESCE SUBPARTITION;

4.4.3.3 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;

4.4.4 About Dropping Partitions and Subpartitions

You can drop partitions from range, interval, list, or composite *-[range | list] partitioned tables.

For interval partitioned tables, you can only drop range or interval partitions that have been materialized. For hash partitioned tables, or hash subpartitions of composite *-hash partitioned tables, you must perform a coalesce operation instead.

You cannot drop a partition from a reference-partitioned table. Instead, a drop operation on a parent table cascades to all descendant tables.

This section contains the following topics:

4.4.4.1 Dropping Table Partitions

To drop table partitions, use DROP PARTITION or DROP SUBPARTITION with the ALTER TABLE SQL statement.

The following statements 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

To preserve the data in the partition, use the MERGE PARTITION statement instead of the DROP PARTITION statement.

To remove data in the partition without dropping the partition, use the TRUNCATE 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:

If a table contains only one partition, you cannot drop the partition. 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.
With asynchronous global index maintenance, a drop partition update indexes operation is on metadata only and all global indexes remain valid.
Dropping a partition does not place the partition in the Oracle Database recycle bin, regardless of the setting of the recycle bin. Dropped partitions are immediately removed from the system.

The following sections contain some scenarios for dropping table partitions.

See Also:

About Merging Partitions and Subpartitions for information about merging a partition
About Truncating Partitions and Subpartitions for information about truncating a partition
Asynchronous Global Index Maintenance for Dropping and Truncating Partitions for information about asynchronous index maintenance for dropping partitions

4.4.4.1.1 Dropping a Partition from a Table that Contains Data and Global Indexes

There are several methods you can use to drop a partition from a table that contains data and global indexes.

If the partition contains data and one or more global indexes are defined on the table, then use one of the following methods (method 1, 2 or 3) to drop the table partition.

Method 1

Issue the ALTER TABLE DROP PARTITION statement without maintaining global indexes. Afterward, you must rebuild any global indexes (whether partitioned or not) because the index (or index partitions) has been marked UNUSABLE. The following statements provide an example of dropping partition dec98 from the sales table, then rebuilding its global nonpartitioned index.

ALTER TABLE sales DROP PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

If index sales_area_ix were a range-partitioned global index, then all partitions of the index would require rebuilding. Further, it is not possible to rebuild all partitions of an index in one statement. You must issue a separate REBUILD statement for each partition in the index. The following statements rebuild the index partitions jan99_ix to dec99_ix.

ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix;
...
ALTER INDEX sales_area_ix REBUILD PARTITION dec99_ix;

This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table. While asynchronous global index maintenance keeps global indexes valid without the need of any index maintenance, you must use the UPDATE INDEXES clause to enable this new functionality. This behavior ensures backward compatibility.

Method 2

Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE DROP PARTITION statement. The DELETE statement updates the global indexes.

For example, to drop the first partition, issue the following statements:

DELETE FROM sales partition (dec98);
ALTER TABLE sales DROP PARTITION dec98;

This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.

Method 3

Specify UPDATE INDEXES in the ALTER TABLE statement. Doing so leverages the new asynchronous global index maintenance. Indexes remain valid.

ALTER TABLE sales DROP PARTITION dec98
     UPDATE INDEXES;

4.4.4.1.2 Dropping a Partition Containing Data and Referential Integrity Constraints

There are several methods you can use to drop a partition containing data and referential integrity constraints.

If a partition contains data and the table has referential integrity constraints, choose either of the following methods (method 1 or 2) to drop the table partition. This table has a local index only, so it is not necessary to rebuild any indexes.

Method 1

If there is no data referencing the data in the partition to drop, then you can disable the integrity constraints on the referencing tables, issue the ALTER TABLE DROP PARTITION statement, then re-enable the integrity constraints.

This method is most appropriate for large tables where the partition being dropped contains a significant percentage of the total data in the table. If there is still data referencing the data in the partition to be dropped, then ensure the removal of all the referencing data so that you can re-enable the referential integrity constraints.

Method 2

If there is data in the referencing tables, then you can issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE DROP PARTITION statement. The DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo logs. The delete can succeed if you created the constraints with the ON DELETE CASCADE option, deleting all rows from referencing tables as well.

DELETE FROM sales partition (dec94);
ALTER TABLE sales DROP PARTITION dec94;

This method is most appropriate for small tables or for large tables when the partition being dropped contains a small percentage of the total data in the table.

4.4.4.2 Dropping Interval Partitions

You can drop interval partitions in an interval-partitioned table.

This operation drops the data for the interval only and leaves the interval definition in tact. If data is inserted in the interval just dropped, then the database again creates 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 are invalidated.

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

4.4.4.3 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;

Note:

You cannot drop the highest partition in a global index.

4.4.4.4 Dropping Multiple Partitions

You can remove multiple partitions or subpartitions from a range or list partitioned table with the DROP PARTITION and DROP SUBPARTITION clauses of the SQL ALTER TABLE statement.

For example, the following SQL statement drops multiple partitions from the range-partitioned table sales.

ALTER TABLE sales DROP PARTITION sales_q1_2008, sales_q2_2008,
     sales_q3_2008, sales_q4_2008;

You cannot drop all the partitions of a table. When dropping multiple partitions, local and global index operations are the same as when dropping a single partition.

4.4.5 About Exchanging Partitions and Subpartitions

You can convert a partition or subpartition into a nonpartitioned table, and a nonpartitioned table into a partition or subpartition of a partitioned table by exchanging their data segments.

You can also convert a hash partitioned table into a partition of a composite *-hash partitioned table, or convert the partition of a composite *-hash partitioned table into a hash partitioned table. Similarly, you can convert a range- or list-partitioned table into a partition of a composite *-range or -list partitioned table, or convert a partition of the composite *-range or -list partitioned table into a range- or list-partitioned table.

Exchanging table partitions is useful to get data quickly in or out of a partitioned table. For example, in data warehousing environments, exchanging partitions facilitates high-speed data loading of new, incremental data into an existing partitioned table.

Note that during the exchange process the data from the source is moved to the target and the data from the target is moved to the source.

OLTP and data warehousing environments benefit from exchanging old data partitions out of a partitioned table. The data is purged from the partitioned table without actually being deleted and can be archived separately afterward.

When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged with the INCLUDING INDEXES clause, and if rows are to be validated for proper mapping with the WITH VALIDATION clause.

Note:

When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation is performed as if WITH VALIDATION were specified to maintain the integrity of the constraints.

To avoid the overhead of this validation activity, issue the following statement for each constraint before performing the exchange partition operation:

ALTER TABLE table_name
     DISABLE CONSTRAINT constraint_name KEEP INDEX

Enable the constraints after the exchange.

If you specify WITHOUT VALIDATION, then you must ensure that the data to be exchanged belongs in the partition you exchange. You can use the ORA_PARTITION_VALIDATION SQL function to help identify those records that have been inserted incorrectly in the wrong partition.

Unless you specify UPDATE INDEXES, the Oracle Database marks the global indexes or all global index partitions on the table whose partition is being exchanged as UNUSABLE. Global indexes or global index partitions on the table being exchanged remain invalidated.

You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.

Incremental statistics on a partitioned table are maintained with a partition exchange operation if the statistics were gathered on the nonpartitioned table when DBMS_STATS table preferences INCREMENTAL is set to true and INCREMENTAL_LEVEL is set to TABLE.

Note:

In situations where column statistics for virtual columns are out of order, the column statistics are deleted rather than retaining the stale statistics. Information about this deletion is written to the alert log file.

This section contains the following topics:

See Also:

Partitioning Key for information about validating partition content
Viewing Information About Partitioned Tables and Indexes for information about using views to monitor details about partitioned tables and indexes
Oracle Database SQL Tuning Guide for more information about incremental statistics
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_STATS package

4.4.5.1 Creating a Table for Exchange With a Partitioned Table

Tables can be created with the FOR EXCHANGE WITH clause to exactly match the shape of a partitioned table and be eligible for a partition exchange command. However, indexes are not created as an operation of this command.

Because the FOR EXCHANGE WITH clause of CREATE TABLE provides an exact match between a non-partitioned and partitioned table, this is an improvement over the CREATE TABLE AS SELECT statement.

The following list is a summary of the effects of the CREATE TABLE FOR EXCHANGE WITH DDL operation:

The use case of this DDL operation is to facilitate creation of a table to be used for exchange partition DDL.
The operation creates a clone of the for exchange table in terms of column ordering and column properties.
Columns cannot be renamed. The table being created inherits the names from the for exchange table.
The only logical property that can be specified during the DDL operation is the partitioning specification of the table.

The partitioning clause is only relevant for the exchange with a partition of a composite-partitioned table. In this case, a partition with n subpartitions is exchanged with a partitioned table with n partitions matching the subpartitions. You are responsible for the definition of the partitioning clause for this exchange in this scenario.

The subpartitioning can be asymmetrical across partitions. The partitioning clause has to match exactly the subpartitioning of the partition to being exchanged.
The physical properties which can be specified are primarily table segment attributes.
Column properties copied with this DDL operation include, but are not limited to, the following: unusable columns, invisible columns, virtual expression columns, functional index expression columns, and other internal settings and attributes.

The following is an example of the use of the CREATE TABLE statement with the FOR EXCHANGE WITH clause to create a table that mimics the shape of an existing table in terms of column ordering and properties.

Example 4-30 Using the FOR EXCHANGE WITH clause of CREATE TABLE

CREATE TABLE sales_by_year_table
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    PARTITION BY RANGE (time_id)
     (PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')),
      PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')),
      PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
      PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')),
      PARTITION sales_future VALUES LESS THAN (MAXVALUE)
 );

DESCRIBE sales_by_year_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                   NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 TIME_ID                                   NOT NULL DATE
 CHANNEL_ID                                NOT NULL NUMBER
 PROMO_ID                                  NOT NULL NUMBER
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

CREATE TABLE sales_later_year_table
  FOR EXCHANGE WITH TABLE sales_by_year_table;

DESCRIBE sales_later_year_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                   NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 TIME_ID                                   NOT NULL DATE
 CHANNEL_ID                                NOT NULL NUMBER
 PROMO_ID                                  NOT NULL NUMBER
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)

4.4.5.2 Exchanging a Range, Hash, or List Partition

To exchange a partition of a range, hash, or list partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE EXCHANGE PARTITION statement.

The following is an example of exchanging range partitions with a nonpartitioned table.

Example 4-31 Exchanging a Range Partition

CREATE TABLE sales_future_table
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    PARTITION BY RANGE (time_id)
     (PARTITION s_2020 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy')),
      PARTITION s_2021 VALUES LESS THAN (TO_DATE('01-01-2022','dd-mm-yyyy')),
      PARTITION s_2022 VALUES LESS THAN (TO_DATE('01-01-2023','dd-mm-yyyy'))
 );

CREATE TABLE sales_exchange_table
  FOR EXCHANGE WITH TABLE sales_future_table;

INSERT INTO sales_exchange_table VALUES (1002,110,TO_DATE('19-02-2020','dd-mm-yyyy'),12,18,150,4800);
INSERT INTO sales_exchange_table VALUES (1001,100,TO_DATE('12-03-2020','dd-mm-yyyy'),10,15,400,6500);
INSERT INTO sales_exchange_table VALUES (1001,100,TO_DATE('31-05-2020','dd-mm-yyyy'),10,15,600,8000);
INSERT INTO sales_exchange_table VALUES (2105,101,TO_DATE('25-06-2020','dd-mm-yyyy'),12,19,100,3000);
INSERT INTO sales_exchange_table VALUES (1002,120,TO_DATE('31-08-2020','dd-mm-yyyy'),10,15,400,6000);
INSERT INTO sales_exchange_table VALUES (2105,101,TO_DATE('25-10-2020','dd-mm-yyyy'),12,19,250,7500);

ALTER TABLE sales_future_table
    EXCHANGE PARTITION s_2020 WITH TABLE sales_exchange_table;

SELECT * FROM sales_future_table PARTITION(s_2020);
   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1002        110 19-FEB-20         12         18           150        4800
      1001        100 12-MAR-20         10         15           400        6500
      1001        100 31-MAY-20         10         15           600        8000
      2105        101 25-JUN-20         12         19           100        3000
      1002        120 31-AUG-20         10         15           400        6000
      2105        101 25-OCT-20         12         19           250        7500
6 rows selected.

REM Note that all records have been removed from the sales_exchange_table
SELECT * FROM sales_exchange_table;
no rows selected

INSERT INTO sales_exchange_table VALUES (1002,110,TO_DATE('15-02-2021','dd-mm-yyyy'),12,18,300,9500);
INSERT INTO sales_exchange_table VALUES (1002,120,TO_DATE('31-03-2021','dd-mm-yyyy'),10,15,200,3000);
INSERT INTO sales_exchange_table VALUES (2105,101,TO_DATE('25-04-2021','dd-mm-yyyy'),12,19,150,9000);

ALTER TABLE sales_future_table
    EXCHANGE PARTITION s_2021 WITH TABLE sales_exchange_table;

SELECT * FROM sales_future_table PARTITION(s_2021);
   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1002        110 15-FEB-21         12         18           300        9500
      1002        120 31-MAR-21         10         15           200        3000
      2105        101 25-APR-21         12         19           150        9000
3 rows selected.

4.4.5.3 Exchanging a Partition of an Interval Partitioned Table

You can exchange interval partitions in an interval-partitioned table. However, you must ensure that the interval partition has been created before you can exchange the partition.

The following example shows a partition exchange for the interval_sales table, interval-partitioned using monthly partitions as of January 1, 2007. 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.

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. You can determine the partition name by querying the *_TAB_PARTITIONS data dictionary view to display the system-generated partition name.

4.4.5.4 Exchanging a Partition of a Reference-Partitioned Table

You can exchange partitions in a reference-partitioned table, but you must ensure that the data that you reference is available in the respective partition in the parent table.

Example 4-32 shows a partition exchange load scenario for the range-partitioned orders table, and the reference partitioned order_items table. The data in the order_items_2018_dec table only contains order item data for orders with an order_date in December 2018.

You must 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 must create or enable the foreign key constraint on the order_items_2018_dec table in order for the partition exchange on the reference-partitioned table to succeed.

For information and an example using exchanging with the CASCADE keyword, refer to About Exchanging a Partition with the Cascade Option.

Example 4-32 Exchanging a partition for a reference-partitioned table

CREATE TABLE orders (
       order_id number NOT NULL, 
       order_date DATE,
       CONSTRAINT order_pk PRIMARY KEY (order_id))
       PARTITION by range (order_date) 
       (PARTITION p_2018_dec values less than ('01-JAN-2019'));
 
CREATE TABLE order_items  (
       order_item_id NUMBER NOT NULL,
       order_id NUMBER not null,
       order_item VARCHAR2(100),
       CONSTRAINT order_item_pk PRIMARY KEY (order_item_id), 
       CONSTRAINT order_item_fk FOREIGN KEY (order_id) references orders(order_id) on delete cascade)
       PARTITION by reference (order_item_fk);

CREATE TABLE orders_2018_dec (
       order_id NUMBER, 
       order_date DATE, 
       CONSTRAINT order_2018_dec_pk PRIMARY KEY (order_id));

INSERT into orders_2018_dec values (1,'01-DEC-2018');
COMMIT;

CREATE TABLE order_items_2018_dec (
       order_item_id NUMBER,
       order_id NUMBER NOT NULL, 
       order_item VARCHAR2(100),
       CONSTRAINT order_item_2018_dec_pk PRIMARY KEY (order_item_id), 
       CONSTRAINT order_item_2018_dec_fk FOREIGN KEY (order_id) references orders_2018_dec (order_id) on delete cascade);

INSERT into order_items_2018_dec values (1,1,'item A');
INSERT into order_items_2018_dec values (2,1,'item B');

REM You must disable or DROP the constraint before the exchange
ALTER TABLE order_items_2018_dec DROP CONSTRAINT order_item_2018_dec_fk;

REM ALTER TABLE is successful with disabled PK-FK
ALTER TABLE orders
  EXCHANGE PARTITION p_2018_dec
  WITH TABLE orders_2018_dec
  UPDATE GLOBAL INDEXES;

REM You must establish the PK-FK with the future parent prior to this exchange
ALTER TABLE order_items_2018_dec
  ADD CONSTRAINT order_items_dec_2018_fk
  FOREIGN KEY (order_id)
  REFERENCES orders(order_id) ;

REM Complete the exchange
ALTER TABLE order_items
  EXCHANGE PARTITION p_2018_dec
  WITH TABLE order_items_2018_dec;

REM Display the data
SELECT * FROM orders;
  ORDER_ID ORDER_DAT
---------- ---------
         1 01-DEC-18

SELECT * FROM order_items;
ORDER_ITEM_ID   ORDER_ID ORDER_ITEM
------------- ---------- ------------
            1          1 item A
            2          1 item B

4.4.5.5 About 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 must 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 must include the virtual column definition to match the partitioned table's constraint and index definitions. This scenario also applies to virtual column-based partitioned tables.

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

You can exchange 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 follows:

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 equals 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;

4.4.5.7 Exchanging a Subpartition of a *-Hash Partitioned Table

You can use the ALTER TABLE EXCHANGE SUBPARTITION statement to convert a hash subpartition of a *-hash partitioned table into a nonpartitioned table, or the reverse.

The following example converts the subpartition q3_1999_s1 of table sales into the nonpartitioned 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;

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

You can use the ALTER TABLE EXCHANGE PARTITION statement to exchange a list-partitioned table with a *-list partition.

The semantics are the same as described previously in Exchanging a Hash Partitioned Table with a *-Hash Partition. The following example 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;

4.4.5.9 About Exchanging a Subpartition of a *-List Partitioned Table

You can use the ALTER TABLE EXCHANGE SUBPARTITION statement to exchange a subpartition of a *-list partitioned table.

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

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

You can use the ALTER TABLE EXCHANGE PARTITION statement to exchange 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.

ALTER TABLE orders
  EXCHANGE PARTITION
   FOR (TO_DATE('01-MAR-2007','dd-MON-yyyy'))
   WITH TABLE orders_mar_2007
   WITH VALIDATION;

4.4.5.11 About Exchanging a Subpartition of a *-Range Partitioned Table

You can use the ALTER TABLE EXCHANGE SUBPARTITION statement to exchange a subpartition of a *-range partition.

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

4.4.5.12 About Exchanging a Partition with the Cascade Option

You can cascade exchange operations to reference partitioned child tables with the CASCADE option of the ALTER TABLE EXCHANGE PARTITION and ALTER TABLE EXCHANGE SUBPARTITION SQL statements.

Cascading exchange operations require all foreign key constraints to being defined as ON DELETE CASCADE.

When the CASCADE option for ALTER TABLE EXCHANGE PARTITION and ALTER TABLE EXCHANGE SUBPARTITION is specified, the EXCHANGE operation cascades to reference partitioned tables that are children of the targeted table. The exchange operation can be targeted at any level in a reference partitioned hierarchy and cascades to child tables starting from the targeted table. Privileges are not required on the child tables, but ordinary restrictions on the exchange operation apply for all tables affected by the operation. The CASCADE option is ignored if it is specified for a table that does not have reference partitioned children.

The reference partitioned hierarchy of the targeted table and the reference partitioned hierarchy of the exchange table must match. The CASCADE option is not supported if the same parent key is referenced by multiple dependent tables. Having more than one dependent table relying on the same primary key makes it impossible for the kernel to unambiguously identify how to exchange the dependent partitions. Any other options specified for the operation, such as UPDATE INDEXES, applies for all tables affected by the operation.

The cascade options are off by default so they do not affect Oracle Database compatibility.

The following example shows the use of CASCADE when exchanging the a partition of a referenced-partitioned table.

Example 4-33 Exchanging a partition using cascade for a reference-partitioned table

CREATE TABLE orders (
       order_id number NOT NULL, 
       order_date DATE,
       CONSTRAINT order_pk PRIMARY KEY (order_id))
       PARTITION by range (order_date) 
       (PARTITION p_2018_dec values less than ('01-JAN-2019'));
 
CREATE TABLE order_items  (
       order_item_id NUMBER NOT NULL,
       order_id NUMBER not null,
       order_item VARCHAR2(100),
       CONSTRAINT order_item_pk PRIMARY KEY (order_item_id), 
       CONSTRAINT order_item_fk FOREIGN KEY (order_id) references orders(order_id) on delete cascade)
       PARTITION by reference (order_item_fk);

CREATE TABLE orders_2018_dec (
       order_id NUMBER, 
       order_date DATE, 
       CONSTRAINT order_2018_dec_pk PRIMARY KEY (order_id));

INSERT into orders_2018_dec values (1,'01-DEC-2018');

CREATE TABLE order_items_2018_dec (
       order_item_id NUMBER,
       order_id NUMBER NOT NULL, 
       order_item VARCHAR2(100),
       CONSTRAINT order_item_2018_dec_pk PRIMARY KEY (order_item_id), 
       CONSTRAINT order_item_2018_dec_fk FOREIGN KEY (order_id) references orders_2018_dec (order_id) on delete cascade);

INSERT into order_items_2018_dec values (1,1,'item A new');
INSERT into order_items_2018_dec values (2,1,'item B new');

REM Display data from reference partitioned tables before exchange
SELECT * FROM orders;
no rows selected

SELECT * FROM order_items;
no rows selected

REM ALTER TABLE using cascading exchange
ALTER TABLE orders
   EXCHANGE PARTITION p_2018_dec
   WITH TABLE orders_2018_dec
    CASCADE
   UPDATE GLOBAL INDEXES;

REM Display data from reference partitioned tables after exchange
SELECT * FROM orders;
  ORDER_ID ORDER_DAT
---------- ---------
         1 01-DEC-18

SELECT * FROM order_items;
ORDER_ITEM_ID   ORDER_ID ORDER_ITEM
------------- ---------- ------------
            1          1 item A new
            2          1 item B new

4.4.6 About Merging Partitions and Subpartitions

Use the ALTER TABLE MERGE PARTITION and SUBPARTITION SQL statements to merge the contents of two partitions or subpartitions.

The two original partitions or subpartitions are dropped, as are any corresponding local indexes. You cannot use this statement for a hash partitioned table or for hash subpartitions of a composite *-hash partitioned table.

You cannot merge partitions for a reference-partitioned table. Instead, a merge operation on a parent table cascades to all descendant tables. However, you can use the DEPENDENT TABLES clause to set specific properties for dependent tables when you issue the merge operation on the parent table to merge partitions or subpartitions.

You can use the ONLINE keyword with the ALTER TABLE MERGE PARTITION and SUBPARTITION SQL statements to enable online merge operations for regular (heap-organized) tables. For an example of the use of the ONLINE keyword, see Example 4-34.

If the involved partitions or subpartitions contain data, then indexes may be marked UNUSABLE as described in the following table:

Table Type Index Behavior

Table Type	Index Behavior
Regular (Heap)	Unless you specify `UPDATE` `INDEXES` as part of the `ALTER` `TABLE` statement: The database marks `UNUSABLE` all resulting corresponding local index partitions or subpartitions. Global indexes, or all partitions of partitioned global indexes, are marked `UNUSABLE` and must be rebuilt.
Index-organized	The database marks `UNUSABLE` all resulting corresponding local index partitions. All global indexes remain usable.

Regular (Heap)

Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

The database marks UNUSABLE all resulting corresponding local index partitions or subpartitions.
Global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

Index-organized

The database marks UNUSABLE all resulting corresponding local index partitions.
All global indexes remain usable.

This section contains the following topics:

Oracle Database SQL Language Reference

4.4.6.1 Merging Range Partitions

You can 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.

Example 4-34 shows an example of merging range partitions using the ONLINE keyword.

Example 4-34 Merging range partitions

-- First, create a partitioned table with four partitions, each on its own
-- tablespace, partitioned by range on the date 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-2017','dd-mon-yyyy'))
   TABLESPACE quarter_one,
PARTITION quarter_two 
   VALUES LESS THAN ( TO_DATE('01-JUL-2017','dd-mon-yyyy'))
   TABLESPACE quarter_two,
PARTITION quarter_three
   VALUES LESS THAN ( TO_DATE('01-OCT-2017','dd-mon-yyyy'))
   TABLESPACE quarter_three,
PARTITION quarter_four
   VALUES LESS THAN ( TO_DATE('01-JAN-2018','dd-mon-yyyy'))
   TABLESPACE quarter_four
);
-- 
-- Create local PREFIXED indexes 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
);


SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='FOUR_SEASONS';
TABLE_NAME                          PARTITION_NAME
----------------------------------- -------------------------
FOUR_SEASONS                        QUARTER_FOUR
FOUR_SEASONS                        QUARTER_ONE
FOUR_SEASONS                        QUARTER_THREE
FOUR_SEASONS                        QUARTER_TWO

-- Next, merge the first two partitions
ALTER TABLE four_seasons 
  MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two
  UPDATE INDEXES 
  ONLINE;

SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='FOUR_SEASONS';
TABLE_NAME                          PARTITION_NAME
----------------------------------- -------------------------
FOUR_SEASONS                        QUARTER_FOUR
FOUR_SEASONS                        QUARTER_THREE
FOUR_SEASONS                        QUARTER_TWO

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

-- Rebuild the index for quarter_two, which has been marked unusable 
-- because it has not had all of the data from quarter_one added to it.
-- Rebuilding the index corrects this condition.
--
ALTER TABLE four_seasons MODIFY PARTITION quarter_two 
  REBUILD UNUSABLE LOCAL INDEXES;

4.4.6.2 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 result is that the range section of the interval-partitioned table is extended to the upper bound of the two merged partitions. Any materialized interval partitions with boundaries lower than the newly merged partition are automatically 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')));

Inserting data into the interval section of the table creates the interval partitions for these days. The data for 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 again has 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.

4.4.6.3 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, then the resulting partition is 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')

4.4.6.4 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.

The inheritance of properties is different when a *-hash partition is split, 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. The transition point moves 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;

See Also:

Splitting a *-Hash Partition for information about splitting a hash partition
Merging Interval Partitions for information about merging interval partitions

4.4.6.5 About Merging *-List Partitions

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

This section contains the following topics.

4.4.6.5.1 Merging Partitions in a *-List Partitioned Table

When you merge two *-list partitions, the resulting new partition inherits the subpartition descriptions from the subpartition template, if a template exists. If no subpartition template exists, then a single default subpartition is created for the new partition.

For interval-list partitioned tables, you can only merge two adjacent interval partitions, or the highest range partition with the first interval partition. The transition point moves when you merge intervals in an interval-list partitioned table.

The following statement merges two partitions in the range-list partitioned stripe_regional_sales table. A subpartition template exists for the table.

ALTER TABLE stripe_regional_sales
   MERGE PARTITIONS q1_1999, q2_1999 INTO PARTITION q1_q2_1999
      STORAGE(MAXEXTENTS 20);

Some new physical attributes are specified for this new partition while table-level defaults are inherited for those that are not specified. The new resulting partition q1_q2_1999 inherits the high-value bound of the partition q2_1999 and the subpartition value-list descriptions from the subpartition template description of the table.

The data in the resulting partitions consists of data from both the partitions. However, there may be cases where the database returns an error. This can occur because data may map out of the new partition when both of the following conditions exist:

This error condition can be eliminated by always specifying a default partition in the default subpartition template.

Some literal values of the merged subpartitions were not included in the subpartition template.
The subpartition template does not contain a default partition definition.

See Also:

Merging List Partitions for information about merging partitions in a *-list partitioned table
Merging Interval Partitions for information about merging interval partitions

4.4.6.5.2 Merging Subpartitions in a *-List Partitioned Table

You can merge the contents of any two arbitrary list subpartitions belonging to the same partition.

The resulting subpartition value-list descriptor includes all of the literal values in the value lists for the partitions being merged.

The following statement merges two subpartitions of a table partitioned using range-list method into a new subpartition located in tablespace ts4:

ALTER TABLE quarterly_regional_sales
   MERGE SUBPARTITIONS q1_1999_northwest, q1_1999_southwest
      INTO SUBPARTITION q1_1999_west
         TABLESPACE ts4;

The value lists for the original two partitions were:

Subpartition q1_1999_northwest was described as ('WA','OR')
Subpartition q1_1999_southwest was described as ('AZ','NM','UT')

The resulting subpartition value list comprises the set that represents the union of these two subpartition value lists:

Subpartition q1_1999_west has a value list described as ('WA','OR','AZ','NM','UT')

The tablespace in which the resulting subpartition is located and the subpartition attributes are determined by the partition-level default attributes, except for those specified explicitly. If any of the existing subpartition names are being reused, then the new subpartition inherits the subpartition attributes of the subpartition whose name is being reused.

4.4.6.6 About Merging *-Range Partitions

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

Merging Partitions in a *-Range Partitioned Table

4.4.6.6.1 Merging Partitions in a *-Range Partitioned Table

When you merge two *-range partitions, the resulting new partition inherits the subpartition descriptions from the subpartition template, if one exists. If no subpartition template exists, then a single subpartition with an upper boundary MAXVALUE is created for the new partition.

For interval-range partitioned tables, you can only merge two adjacent interval partitions, or the highest range partition with the first interval partition. The transition point moves when you merge intervals in an interval-range partitioned table.

The following statement merges two partitions in the monthly interval-range partitioned orders table. A subpartition template exists for the table.

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;

If the March 2007 and April 2007 partitions were still in the interval section of the interval-range partitioned table, then the merge operation would move the transition point to May 1, 2007.

The subpartitions for partition p_pre_may_2007 inherit their properties from the subpartition template. The data in the resulting partitions consists of data from both the partitions. However, there may be cases where the database returns an error. This can occur because data may map out of the new partition when both of the following conditions are met:

The error condition can be eliminated by always specifying a subpartition with an upper boundary of MAXVALUE in the subpartition template.

Some range values of the merged subpartitions were not included in the subpartition template.
The subpartition template does not have a subpartition definition with a MAXVALUE upper boundary.

See Also:

Merging Range Partitions for information about merging partitions in a *-range partitioned table
Merging Interval Partitions for information about merging interval partitions

4.4.6.7 Merging Multiple Partitions

You can merge the contents of two or more partitions or subpartitions into one new partition or subpartition and then drop the original partitions or subpartitions with the MERGE PARTITIONS and MERGE SUBPARTITIONS clauses of the ALTER TABLE SQL statement.

The MERGE PARTITIONS and MERGE SUBPARTITIONS clauses are synonymous with the MERGE PARTITION and MERGE SUBPARTITION clauses.

For example, the following SQL statement merges four partitions into one partition and drops the four partitions that were merged.

ALTER TABLE t1 MERGE PARTITIONS p01, p02, p03, p04 INTO p0;

When merging multiple range partitions, the partitions must be adjacent and specified in the ascending order of their partition bound values. The new partition inherits the partition upper bound of the highest of the original partitions.

You can specify the lowest and the highest partitions to be merged when merging multiple range partitions with the TO syntax. All partitions between specified partitions, including those specified, are merged into the target partition. You cannot use this syntax for list and system partitions.

For example, the following SQL statements merges partitions p01 through p04 into the partition p0.

ALTER TABLE t1 MERGE PARTITIONS p01 TO p04 INTO p0;

List partitions and system partitions that you want to merge do not need to be adjacent, because no ordering of the partitions is assumed. When merging multiple list partitions, the resulting partition value list are the union of the set of partition value list of all of the partitions to be merged. A DEFAULT list partition merged with other list partitions results in a DEFAULT partition.

When merging multiple partitions of a composite partitioned table, the resulting new partition inherits the subpartition descriptions from the subpartition template, if one exists. If no subpartition template exists, then Oracle creates one MAXVALUE subpartition from range subpartitions or one DEFAULT subpartition from list subpartitions for the new partition. When merging multiple subpartitions of a composite partitioned table, the subpartitions to be merged must belong to the same partition.

When merging multiple partitions, local and global index operations and semantics for inheritance of unspecified physical attributes are the same for merging two partitions.

In the following SQL statement, four partitions of the partitioned by range table sales are merged. These four partitions that correspond to the four quarters of the oldest year are merged into a single partition containing the entire sales data of the year.

ALTER TABLE sales
  MERGE PARTITIONS sales_q1_2009, sales_q2_2009, sales_q3_2009, sales_q4_2009
  INTO PARTITION sales_2009;

The previous SQL statement can be rewritten as the following SQL statement to obtain the same result.

ALTER TABLE sales
  MERGE PARTITIONS sales_q1_2009 TO sales_q4_2009
  INTO PARTITION sales_2009;

Note:

See Restrictions and Special Requirements for Multi-Partition Operations on Tables with Domain Indexes

4.4.7 About Modifying Attributes of Tables, Partitions, and Subpartitions

The modification of attributes of tables, partitions, and subpartitions is introduced in this topic.

4.4.7.1 About Modifying Default Attributes

You can modify the default attributes of a table, or for a partition of a composite partitioned table.

When you modify default attributes, the new attributes affect only future partitions, or subpartitions, that are created. The default values can still be specifically overridden when creating a new partition or subpartition. You can modify the default attributes of a reference-partitioned table.

This section contains the following topics:

4.4.7.1.1 Modifying Default Attributes of a Table

You can modify the default attributes that are 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.

4.4.7.1.2 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 the range-hash partitioned table reside.

ALTER TABLE employees_subpartitions
     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. To change the way in which future subpartitions in an interval-partitioned table are created, you must modify the subpartition template.

4.4.7.1.3 Modifying Default Attributes of Index Partitions

In a similar fashion to table partitions, you can alter the default attributes that are 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.

4.4.7.2 About Modifying Real Attributes of Partitions

It is possible to modify attributes of an existing partition of a table or index.

You cannot change the TABLESPACE attribute. Use ALTER TABLE MOVE PARTITION/SUBPARTITION to move a partition or subpartition to a new tablespace.

This section contains the following topics:

4.4.7.2.1 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.

4.4.7.2.2 Modifying Real Attributes for a Hash Partition

You can 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 the table:

ALTER TABLE departments_rebuild_index MODIFY PARTITION p1
     REBUILD UNUSABLE LOCAL INDEXES;

4.4.7.2.3 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 employees_rebuild_index MODIFY SUBPARTITION p3_s1
     REBUILD UNUSABLE LOCAL INDEXES;

4.4.7.2.4 Modifying Real Attributes of Index Partitions

The MODIFY PARTITION clause of ALTER INDEX enables you to 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.

4.4.8 About Modifying List Partitions

The modification of values in list partitions and subpartitions is introduced in this topic.

4.4.8.1 About Modifying List Partitions: Adding Values

List partitioning enables you to optionally add literal values from the defining value list.

This section contains the following topics:

4.4.8.1.1 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 to add values to a list partition, the database must check that the values being added do not exist in the default partition. If any of the values do exist in the default partition, then an error is displayed.

Note:

The database runs a query to check for the existence of rows in the default partition that correspond to the literal values being added. Therefore, it is advisable to create a local prefixed index on the table. This speeds up the execution of the query and the overall operation.

You cannot add values to a default list partition.

4.4.8.1.2 Adding Values for a List Subpartition

Use the MODIFY SUBPARTITION ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing subpartition.

This operation is essentially the same as described for About 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. To add values to subpartitions of interval partitions that have not yet been created, you must modify the subpartition template.

4.4.8.2 About Modifying List Partitions: Dropping Values

List partitioning enables you to optionally drop literal values from the defining value list.

This section contains the following topics:

4.4.8.2.1 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.

Note:

You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE DROP PARTITION statement instead.

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');

Note:

The database runs a query to check for the existence of rows in the partition that correspond to the literal values being dropped. Therefore, it is advisable to create a local prefixed index on the table. This speeds up the query and the overall operation.

You cannot drop values from a default list partition.

4.4.8.2.2 Dropping Values from a List Subpartition

Use the MODIFY SUBPARTITION DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing subpartition.

This operation is essentially the same as described for About 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. To drop values from subpartitions of interval partitions that have not yet been created, you must modify the subpartition template.

4.4.9 About Modifying the Partitioning Strategy

You can change the partitioning strategy of a regular (heap-organized) table with the ALTER TABLE MODIFY PARTITION SQL statement.

Modifying the partitioning strategy, such as hash partitioning to composite range-hash partitioning, can be performed offline or online. When performed in online mode, the conversion does not impact ongoing DML operations. When performed in offline mode, the conversion does not allow concurrent DML operations during the modification.

Indexes are maintained as part of the table modification. When modifying the partitioning strategy, all unspecified indexes whose index columns are a prefix of the new partitioning key are automatically converted to a local partitioned index; otherwise, an index is converted to global index.

The modification operation is not supported with domain indexes. The UPDATE INDEXES clause cannot change the columns on which the list of indexes was originally defined or the uniqueness property of the index or any other index property.

For information about converting a non-partitioned table to a partitioned table, refer to Converting a Non-Partitioned Table to a Partitioned Table.

Example 4-35 shows the use of ALTER TABLE to convert a range partitioned table to a composite range-hash partitioned table online. During the ALTER TABLE modification in the example, indexes are updated.

Live SQL:

View and run a related example on Oracle Live SQL at Modifying the Partitioning Strategy of a Table.

Example 4-35 Modifying the partitioning strategy

CREATE TABLE mod_sales_partitioning
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
  PARTITION BY RANGE (time_id)
  (PARTITION sales_q1_2017 VALUES LESS THAN (TO_DATE('01-APR-2017','dd-MON-yyyy')),
   PARTITION sales_q2_2017 VALUES LESS THAN (TO_DATE('01-JUL-2017','dd-MON-yyyy')),
   PARTITION sales_q3_2017 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy')),
   PARTITION sales_q4_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy'))
 ); 

CREATE INDEX i1_cust_id_indx ON mod_sales_partitioning (cust_id) LOCAL;
CREATE INDEX i2_time_id_indx ON mod_sales_partitioning (time_id);
CREATE INDEX i3_prod_id_indx ON mod_sales_partitioning (prod_id);

SELECT TABLE_NAME, PARTITIONING_TYPE FROM USER_PART_TABLES WHERE TABLE_NAME ='MOD_SALES_PARTITIONING';
TABLE_NAME                PARTITION_NAME 
------------------------- --------------
MOD_SALES_PARTITIONING    RANGE

SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='MOD_SALES_PARTITIONING';
TABLE_NAME                PARTITION_NAME 
------------------------- --------------
MOD_SALES_PARTITIONING    SALES_Q1_2017
MOD_SALES_PARTITIONING    SALES_Q2_2017
MOD_SALES_PARTITIONING    SALES_Q3_2017
MOD_SALES_PARTITIONING    SALES_Q4_2017
...

ALTER TABLE mod_sales_partitioning
 MODIFY 
 PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
  SUBPARTITIONS 8
 ( PARTITION  sales_q1_2017 VALUES LESS THAN (TO_DATE('01-APR-2017','dd-MON-yyyy')),
    PARTITION sales_q2_2017 VALUES LESS THAN (TO_DATE('01-JUL-2017','dd-MON-yyyy')),
    PARTITION sales_q3_2017 VALUES LESS THAN (TO_DATE('01-OCT-2017','dd-MON-yyyy')),
    PARTITION sales_q4_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy')))
    ONLINE
  UPDATE INDEXES
   ( i1_cust_id_indx LOCAL, 
     i2_time_id_indx GLOBAL PARTITION BY RANGE (time_id) 
     (PARTITION ip1_indx VALUES LESS THAN (MAXVALUE) ) );

SELECT TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE FROM USER_PART_TABLES WHERE TABLE_NAME ='MOD_SALES_PARTITIONING';
TABLE_NAME                  PARTITION      SUBPARTIT
--------------------------- -------------- ----------
MOD_SALES_PARTITIONING      RANGE          HASH

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='MOD_SALES_PARTITIONING';
TABLE_NAME                  PARTITION_NAME     SUBPARTITION_NAME 
--------------------------- ------------------ ------------------
MOD_SALES_PARTITIONING      SALES_Q1_2017      SYS_SUBP567       
MOD_SALES_PARTITIONING      SALES_Q1_2017      SYS_SUBP568
MOD_SALES_PARTITIONING      SALES_Q1_2017      SYS_SUBP569
MOD_SALES_PARTITIONING      SALES_Q1_2017      SYS_SUBP570
...

4.4.10 About Moving Partitions and Subpartitions

Use the MOVE PARTITION clause of the ALTER TABLE statement to change the physical storage attributes of a partition.

With the MOVE PARTITION clause of the ALTER TABLE statement, you can:

Re-cluster data and reduce fragmentation
Move a partition to another tablespace
Modify create-time attributes
Store the data in compressed format using table compression

Typically, you can change the physical storage attributes of a partition in a single step using an ALTER TABLE/INDEX MODIFY PARTITION statement. However, there are some physical attributes, such as TABLESPACE, that you cannot modify using MODIFY PARTITION. In these cases, use the MOVE PARTITION clause. Modifying some other attributes, such as table compression, affects only future storage, but not existing data.

If the partition being moved contains any data, then indexes may be marked UNUSABLE according to the following table:

Table Type Index Behavior

Table Type	Index Behavior
Regular (Heap)	Unless you specify `UPDATE` `INDEXES` as part of the `ALTER` `TABLE` statement: The matching partition in each local index is marked `UNUSABLE`. You must rebuild these index partitions after issuing `MOVE` `PARTITION`. Any global indexes, or all partitions of partitioned global indexes, are marked `UNUSABLE`.
Index-organized	Any local or global indexes defined for the partition being moved remain usable because they are primary-key based logical rowids. However, the guess information for these rowids becomes incorrect.

Regular (Heap)

Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

The matching partition in each local index is marked UNUSABLE. You must rebuild these index partitions after issuing MOVE PARTITION.
Any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE.

Index-organized

Any local or global indexes defined for the partition being moved remain usable because they are primary-key based logical rowids. However, the guess information for these rowids becomes incorrect.

This section contains the following topics:

4.4.10.1 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 (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 are 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 materialized. 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 parent table.

4.4.10.2 Moving Subpartitions

Use the MOVE SUBPARTITION clause to move a subpartition.

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 parent table.

4.4.10.3 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.

4.4.11 About Rebuilding Index Partitions

Rebuilding an index provides several advantages.

Some reasons for rebuilding index partitions include:

To recover space and improve performance
To repair a damaged index partition caused by media failure
To rebuild a local index partition after loading the underlying table partition with SQL*Loader or an import utility
To rebuild index partitions that have been marked UNUSABLE
To enable key compression for B-tree indexes

The following sections discuss options for rebuilding index partitions and subpartitions.

This section contains the following topics:

4.4.11.1 About Rebuilding Global Index Partitions

You can rebuild global index partitions with several methods.

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 one time.

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.

4.4.11.2 About Rebuilding Local Index Partitions

You can rebuild local index partitions with several methods.

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.

The following sections contain examples about rebuilding indexes.

4.4.11.2.1 Using ALTER INDEX to Rebuild a Partition

The ALTER INDEX REBUILD PARTITION statement rebuilds one partition of an index.

It cannot be used for composite-partitioned tables. Only real physical segments can be rebuilt with this command. When you re-create the index, you can also choose to move the partition to a new tablespace or change attributes.

For composite-partitioned tables, use ALTER INDEX REBUILD SUBPARTITION to rebuild a subpartition of an index. You can move the subpartition to another tablespace or specify a parallel clause. The following statement rebuilds a subpartition of a local index on a table and moves the index subpartition to another tablespace.

ALTER INDEX scuba
   REBUILD SUBPARTITION bcd_types
   TABLESPACE tbs23 PARALLEL (DEGREE 2);

4.4.11.2.2 Using ALTER TABLE to Rebuild an Index Partition

The REBUILD UNUSABLE LOCAL INDEXES clause of ALTER TABLE MODIFY PARTITION enables you to rebuild an unusable index partition.

However, the statement does not allow you to specify any new attributes for the rebuilt index partition. The following example finds and rebuilds any unusable local index partitions for table scubagear, partition p1.

ALTER TABLE scubagear
   MODIFY PARTITION p1 REBUILD UNUSABLE LOCAL INDEXES;

The ALTER TABLE MODIFY SUBPARTITION is the clause for rebuilding unusable local index subpartitions.

4.4.12 About Renaming Partitions and Subpartitions

You can rename partitions and subpartitions of both tables and indexes.

One reason for renaming a partition might be to assign a meaningful name, as opposed to a default system name that was assigned to the partition in another maintenance operation.

All partitioning methods support the FOR(value) method to identify a partition. You can use this method to rename a system-generated partition name into a more meaningful name. This is particularly useful in interval or interval-* partitioned tables.

You can independently rename partitions and subpartitions for reference-partitioned parent and child tables. The rename operation on the parent table is not cascaded to descendant tables.

This section contains the following topics:

4.4.12.1 Renaming a Table Partition

You can 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;

4.4.12.2 Renaming a Table Subpartition

You can assign new names to subpartitions of a table.

In this case, you would use the ALTER TABLE RENAME SUBPARTITION syntax.

4.4.12.3 About Renaming Index Partitions

You can rename index partitions and subpartitions with the ALTER INDEX statement.

4.4.12.3.1 Renaming an Index Partition

Use the ALTER INDEX RENAME PARTITION statement to rename an index partition.

The ALTER INDEX statement does not support the use of FOR(value) to identify a partition. You must use the original partition name in the rename operation.

4.4.12.3.2 Renaming an Index Subpartition

Use the ALTER INDEX RENAME SUBPARTITION statement to rename an index subpartition.

The following statement simply shows how to rename a subpartition that has a system generated name that was a consequence of adding a partition to an underlying table:

ALTER INDEX scuba RENAME SUBPARTITION sys_subp3254 TO bcd_types;

4.4.13 About Splitting Partitions and Subpartitions

You can split the contents of a partition into two new partitions.

The SPLIT PARTITION clause of the ALTER TABLE or ALTER INDEX statement is used to redistribute the contents of a partition into two new partitions. Consider doing this when a partition becomes too large and causes backup, recovery, or maintenance operations to take a long time to complete or it is felt that there is simply too much data in the partition. You can also use the SPLIT PARTITION clause to redistribute the I/O load. This clause cannot be used for hash partitions or subpartitions.

If the partition you are splitting contains any data, then indexes may be marked UNUSABLE as explained in the following table:

Table Type Index Behavior

Table Type	Index Behavior
Regular (Heap)	Unless you specify `UPDATE` `INDEXES` as part of the `ALTER` `TABLE` statement: The database marks `UNUSABLE` the new partitions (there are two) in each local index. Any global indexes, or all partitions of partitioned global indexes, are marked `UNUSABLE` and must be rebuilt.
Index-organized	The database marks `UNUSABLE` the new partitions (there are two) in each local index. All global indexes remain usable.

Regular (Heap)

Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:

The database marks UNUSABLE the new partitions (there are two) in each local index.
Any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

Index-organized

The database marks UNUSABLE the new partitions (there are two) in each local index.
All global indexes remain usable.

You cannot split partitions or subpartitions in a reference-partitioned table except for the parent table. When you split partitions or subpartitions in the parent table then the split is cascaded to all descendant tables. However, you can use the DEPENDENT TABLES clause to set specific properties for dependent tables when you issue the SPLIT statement on the parent table to split partitions or subpartitions.

Partition maintenance with SPLIT operations are supported as online operations with the keyword ONLINE for heap organized tables, enabling concurrent DML operations while a partition maintenance operation is ongoing.

For ONLINE operations, split indexes are always updated by default, regardless whether you specify the UPDATE INDEXES clause.

For an example of the use of the keyword ONLINE with a SPLIT operation, see Example 4-37.

This section contains the following topics:

See Also:

Oracle Database SQL Language Reference

4.4.13.1 Splitting a Partition of a Range-Partitioned Table

You can split a range partition using the ALTER TABLE SPLIT PARTITION statement.

In the SQL statement, you must specify values of the partitioning key column within the range of the partition at which to split the partition.

You can optionally specify new attributes for the 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.

If you do not specify new partition names, then the database assigns names of the form SYS_Pn. You can examine the data dictionary to locate the names assigned to the new local index partitions. You may want to rename them. Any attributes that you do not specify are inherited from the original partition.

Example 4-36 Splitting a partition of a range-partitioned table and rebuilding indexes

In this 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. 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 following SQL statement split the partition fee_katy, and rebuild the index partitions.

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;

Example 4-37 Splitting a partition of a range-partitioned table online

In this example, the sales_q4_2016 partition of theORDERS table is split into separate partitions for each month. The ONLINE keyword is specified to enable concurrent DML operations while a partition maintenance operation is ongoing.

If there were any indexes on the ORDERS table, then those would be maintained automatically as part of the online split.

CREATE TABLE orders
 (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_2016 VALUES LESS THAN (TO_DATE('01-APR-2016','dd-MON-yyyy')),
   PARTITION sales_q2_2016 VALUES LESS THAN (TO_DATE('01-JUL-2016','dd-MON-yyyy')),
   PARTITION sales_q3_2016 VALUES LESS THAN (TO_DATE('01-OCT-2016','dd-MON-yyyy')),
   PARTITION sales_q4_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy')) 
   );

ALTER TABLE orders 
  SPLIT PARTITION sales_q4_2016 INTO
  (PARTITION sales_oct_2016 VALUES LESS THAN (TO_DATE('01-NOV-2016','dd-MON-yyyy')),
   PARTITION sales_nov_2016 VALUES LESS THAN (TO_DATE('01-DEC-2016','dd-MON-yyyy')),
   PARTITION sales_dec_2016
   )
  ONLINE;

4.4.13.2 Splitting a Partition of a List-Partitioned Table

You can split a list partition with 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 (INITIAL 8M)) 
   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 new 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.

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Splitting the DEFAULT Partition of a List-Partitioned Table.

Example 4-38 Splitting the default partition of a list-partitioned table

This example splits the default partition of sales_by_region, creating new partitions.

CREATE TABLE sales_by_region
     (dept_number         NUMBER NOT NULL,
      dept_name           VARCHAR2(20),
      quarterly_sales     NUMBER(10,2),
      state               VARCHAR2(2)
 )
 PARTITION BY LIST (state)
 (
  PARTITION yearly_north VALUES ('MN','WI','MI'),
  PARTITION yearly_south VALUES ('NM','TX','GA'),
  PARTITION yearly_east VALUES  ('MA','NY','NC'),
  PARTITION yearly_west VALUES  ('CA','OR','WA'),
  PARTITION unknown VALUES (DEFAULT)
 );

SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_BY_REGION';
PARTITION_NAME       HIGH_VALUE
--------------       ---------------
UNKNOWN              DEFAULT
YEARLY_EAST          'MA', 'NY', 'NC'
YEARLY_NORTH         'MN', 'WI', 'MI'
YEARLY_SOUTH         'NM', 'TX', 'GA'
YEARLY_WEST          'CA', 'OR', 'WA
5 rows selected.

INSERT INTO SALES_BY_REGION VALUES (002, 'AUTO NORTH', 450000, 'MN');
INSERT INTO SALES_BY_REGION VALUES (002, 'AUTO NORTH', 495000, 'WI');
INSERT INTO SALES_BY_REGION VALUES (002, 'AUTO NORTH', 850000, 'MI');

INSERT INTO SALES_BY_REGION VALUES (004, 'AUTO SOUTH',  595000, 'NM');
INSERT INTO SALES_BY_REGION VALUES (004, 'AUTO SOUTH', 4825000, 'TX');
INSERT INTO SALES_BY_REGION VALUES (004, 'AUTO SOUTH',  945000, 'GA');

INSERT INTO SALES_BY_REGION VALUES (006, 'AUTO EAST', 2125000, 'MA');
INSERT INTO SALES_BY_REGION VALUES (006, 'AUTO EAST', 6101000, 'NY');
INSERT INTO SALES_BY_REGION VALUES (006, 'AUTO EAST',  741000, 'NC');

INSERT INTO SALES_BY_REGION VALUES (008, 'AUTO WEST', 7201000, 'CA');
INSERT INTO SALES_BY_REGION VALUES (008, 'AUTO WEST',  901000, 'OR');
INSERT INTO SALES_BY_REGION VALUES (008, 'AUTO WEST', 1125000, 'WA');

INSERT INTO SALES_BY_REGION VALUES (009, 'AUTO MIDWEST', 1950000, 'AZ');
INSERT INTO SALES_BY_REGION VALUES (009, 'AUTO MIDWEST', 5725000, 'UT');

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(yearly_north);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST
----------- -------------------- --------------- --
2           AUTO NORTH                    450000 MN
2           AUTO NORTH                    495000 WI
2           AUTO NORTH                    850000 MI

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(yearly_south);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST
----------- -------------------- --------------- --
4           AUTO SOUTH                    595000 NM
4           AUTO SOUTH                   4825000 TX
4           AUTO SOUTH                    945000 GA

…

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(unknown);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST
----------- -------------------- --------------- --
9           AUTO MIDWEST                 1950000 AZ
9           AUTO MIDWEST                 5725000 UT

REM Note that the following ADD PARTITION statement fails. This action fails because 
REM all undefined values are automatically included in the DEFAULT partition.
ALTER TABLE sales_by_region ADD PARTITION yearly_midwest VALUES ('AZ', 'UT');
ORA-14323: cannot add partition when DEFAULT partition exists 

REM You must SPLIT the DEFAULT partition to add a new partition.
ALTER TABLE sales_by_region
   SPLIT PARTITION unknown VALUES ('AZ', 'UT')
   INTO
    ( PARTITION yearly_midwest,
      PARTITION unknown);

SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_BY_REGION';
PARTITION_NAME       HIGH_VALUE
--------------       ---------------
UNKNOWN              DEFAULT
YEARLY_EAST          'MA', 'NY', 'NC'
YEARLY_MIDWEST       'AZ', 'UT'
YEARLY_NORTH         'MN', 'WI', 'MI'
YEARLY_SOUTH         'NM', 'TX', 'GA'
YEARLY_WEST          'CA', 'OR', 'WA'
6 Rows selected.

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(yearly_midwest);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST
----------- -------------------- --------------- --
          9 AUTO MIDWEST                 1950000 AZ
          9 AUTO MIDWEST                 5725000 UT

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(unknown);
no rows selected

REM Split the DEFAULT partition again to add a new 'yearly_mideast' partition.
ALTER TABLE sales_by_region
   SPLIT PARTITION unknown VALUES ('OH', 'IL')
   INTO
    ( PARTITION yearly_mideast,
      PARTITION unknown);
Table altered.

SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_BY_REGION';
PARTITION_NAME      HIGH_VALUE
------------------  ------------------
UNKNOWN             DEFAULT
YEARLY_EAST         'MA', 'NY', 'NC'
YEARLY_MIDEAST      'OH', 'IL'
YEARLY_MIDWEST      'AZ', 'UT'
YEARLY_NORTH        'MN', 'WI', 'MI'
YEARLY_SOUTH        'NM', 'TX', 'GA'
YEARLY_WEST         'CA', 'OR', 'WA'
7 rows selected.

INSERT INTO SALES_BY_REGION VALUES (007, 'AUTO MIDEAST',  925000, 'OH');
INSERT INTO SALES_BY_REGION VALUES (007, 'AUTO MIDEAST', 1325000, 'IL');

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(yearly_mideast);
DEPT_NUMBER DEPT_NAME            QUARTERLY_SALES ST
----------- -------------------- --------------- --
          7 AUTO MIDEAST                  925000 OH
          7 AUTO MIDEAST                 1325000 IL

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE FROM SALES_BY_REGION PARTITION(unknown);
no rows selected

4.4.13.3 Splitting a Partition of an Interval-Partitioned Table

You can split a range or a materialized interval partition with the ALTER TABLE SPLIT PARTITION statement in an interval-partitioned table.

Splitting a range partition in the interval-partitioned table is 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 moves 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'));

4.4.13.4 Splitting a *-Hash Partition

You can split a hash partition with the ALTER TABLE SPLIT PARTITION statement.

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.

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 is changed to the higher boundary of the split partition.

4.4.13.5 Splitting Partitions in a *-List Partitioned Table

Partitions can be split at both the partition level and at the subpartition level in a list partitioned table..

4.4.13.5.1 Splitting a *-List Partition

You can split a list partition with the ALTER TABLE SPLIT PARTITION statement.

Splitting a partition of a *-list partitioned table is similar to the description in Splitting a Partition of a List-Partitioned Table. No subpartition literal value list 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 q1_1999 partition of the quarterly_regional_sales table:

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 (INITIAL 8M) TABLESPACE ts2) 
   PARALLEL 5;

This operation splits the partition q1_1999 into two resulting partitions: q1_1999_jan_feb and q1_1999_feb_mar. Both partitions inherit their subpartition descriptions from the original partition. The individual partitions have new physical attributes, including tablespaces, specified at the partition level. These new attributes become the default attributes of the new partitions. This operation is run with parallelism of degree 5.

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 resulting from the previous split partition operation on table quarterly_regional_sales. It also reflects the results of other operations performed on this table in preceding sections of this chapter since its creation in About Creating Composite Range-List Partitioned Tables.

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.

4.4.13.5.2 Splitting a *-List Subpartition

You can split a list subpartition with the ALTER TABLE SPLIT SUBPARTITION statement.

Splitting a list subpartition of a *-list partitioned table is similar to the description in Splitting a Partition of a List-Partitioned Table, but the syntax is that of SUBPARTITION rather than PARTITION. For example, the following statement splits a subpartition of the quarterly_regional_sales table:

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;

This operation splits the subpartition q2_1999_southwest into two subpartitions:

q2_1999_utah with literal value list of ('UT')
q2_1999_southwest which inherits the remaining literal value list of ('AZ','NM')

The individual subpartitions have new physical attributes that are inherited from the subpartition being split.

You can only split subpartitions in an interval-list partitioned table for range partitions or materialized interval partitions. To change subpartition values for future interval partitions, you must modify the subpartition template.

4.4.13.6 Splitting a *-Range Partition

You can split a range partition using the ALTER TABLE SPLIT PARTITION statement.

Splitting a partition of a *-range partitioned table is similar to the description 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

4.4.13.6.1 Splitting a *-Range Subpartition

You can split a range subpartition using the ALTER TABLE SPLIT SUBPARTITION statement.

Splitting a range subpartition of a *-range partitioned table is similar to the description in Splitting a Partition of a Range-Partitioned Table, but the syntax is that of SUBPARTITION rather than PARTITION. For example, the following statement splits a subpartition of the orders table:

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
     );

This operation splits the subpartition p_pre_may_2007_p_large into two subpartitions:

p_pre_may_2007_med_large with values between 10000 and 50000
p_pre_may_2007_large_large with values between 50000 and 100000

The individual subpartitions have new physical attributes that are inherited from the subpartition being split.

You can only split subpartitions in an interval-range partitioned table for range partitions or materialized interval partitions. To change subpartition boundaries for future interval partitions, you must modify the subpartition template.

4.4.13.7 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.

4.4.13.8 Splitting into Multiple Partitions

You can redistribute the contents of one partition or subpartition into multiple partitions or subpartitions with the SPLIT PARTITION and SPLIT SUBPARTITION clauses of the ALTER TABLE statement.

When splitting multiple partitions, the segment associated with the current partition is discarded. Each new partitions obtains a new segment and inherits all unspecified physical attributes from the current source partition. Fast split optimization is applied to multipartition split operations when required conditions are met.

You can use the extended split syntax to specify a list of new partition descriptions similar to the create partitioned table SQL statements, rather than specifying the AT or VALUES clause. Additionally, the range or list values clause for the last new partition description is derived based on the high bound of the source partition and the bound values specified for the first (N-1) new partitions resulting from the split.

The following SQL statements are examples of splitting a partition into multiple partitions.

ALTER TABLE SPLIT PARTITION p0 INTO 
  (PARTITION p01 VALUES LESS THAN (25),
   PARTITION p02 VALUES LESS THAN (50), 
   PARTITION p03 VALUES LESS THAN (75),
   PARTITION p04);

ALTER TABLE SPLIT PARTITION p0 INTO 
  (PARTITION p01 VALUES LESS THAN (25),
   PARTITION p02);

In the second SQL example, partition p02 has the high bound of the original partition p0.

To split a range partition into N partitions, (N-1) values of the partitioning key column must be specified within the range of the partition at which to split the partition. The new non-inclusive upper bound values specified must be in ascending order. The high bound of Nth new partition is assigned the value of the high bound of the partition being split. The names and physical attributes of the N new partitions resulting from the split can be optionally specified.

To split a list partition into N partitions, (N-1) lists of literal values must be specified, each of which defines the first (N-1) partitions into which rows with corresponding partitioning key values are inserted. The remaining rows of the original partition are inserted into the Nth new partition whose value list contains the remaining literal values from the original partition. No two value lists can contain the same partition value. The (N-1) value lists that are specified cannot contain all of the partition values of the current partition because the Nth new partition would be empty. Also, the (N-1) value lists cannot contain any partition values that do not exist for the current partition.

When splitting a DEFAULT list partition or a MAXVALUE range partition into multiple partitions, the first (N-1) new partitions are created using the literal value lists or high bound values specified, while the Nth new partition resulting from the split have the DEFAULT value or MAXVALUE. Splitting a partition of a composite partitioned table into multiple partitions assumes the existing behavior with respect to inheritance of the number, names, bounds and physical properties of the subpartitions of the new partitions resulting from the split. The SPLIT_TABLE_SUBPARTITION clause is extended similarly to allow split of a range or list subpartition into N new subpartitions.

The behavior of the SQL statement with respect to local and global indexes remains unchanged. Corresponding local index partition are split into multiple partitions. If the partitioned table contains LOB columns, then existing semantics for the SPLIT PARTITION clause apply with the extended syntax; that is, LOB data and index segments is dropped for current partition and new segments are created for each LOB column for each new partition. Fast split optimization is applied to multipartition split operations when required conditions are met.

For example, the following SQL statement splits the sales_Q4_2007 partition of the partitioned by range table sales splits into five partitions corresponding to the quarters of the next year. In this example, the partition sales_Q4_2008 implicitly becomes the high bound of the split partition.

ALTER TABLE sales SPLIT PARTITION sales_Q4_2007 INTO
( PARTITION sales_Q4_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy')),
  PARTITION sales_Q1_2008 VALUES LESS THAN (TO_DATE('01-APR-2008','dd-MON-yyyy')),
  PARTITION sales_Q2_2008 VALUES LESS THAN (TO_DATE('01-JUL-2008','dd-MON-yyyy')),
  PARTITION sales_Q3_2008 VALUES LESS THAN (TO_DATE('01-OCT-2008','dd-MON-yyyy')),
  PARTITION sales_Q4_2008);

For the sample table customers partitioned by list, the following statement splits the partition Europe into three partitions.

ALTER TABLE list_customers SPLIT PARTITION Europe INTO
  (PARTITION western-europe VALUES ('GERMANY', 'FRANCE'),
   PARTITION southern-europe VALUES ('ITALY'), 
   PARTITION rest-europe);

4.4.13.9 Fast SPLIT PARTITION and SPLIT SUBPARTITION Operations

Oracle Database implements a SPLIT PARTITION operation by creating two or more new partitions and redistributing the rows from the partition being split into the new partitions.

This is a time-consuming 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, then both local and global indexes also require rebuilding.

Sometimes after a split operation, one new partition contains all of the rows from the partition being split, while the other partitions contain no rows. This is often the case when splitting the first or last 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 resulting partitions contains all of the rows.
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 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 the resulting partitions remain usable if they were usable before the split. Local index (sub)partitions corresponding to the non-empty resulting partition are identical to the local index (sub)partitions of the partition that was split. The same optimization holds for SPLIT SUBPARTITION operations.

4.4.14 About Truncating Partitions and Subpartitions

Truncating a partition is similar to dropping a partition, except that the partition is emptied of its data, but not physically dropped.

Use the ALTER TABLE TRUNCATE PARTITION statement to remove all rows from a table partition. You cannot truncate an index partition. However, if local indexes are defined for the table, the ALTER TABLE TRUNCATE PARTITION statement truncates the matching partition in each local index. Unless you specify UPDATE INDEXES, any global indexes are marked UNUSABLE and must be rebuilt. You cannot use UPDATE INDEXES for index-organized tables. Use UPDATE GLOBAL INDEXES instead.

This section contains the following topics:

See Also:

Asynchronous Global Index Maintenance for Dropping and Truncating Partitions for information about asynchronous index maintenance for truncating partitions
About Dropping Partitions and Subpartitions for information about dropping a partition

4.4.14.1 About 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.

4.4.14.1.1 Truncating Table Partitions Containing Data and Global Indexes

When truncating a table partition that contains data and global indexes, you can use one of several methods.

If the partition contains data and global indexes, use one of the following methods (method 1, 2, or 3) to truncate the table partition.

Method 1

Leave the global indexes in place during the ALTER TABLE TRUNCATE PARTITION statement. In this example, table sales has a global index sales_area_ix, which is rebuilt.

ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table.

Method 2

Run the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE TRUNCATE PARTITION statement. The DELETE statement updates the global indexes, and also fires triggers and generates redo and undo logs.

For example, to truncate the first partition, run the following statements:

DELETE FROM sales PARTITION (dec98);
ALTER TABLE sales TRUNCATE PARTITION dec98;

This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.

Method 3

Specify UPDATE INDEXES in the ALTER TABLE statement. This causes the global index to be truncated at the time the partition is truncated.

ALTER TABLE sales TRUNCATE PARTITION dec98
     UPDATE INDEXES;

With asynchronous global index maintenance, this operation is a metadata-only operation.

4.4.14.1.2 Truncating a Partition Containing Data and Referential Integrity Constraints

If a partition contains data and has referential integrity constraints, then you cannot truncate the partition. However, if no other data is referencing any data in the partition to remove, then you can use one of several methods.

Choose either of the following methods (method 1 or 2) to truncate the table partition.

Method 1

Disable the integrity constraints, run the ALTER TABLE TRUNCATE PARTITION statement, then re-enable the integrity constraints. This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table. If there is still referencing data in other tables, then you must remove that data to be able to re-enable the integrity constraints.

Method 2

Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE TRUNCATE PARTITION statement. The DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo logs. Data in referencing tables is deleted if the foreign key constraints were created with the ON DELETE CASCADE option.

DELETE FROM sales partition (dec94);
ALTER TABLE sales TRUNCATE PARTITION dec94;

This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.

4.4.14.2 Truncating Multiple Partitions

You can truncate multiple partitions from a range or list partitioned table with the TRUNCATE PARTITION clause of the ALTER TABLE statement.

The corresponding partitions of local indexes are truncated in the operation. Global indexes must be rebuilt unless UPDATE INDEXES is specified.

In the following example, the ALTER TABLE SQL statement truncates multiple partitions in a table. Note that the data is truncated, but the partitions are not dropped.

Live SQL:

View and run a related example on Oracle Live SQL at Oracle Live SQL: Truncating a Range-Partitioned Table.

Example 4-39 Truncating Multiple Partitions

CREATE TABLE sales_partition_truncate
  ( product_id       NUMBER(6) NOT NULL,
    customer_id      NUMBER    NOT NULL,
    channel_id       CHAR(1),
    promo_id         NUMBER(6),
    sales_date       DATE,
    quantity_sold    INTEGER,
    amount_sold      NUMBER(10,2)
  )
 PARTITION BY RANGE (sales_date) 
 SUBPARTITION BY LIST (channel_id)
(  PARTITION q3_2018 VALUES LESS THAN (TO_DATE('1-OCT-2018','DD-MON-YYYY'))
   ( SUBPARTITION q3_2018_p_catalog VALUES ('C'),
     SUBPARTITION q3_2018_p_internet VALUES ('I'),
     SUBPARTITION q3_2018_p_partners VALUES ('P'),
     SUBPARTITION q3_2018_p_direct_sales VALUES ('S'),
     SUBPARTITION q3_2018_p_tele_sales VALUES ('T')
   ),
  PARTITION q4_2018 VALUES LESS THAN (TO_DATE('1-JAN-2019','DD-MON-YYYY'))
   ( SUBPARTITION q4_2018_p_catalog VALUES ('C'),
     SUBPARTITION q4_2018_p_internet VALUES ('I'),
     SUBPARTITION q4_2018_p_partners VALUES ('P'),
     SUBPARTITION q4_2018_p_direct_sales VALUES ('S'),
     SUBPARTITION q4_2018_p_tele_sales VALUES ('T')
   ),
  PARTITION q1_2019 VALUES LESS THAN (TO_DATE('1-APR-2019','DD-MON-YYYY'))
   ( SUBPARTITION q1_2019_p_catalog VALUES ('C')
   , SUBPARTITION q1_2019_p_internet VALUES ('I')
   , SUBPARTITION q1_2019_p_partners VALUES ('P')
   , SUBPARTITION q1_2019_p_direct_sales VALUES ('S')
   , SUBPARTITION q1_2019_p_tele_sales VALUES ('T')
   ),
  PARTITION q2_2019 VALUES LESS THAN (TO_DATE('1-JUL-2019','DD-MON-YYYY'))
   ( SUBPARTITION q2_2019_p_catalog VALUES ('C'),
     SUBPARTITION q2_2019_p_internet VALUES ('I'),
     SUBPARTITION q2_2019_p_partners VALUES ('P'),
     SUBPARTITION q2_2019_p_direct_sales VALUES ('S'),
     SUBPARTITION q2_2019_p_tele_sales VALUES ('T')
   ),
  PARTITION q3_2019 VALUES LESS THAN (TO_DATE('1-OCT-2019','DD-MON-YYYY'))
   ( SUBPARTITION q3_2019_p_catalog VALUES ('C'),
     SUBPARTITION q3_2019_p_internet VALUES ('I'),
     SUBPARTITION q3_2019_p_partners VALUES ('P'),
     SUBPARTITION q3_2019_p_direct_sales VALUES ('S'),
     SUBPARTITION q3_2019_p_tele_sales VALUES ('T')
   ),
  PARTITION q4_2019 VALUES LESS THAN (TO_DATE('1-JAN-2020','DD-MON-YYYY'))
   ( SUBPARTITION q4_2019_p_catalog VALUES ('C'),
     SUBPARTITION q4_2019_p_internet VALUES ('I'),
     SUBPARTITION q4_2019_p_partners VALUES ('P'),
     SUBPARTITION q4_2019_p_direct_sales VALUES ('S'),
     SUBPARTITION q4_2019_p_tele_sales VALUES ('T')
   )
  );

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
       WHERE TABLE_NAME ='SALES_PARTITION_TRUNCATE';
TABLE_NAME                  PARTITION_NAME    SUBPARTITION_NAME
–------------------------   –--------------   –------------------
SALES_PARTITION_TRUNCATE    Q1_2019           Q1_2019_P_CATALOG
SALES_PARTITION_TRUNCATE    Q1_2019           Q1_2019_P_DIRECT_SALES
...
30 rows selected.

INSERT INTO sales_partition_truncate VALUES (1001,100,'C',150,'10-SEP-2018',500,2000);
INSERT INTO sales_partition_truncate VALUES (1021,200,'C',160,'16-NOV-2018',100,1500);
INSERT INTO sales_partition_truncate VALUES (1001,100,'C',150,'10-FEB-2019',500,2000);
INSERT INTO sales_partition_truncate VALUES (1021,200,'S',160,'16-FEB-2019',100,1500);
INSERT INTO sales_partition_truncate VALUES (1002,110,'I',180,'15-JUN-2019',100,1000);
INSERT INTO sales_partition_truncate VALUES (5010,150,'P',200,'20-AUG-2019',1000,10000);
INSERT INTO sales_partition_truncate VALUES (1001,100,'T',150,'12-OCT-2019',500,2000);

SELECT * FROM sales_partition_truncate;
PRODUCT_ID CUSTOMER_ID C   PROMO_ID SALES_DAT QUANTITY_SOLD AMOUNT_SOLD
---------- ----------- - ---------- --------- ------------- -----------
      1001         100 C        150 10-SEP-18           500        2000
      1021         200 C        160 16-NOV-18           100        1500
      1001         100 C        150 10-FEB-19           500        2000
      1021         200 S        160 16-FEB-19           100        1500
      1002         110 I        180 15-JUN-19           100        1000
      5010         150 P        200 20-AUG-19          1000       10000
      1001         100 T        150 12-OCT-19           500        2000
7 rows selected.

ALTER TABLE sales_partition_truncate
   TRUNCATE PARTITIONS q3_2018, q4_2018;

SELECT * FROM sales_partition_truncate;
PRODUCT_ID CUSTOMER_ID C   PROMO_ID SALES_DAT QUANTITY_SOLD AMOUNT_SOLD
---------- ----------- - ---------- --------- ------------- -----------
      1001         100 C        150 10-FEB-19           500        2000
      1021         200 S        160 16-FEB-19           100        1500
      1002         110 I        180 15-JUN-19           100        1000
      5010         150 P        200 20-AUG-19          1000       10000
      1001         100 T        150 12-OCT-19           500        2000
5 rows selected.

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
       WHERE TABLE_NAME ='SALES_PARTITION_TRUNCATE';
TABLE_NAME                  PARTITION_NAME    SUBPARTITION_NAME
–------------------------   –--------------   –------------------
SALES_PARTITION_TRUNCATE    Q1_2019           Q1_2019_P_CATALOG
SALES_PARTITION_TRUNCATE    Q1_2019           Q1_2019_P_DIRECT_SALES
...
SALES_PARTITION_TRUNCATE    Q3_2018           Q3_2018_P_CATALOG
SALES_PARTITION_TRUNCATE    Q3_2018           Q3_2018_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE    Q3_2018           Q3_2018_P_INTERNET
SALES_PARTITION_TRUNCATE    Q3_2018           Q3_2018_P_PARTNERS
SALES_PARTITION_TRUNCATE    Q3_2018           Q3_2018_P_TELE_SALES
...
SALES_PARTITION_TRUNCATE    Q4_2018           Q4_2018_P_CATALOG
SALES_PARTITION_TRUNCATE    Q4_2018           Q4_2018_P_DIRECT_SALES
SALES_PARTITION_TRUNCATE    Q4_2018           Q4_2018_P_INTERNET
SALES_PARTITION_TRUNCATE    Q4_2018           Q4_2018_P_PARTNERS
SALES_PARTITION_TRUNCATE    Q4_2018           Q4_2018_P_TELE_SALES
...
30 rows selected.

Note:

If a table has a local domain index, truncating a single partition or the entire table is supported. Truncating multiple partitions in a single ALTER TABLE statement is not permitted if a local index is present.

4.4.14.3 Truncating Subpartitions

Use the ALTER TABLE TRUNCATE SUBPARTITION statement to remove all rows from a subpartition of a composite partitioned table.

When truncating a subpartition, corresponding local index subpartitions are also truncated.

In the following example, the ALTER TABLE statement truncates data in subpartitions 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 with the DROP STORAGE clause. Note that the data is truncated, but the subpartitions are not dropped.

Example 4-40 Truncating Multiple Subpartitions

CREATE TABLE sales_partition_truncate
  ( product_id       NUMBER(6) NOT NULL,
    customer_id      NUMBER    NOT NULL,
    channel_id       CHAR(1),
    promo_id         NUMBER(6),
    sales_date       DATE,
    quantity_sold    INTEGER,
    amount_sold      NUMBER(10,2)
  )
 PARTITION BY RANGE (sales_date) 
 SUBPARTITION BY LIST (channel_id)
(  PARTITION q3_2018 VALUES LESS THAN (TO_DATE('1-OCT-2018','DD-MON-YYYY'))
   ( SUBPARTITION q3_2018_p_catalog VALUES ('C'),
     SUBPARTITION q3_2018_p_internet VALUES ('I'),
     SUBPARTITION q3_2018_p_partners VALUES ('P'),
     SUBPARTITION q3_2018_p_direct_sales VALUES ('S'),
     SUBPARTITION q3_2018_p_tele_sales VALUES ('T')
   ),
  PARTITION q4_2018 VALUES LESS THAN (TO_DATE('1-JAN-2019','DD-MON-YYYY'))
   ( SUBPARTITION q4_2018_p_catalog VALUES ('C'),
     SUBPARTITION q4_2018_p_internet VALUES ('I'),
     SUBPARTITION q4_2018_p_partners VALUES ('P'),
     SUBPARTITION q4_2018_p_direct_sales VALUES ('S'),
     SUBPARTITION q4_2018_p_tele_sales VALUES ('T')
   ),
  PARTITION q1_2019 VALUES LESS THAN (TO_DATE('1-APR-2019','DD-MON-YYYY'))
   ( SUBPARTITION q1_2019_p_catalog VALUES ('C')
   , SUBPARTITION q1_2019_p_internet VALUES ('I')
   , SUBPARTITION q1_2019_p_partners VALUES ('P')
   , SUBPARTITION q1_2019_p_direct_sales VALUES ('S')
   , SUBPARTITION q1_2019_p_tele_sales VALUES ('T')
   ),
  PARTITION q2_2019 VALUES LESS THAN (TO_DATE('1-JUL-2019','DD-MON-YYYY'))
   ( SUBPARTITION q2_2019_p_catalog VALUES ('C'),
     SUBPARTITION q2_2019_p_internet VALUES ('I'),
     SUBPARTITION q2_2019_p_partners VALUES ('P'),
     SUBPARTITION q2_2019_p_direct_sales VALUES ('S'),
     SUBPARTITION q2_2019_p_tele_sales VALUES ('T')
   ),
  PARTITION q3_2019 VALUES LESS THAN (TO_DATE('1-OCT-2019','DD-MON-YYYY'))
   ( SUBPARTITION q3_2019_p_catalog VALUES ('C'),
     SUBPARTITION q3_2019_p_internet VALUES ('I'),
     SUBPARTITION q3_2019_p_partners VALUES ('P'),
     SUBPARTITION q3_2019_p_direct_sales VALUES ('S'),
     SUBPARTITION q3_2019_p_tele_sales VALUES ('T')
   ),
  PARTITION q4_2019 VALUES LESS THAN (TO_DATE('1-JAN-2020','DD-MON-YYYY'))
   ( SUBPARTITION q4_2019_p_catalog VALUES ('C'),
     SUBPARTITION q4_2019_p_internet VALUES ('I'),
     SUBPARTITION q4_2019_p_partners VALUES ('P'),
     SUBPARTITION q4_2019_p_direct_sales VALUES ('S'),
     SUBPARTITION q4_2019_p_tele_sales VALUES ('T')
   )
  );

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
       WHERE TABLE_NAME ='SALES_PARTITION_TRUNCATE';
TABLE_NAME                  PARTITION_NAME    SUBPARTITION_NAME
–------------------------   –--------------   –------------------
SALES_PARTITION_TRUNCATE    Q1_2019           Q1_2019_P_CATALOG
SALES_PARTITION_TRUNCATE    Q1_2019           Q1_2019_P_DIRECT_SALES
...
30 rows selected.

INSERT INTO sales_partition_truncate VALUES (1001,100,'C',150,'10-SEP-2018',500,2000);
INSERT INTO sales_partition_truncate VALUES (1021,200,'C',160,'16-NOV-2018',100,1500);
INSERT INTO sales_partition_truncate VALUES (1001,100,'C',150,'10-FEB-2019',500,2000);
INSERT INTO sales_partition_truncate VALUES (1021,200,'S',160,'16-FEB-2019',100,1500);
INSERT INTO sales_partition_truncate VALUES (1002,110,'I',180,'15-JUN-2019',100,1000);
INSERT INTO sales_partition_truncate VALUES (5010,150,'P',200,'20-AUG-2019',1000,10000);
INSERT INTO sales_partition_truncate VALUES (1001,100,'T',150,'12-OCT-2019',500,2000);

SELECT * FROM sales_partition_truncate;
PRODUCT_ID CUSTOMER_ID C   PROMO_ID SALES_DAT QUANTITY_SOLD AMOUNT_SOLD
---------- ----------- - ---------- --------- ------------- -----------
      1001         100 C        150 10-SEP-18           500        2000
      1021         200 C        160 16-NOV-18           100        1500
      1001         100 C        150 10-FEB-19           500        2000
      1021         200 S        160 16-FEB-19           100        1500
      1002         110 I        180 15-JUN-19           100        1000
      5010         150 P        200 20-AUG-19          1000       10000
      1001         100 T        150 12-OCT-19           500        2000
7 rows selected.

ALTER TABLE sales_subpartition_truncate
   TRUNCATE SUBPARTITIONS q3_2018_p_catalog, q4_2018_p_catalog, q1_2019_p_catalog, 
                          q2_2019_p_catalog, q3_2019_p_catalog, q4_2019_p_catalog
        DROP STORAGE;

SELECT * FROM sales_partition_truncate;
PRODUCT_ID CUSTOMER_ID C   PROMO_ID SALES_DAT QUANTITY_SOLD AMOUNT_SOLD
---------- ----------- - ---------- --------- ------------- -----------
      1021         200 S        160 16-FEB-19           100        1500
      1002         110 I        180 15-JUN-19           100        1000
      5010         150 P        200 20-AUG-19          1000       10000
      1001         100 T        150 12-OCT-19           500        2000
4 rows selected.

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
       WHERE TABLE_NAME ='SALES_PARTITION_TRUNCATE';
TABLE_NAME                  PARTITION_NAME    SUBPARTITION_NAME
–------------------------   –--------------   –------------------
SALES_PARTITION_TRUNCATE    Q1_2019           Q1_2019_P_CATALOG
SALES_PARTITION_TRUNCATE    Q1_2019           Q1_2019_P_DIRECT_SALES
...
30 rows selected.

4.4.14.4 Truncating a Partition with the Cascade Option

You can use cascade truncate operations to a reference partitioned child table with the CASCADE option of TRUNCATE TABLE, ALTER TABLE TRUNCATE PARTITION, and ALTER TABLE TRUNCATE SUBPARTITION SQL statements.

When the CASCADE option is specified for TRUNCATE TABLE, the truncate table operation also truncates child tables that reference the targeted table through an enabled referential constraint that has ON DELETE CASCADE enabled. This cascading action applies recursively to grandchildren, great-grandchildren, and so on. After determining the set of tables to be truncated based on the enabled ON DELETE CASCADE referential constraints, an error is raised if any table in this set is referenced through an enabled constraint from a child outside of the set. If a parent and child are connected by multiple referential constraints, a TRUNCATE TABLE CASCADE operation targeting the parent succeeds if at least one constraint has ON DELETE CASCADE enabled.

Privileges are required on all tables affected by the operation. Any other options specified for the operation, such as DROP STORAGE or PURGE MATERIALIZED VIEW LOG, apply for all tables affected by the operation.

When the CASCADE option is specified, the TRUNCATE PARTITION and TRUNCATE SUBPARTITION operations cascade to reference partitioned tables that are children of the targeted table. The TRUNCATE can be targeted at any level in a reference partitioned hierarchy and cascades to child tables starting from the targeted table. Privileges are not required on the child tables, but the usual restrictions on the TRUNCATE operation, such as the table cannot be referenced by an enabled referential constraint that is not a partitioning constraint, apply for all tables affected by the operation.

The CASCADE option is ignored if it is specified for a table that does not have reference partitioned children. Any other options specified for the operation, such as DROP STORAGE or UPDATE INDEXES, apply to all tables affected by the operation.

The cascade options are off by default so they do not affect Oracle Database compatibility.

ALTER TABLE sales 
     TRUNCATE PARTITION dec2016
     DROP STORAGE
     CASCADE
     UPDATE INDEXES;

4.5 About Dropping Partitioned Tables

Dropping partitioned tables is similar to dropping nonpartitioned tables.

Oracle Database processes a DROP TABLE statement for a partitioned table in the same way that it processes the statement for a nonpartitioned table. One exception is when you use the PURGE keyword.

To avoid running into resource constraints, the DROP TABLE...PURGE statement for a partitioned table drops the table in multiple transactions, where each transaction drops a subset of the partitions or subpartitions and then commits. The table is dropped at the conclusion of the final transaction.

This behavior comes with some changes to the DROP TABLE statement. First, if the DROP TABLE...PURGE statement fails, then you can take corrective action, if any, and then reissue the statement. The statement resumes at the point where it failed. Second, while the DROP TABLE...PURGE statement is in progress, the table is marked as unusable by setting the STATUS column to the value UNUSABLE in the following data dictionary views:

USER_TABLES, ALL_TABLES, DBA_TABLES
USER_PART_TABLES, ALL_PART_TABLES, DBA_PART_TABLES
USER_OBJECT_TABLES, ALL_OBJECT_TABLES, DBA_OBJECT_TABLES

You can list all UNUSABLE partitioned tables by querying the STATUS column of these views.

Queries against other data dictionary views pertaining to partitioning, such as DBA_TAB_PARTITIONS and DBA_TAB_SUBPARTITIONS, exclude rows belonging to an UNUSABLE table.

After a table is marked UNUSABLE, the only statement that can be issued against it is another DROP TABLE...PURGE statement, and only if the previous DROP TABLE...PURGE statement failed. Any other statement issued against an UNUSABLE table results in an error. The table remains in the UNUSABLE state until the drop operation is complete.

See Also:

Viewing Information About Partitioned Tables and Indexes for a list of these views that contain information related to partitioning
Oracle Database SQL Language Reference for the syntax of the DROP TABLE statement
Oracle Database Reference for a description of the data dictionary views mentioned in this section

4.6 Changing a Nonpartitioned Table into a Partitioned Table

You can change a nonpartitioned table into a partitioned table.

The following topics are discussed:

See Also:

Oracle Database Administrator’s Guide for information about redefining partitions of a table

4.6.1 Using Online Redefinition to Partition Collection Tables

Oracle Database provides a mechanism to move one or more partitions or to make other changes to the partitions' physical structures without significantly affecting the availability of the partitions for DML. This mechanism is called online table redefinition.

You can use online redefinition to copy nonpartitioned Collection Tables to partitioned Collection Tables and Oracle Database inserts rows into the appropriate partitions in the Collection Table. Example 4-41 illustrates how this is done for nested tables inside an Objects column; a similar example works for Ordered Collection Type Tables inside an XMLType table or column. During the copy_table_dependents operation, you specify 0 or false for copying the indexes and constraints, because you want to keep the indexes and constraints of the newly defined collection table. However, the Collection Tables and its partitions have the same names as that of the interim table (print_media2 in Example 4-41). You must take explicit steps to preserve the Collection Table names.

Example 4-41 Redefining partitions with collection tables

REM Connect as a user with appropriate privileges, then run the following
DROP USER eqnt CASCADE;
CREATE USER eqnt IDENTIFIED BY eqnt;
GRANT CONNECT, RESOURCE TO eqnt;
 
-- Grant privleges required for online redefinition.
GRANT EXECUTE ON DBMS_REDEFINITION TO eqnt;
GRANT ALTER ANY TABLE TO eqnt;
GRANT DROP ANY TABLE TO eqnt;
GRANT LOCK ANY TABLE TO eqnt;
GRANT CREATE ANY TABLE TO eqnt;
GRANT SELECT ANY TABLE TO eqnt;
 
-- Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO eqnt;
GRANT CREATE ANY INDEX TO eqnt;
 
CONNECT eqnt/eqnt
 
CREATE TYPE textdoc_typ AS OBJECT ( document_typ VARCHAR2(32));
/
CREATE TYPE textdoc_tab AS TABLE OF textdoc_typ;
/

-- (old) non partitioned nested table
CREATE TABLE print_media
    ( product_id        NUMBER(6) primary key
    , ad_textdocs_ntab  textdoc_tab
    )
NESTED TABLE ad_textdocs_ntab STORE AS equi_nestedtab
(   (document_typ NOT NULL)
    STORAGE (INITIAL 8M)
)

;
 
-- Insert into base table
INSERT INTO print_media VALUES (1,
   textdoc_tab(textdoc_typ('xx'), textdoc_typ('yy')));
INSERT INTO print_media VALUES (11,
   textdoc_tab(textdoc_typ('aa'), textdoc_typ('bb')));
COMMIT;
 
-- Insert into nested table
INSERT INTO TABLE
  (SELECT p.ad_textdocs_ntab FROM print_media p WHERE p.product_id = 11)
   VALUES ('cc');
 
SELECT * FROM print_media;

PRODUCT_ID   AD_TEXTDOCS_NTAB(DOCUMENT_TYP)
----------   ------------------------------
         1   TEXTDOC_TAB(TEXTDOC_TYP('xx'), TEXTDOC_TYP('yy'))
        11   TEXTDOC_TAB(TEXTDOC_TYP('aa'), TEXTDOC_TYP('bb'), TEXTDOC_TYP('cc'))
 
-- Creating partitioned Interim Table
CREATE TABLE print_media2
    ( product_id        NUMBER(6)
    , ad_textdocs_ntab  textdoc_tab
    )
NESTED TABLE ad_textdocs_ntab STORE AS equi_nestedtab2
(   (document_typ NOT NULL)
    STORAGE (INITIAL 8M)
)
PARTITION BY RANGE (product_id)
(
    PARTITION P1 VALUES LESS THAN (10),
    PARTITION P2 VALUES LESS THAN (20)
);
 
EXEC dbms_redefinition.start_redef_table('eqnt', 'print_media', 'print_media2');
 
DECLARE
 error_count pls_integer := 0;
BEGIN
  dbms_redefinition.copy_table_dependents('eqnt', 'print_media', 'print_media2',
                                          0, true, false, true, false,
                                          error_count);
 
  dbms_output.put_line('errors := ' || to_char(error_count));
END;
/
 
EXEC  dbms_redefinition.finish_redef_table('eqnt', 'print_media', 'print_media2');
 
-- Drop the interim table
DROP TABLE print_media2;
 
-- print_media has partitioned nested table here

SELECT * FROM print_media PARTITION (p1);

PRODUCT_ID   AD_TEXTDOCS_NTAB(DOCUMENT_TYP)
----------   ------------------------------
         1   TEXTDOC_TAB(TEXTDOC_TYP('xx'), TEXTDOC_TYP('yy'))

SELECT * FROM print_media PARTITION (p2);

PRODUCT_ID   AD_TEXTDOCS_NTAB(DOCUMENT_TYP)
----------   ------------------------------
        11   TEXTDOC_TAB(TEXTDOC_TYP('aa'), TEXTDOC_TYP('bb'), TEXTDOC_TYP('cc'))

4.6.2 Converting a Non-Partitioned Table to a Partitioned Table

A non-partitioned table can be converted to a partitioned table with a MODIFY clause added to the ALTER TABLE SQL statement.

In addition, the keyword ONLINE can be specified, enabling concurrent DML operations while the conversion is ongoing.

The following is an example of the ALTER TABLE statement using the ONLINE keyword for an online conversion to a partitioned table.

Example 4-42 Using the MODIFY clause of ALTER TABLE to convert online to a partitioned table

ALTER TABLE employees_convert MODIFY
  PARTITION BY RANGE (employee_id) INTERVAL (100)
  ( PARTITION P1 VALUES LESS THAN (100),
    PARTITION P2 VALUES LESS THAN (500)
   ) ONLINE
  UPDATE INDEXES
 ( IDX1_SALARY LOCAL,
   IDX2_EMP_ID GLOBAL PARTITION BY RANGE (employee_id)
  ( PARTITION IP1 VALUES LESS THAN (MAXVALUE))
 );

Considerations When Using the UPDATE INDEXES Clause

When using the UPDATE INDEXES clause, note the following.

This clause can be used to change the partitioning state of indexes and storage properties of the indexes being converted.
The specification of the UPDATE INDEXES clause is optional.

Indexes are maintained both for the online and offline conversion to a partitioned table.
This clause cannot change the columns on which the original list of indexes are defined.
This clause cannot change the uniqueness property of the index or any other index property.
If you do not specify the tablespace for any of the indexes, then the following tablespace defaults apply.
- Local indexes after the conversion collocate with the table partition.
- Global indexes after the conversion reside in the same tablespace of the original global index on the non-partitioned table.
If you do not specify the INDEXES clause or the INDEXES clause does not specify all the indexes on the original non-partitioned table, then the following default behavior applies for all unspecified indexes.
- Global partitioned indexes remain the same and retain the original partitioning shape.
- Non-prefixed indexes become global nonpartitioned indexes.
- Prefixed indexes are converted to local partitioned indexes.
  
  Prefixed means that the partition key columns are included in the index definition, but the index definition is not limited to including the partitioning keys only.
- Bitmap indexes become local partitioned indexes, regardless whether they are prefixed or not.
  
  Bitmap indexes must always be local partitioned indexes.
The conversion operation cannot be performed if there are domain indexes.

4.7 Managing Hybrid Partitioned Tables

The following topics are discussed in this section:

See Also:

Hybrid Partitioned Tables for an overview of hybrid partitioned tables, including information about limitations

4.7.1 Creating Hybrid Partitioned Tables

You can use the EXTERNAL PARTITION ATTRIBUTES clause of the CREATE TABLE statement to determine hybrid partitioning for a table. The partitions of the table can be external and or internal.

Note:

As of Oracle Database 23ai, single-level partitioning with interval, autolist, range and list partitioning are supported for hybrid partitioned tables.

A hybrid partitioned table enables partitions to reside both in database data files (internal partitions) and in external files and sources (external partitions). You can create and query a hybrid partitioned table to utilize the benefits of partitioning with classic partitioned tables, such as pruning, on data that is contained in both internal and external partitions.

The EXTERNAL PARTITION ATTRIBUTES clause of the CREATE TABLE statement is defined at the table level for specifying table level external parameters in the hybrid partitioned table, such as:

The access driver type, such as ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, ORACLE_HIVE, ORACLE_BIGDATA.

Note:
ORACLE_HIVE does not support interval and autolist partitioning.
The default directory for all external partitions files
The access parameters

The EXTERNAL clause of the PARTITION clause defines the partition as an external partition. When there is no EXTERNAL clause, the partition is an internal partition. You can specify a default location for each external partition different then the default directory defined at the table level. For example in Creating a Hybrid Range-Partitioned Table, the DEFAULT DIRECTORY value for partitions sales_data2, sales_data3, and sales_data_acfs is different than the DEFAULT DIRECTORY value defined in the EXTERNAL PARTITION ATTRIBUTES clause.

When there is no external file defined for an external partition, the external partition is empty. It can be populated with an external file by using an ALTER TABLE MODIFY PARTITION statement. Note that at least one partition must be an internal partition.

See Also:

Hybrid Partitioned Tables

4.7.1.1 Creating a Hybrid Range-Partitioned Table

In the first example below, a hybrid range-partitioned table is a created with four external partitions and two internal partitions. The external CSV data files are stored in the sales_data , sales_data2, sales_data3, and sales_data_acfs directories defined by the DEFAULT DIRECTORY clauses. sales_data is defined as the overall DEFAULT DIRECTORY in the EXTERNAL PARTITION ATTRIBUTES clause. The other directories are defined at the partition level. sales_2014 and sales_2015 are internal partitions. Data directory sales_data_acfs is stored on an Oracle ACFS file system to illustrate the use of that storage option.

In the second example, an additional external partition is added to the table.

Example 4-43 Creating a Hybrid Range-Partitioned Table

REM Connect as a user with appropriate privileges, 
REM then run the following to set up data directories that contain the data files
CREATE DIRECTORY sales_data AS '/u01/my_data/sales_data1';
GRANT READ,WRITE ON DIRECTORY sales_data TO hr;

CREATE DIRECTORY sales_data2 AS '/u01/my_data/sales_data2';
GRANT READ,WRITE ON DIRECTORY sales_data2 TO hr;

CREATE DIRECTORY sales_data3 AS '/u01/my_data/sales_data3';
GRANT READ,WRITE ON DIRECTORY sales_data3 TO hr;

REM set up a data directory on an Oracle ACFS mount point (file system)
CREATE DIRECTORY sales_data_acfs AS '/u01/acfsmounts/acfs1';
GRANT READ,WRITE ON DIRECTORY sales_data_acfs TO hr;

CONNECT AS hr, run the following
CREATE TABLE hybrid_partition_table
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    EXTERNAL PARTITION ATTRIBUTES (
      TYPE ORACLE_LOADER 
      DEFAULT DIRECTORY sales_data
       ACCESS PARAMETERS(
         FIELDS TERMINATED BY ','
         (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
       ) 
      REJECT LIMIT UNLIMITED
     ) 
    PARTITION BY RANGE (time_id)
    (PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
     PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
     PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) EXTERNAL 
          LOCATION ('sales2016_data.txt'),
     PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')) EXTERNAL 
          DEFAULT DIRECTORY sales_data2 LOCATION ('sales2017_data.txt'),
     PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')) EXTERNAL 
          DEFAULT DIRECTORY sales_data3 LOCATION ('sales2018_data.txt'),
     PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')) EXTERNAL
          DEFAULT DIRECTORY sales_data_acfs LOCATION ('sales2019_data.txt')
 );

Example 4-44 Adding an External Partition to a Hybrid Range-Partitioned Table

ALTER TABLE hybrid_partition_table 
      ADD PARTITION sales_2020 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy'))
          EXTERNAL DEFAULT DIRECTORY sales_data_acfs  LOCATION ('sales2020_data.txt');

4.7.1.2 Creating a Hybrid Partitioned Table Containing Interval Partitions

The table created below has a top level INTERVAL clause indicating that the table is interval partitioned. The create table statement specifies four partitions with varying widths. It also specifies that above the transition point of 1-Jan-2014 partitions are created with a width of one month. The high bound of partition P3 represents a transition point. P3 and all partitions below it (P0, P1, and P2 in this example) are in the range section while all partitions above it fall into the interval section

CREATE TABLE SALES_INTERVAL
( prod_id NUMBER NOT NULL,
    cust_id NUMBER NOT NULL,
    time_id DATE NOT NULL,
    channel_id NUMBER NOT NULL,
    promo_id NUMBER NOT NULL,
    quantity_sold NUMBER(10,2) NOT NULL,
    amount_sold NUMBER(10,2) NOT NULL
  )
   EXTERNAL PARTITION ATTRIBUTES( TYPE ORACLE_LOADER
       DEFAULT DIRECTORY Data_pump_dir
       ACCESS PARAMETERS
       (
         RECORDS DELIMITED BY NEWLINE
         BADFILE 'saleshyt_%a_%p.bad'
         LOGFILE 'saleshyt_%a_%p.log'
         FIELDS TERMINATED BY ','
         MISSING FIELD VALUES ARE NULL
        )
        REJECT LIMIT UNLIMITED
                                 )
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1,'month'))
(PARTITION P0 values less than (TO_DATE('01-01-2012', 'dd-mm-yyyy')) EXTERNAL LOCATION('sales_interval_hypt1.csv'),
PARTITION P1 values less than (TO_DATE('01-01-2013', 'dd-mm-yyyy')) EXTERNAL LOCATION('sales_interval_hypt1.csv'),
PARTITION P2 values less than (TO_DATE('01-07-2013', 'dd-mm-yyyy')),
PARTITION P3 values less than (TO_DATE('01-01-2014', 'dd-mm-yyyy')));

Note that when a row with the time_id of 01-01-2014 is inserted, a partition SYS_P is automatically created with a high bound of 01-02-2014, and when a row for 07-01-2020 is subsequently added, another partition SYS_P with a high bound of 08-01-2020 is added. The high bound of the automatically created partitions in this example is one month above the preceding high value, as specified in the interval clause of the hybrid partitioned table.

A hybrid partitioned table with interval partitioning allows both internal and external partitions in the range section. In the example, P1 is an external partition in the table’s range section, and internal partition P3 is the transition point. When a row corresponding containing a time_id of 01-01-2014 is inserted into the table, internal partition Pi0 is created in the interval section and a row is inserted into the partition. Note that a partition is required at the end of the range section to specify the transition point. An interval hybrid partitioned table must contain at least one internal partition.

4.7.1.3 Creating a Hybrid Partitioned Table With Automatic List Partitioning

As of Oracle Database 23ai, Oracle supports automatic list partitioning on hybrid partitioned tables

The following example describes the syntax for creating a hybrid partitioned table with automatic list partitioning. The table has an external partition P_NY containing a list of values for New York, in addition to the internal partition P_CAL for California. When an inserted row does not map to one of these existing partitions and a new partition is needed (for New Jersey, for example), the new internal partition is automatically created in the table.

CREATE TABLE SALES
(
    SALESPERSON_ID  NUMBER(5), 
    SALESPERSON_NAME VARCHAR2(30),
    SALES_STATE   VARCHAR2(20),
    SALES_AMOUNT  NUMBER(10), 
    SALES_DATE  DATE
)
EXTERNAL PARTITION ATTRIBUTES( 
      TYPE ORACLE_LOADER
       DEFAULT DIRECTORY DEF_DIR1
       ACCESS PARAMETERS
        (
           RECORDS DELIMITED BY NEWLINE
            BADFILE 'saleshyt_%a_%p.bad'
             LOGFILE 'saleshyt_%a_%p.log'
             FIELDS TERMINATED BY ','
             MISSING FIELD VALUES ARE NULL
          )
          REJECT LIMIT UNLIMITED
 )
PARTITION BY LIST(SALES_STATE) AUTOMATIC
(PARTITION P_CAL VALUES ('CALIFORNIA'),
 PARTITION P_NY   VALUES (‘NEW YORK’) EXTERNAL LOCATION(‘p_ny.csv’));

4.7.2 Converting to Hybrid Partitioned Tables

You can convert a table with only internal partitions to a hybrid partitioned table.

In Example 4-45, an internal range partitioned table is converted to a hybrid partitioned table. You must add external partition attributes to an existing table first, then add external partitions. Note that at least one partition must be an internal partition.

Example 4-45 Converting to a Hybrid Range-Partitioned Table

CREATE TABLE internal_to_hypt_table (
    prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
   PARTITION by range (time_id) 
   (PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy'))
  );

SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'INTERNAL_TO_HYPT_TABLE';
HYB
---
NO

ALTER TABLE internal_to_hypt_table 
  ADD EXTERNAL PARTITION ATTRIBUTES
   (TYPE ORACLE_LOADER 
     DEFAULT DIRECTORY sales_data
     ACCESS PARAMETERS (
       FIELDS TERMINATED BY ','
       (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
     )
   )
;

ALTER TABLE internal_to_hypt_table 
      ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy'))
          EXTERNAL LOCATION ('sales2015_data.txt');

ALTER TABLE internal_to_hypt_table 
      ADD PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy'))
          EXTERNAL LOCATION ('sales2016_data.txt');

SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'INTERNAL_TO_HYPT_TABLE';
HYB
---
YES

SELECT DEFAULT_DIRECTORY_NAME FROM USER_EXTERNAL_TABLES WHERE TABLE_NAME = 'INTERNAL_TO_HYPT_TABLE';
DEFAULT_DIRECTORY_NAME
---------------------------------------------------------------------------------------------
SALES_DATA

See Also:

Hybrid Partitioned Tables

4.7.3 Converting Hybrid Partitioned Tables to Internal Partitioned Tables

You can convert a hybrid partitioned table to a table with only internal partitions.

In Example 4-46, a hybrid partitioned table is converted to an internal range partitioned table. First, you must drop all external partitions and then you can drop the external partition attributes.

Example 4-46 Converting from a Hybrid Partitioned Table to an Internal Table

CREATE TABLE hypt_to_int_table
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    EXTERNAL PARTITION ATTRIBUTES (
     TYPE ORACLE_LOADER 
     DEFAULT DIRECTORY sales_data
       ACCESS PARAMETERS(
        FIELDS TERMINATED BY ','
        (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
       ) 
      REJECT LIMIT UNLIMITED
     ) 
    PARTITION BY RANGE (time_id)
    (PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
     PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
     PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) 
          EXTERNAL LOCATION ('sales2016_data.txt'),
     PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data2 LOCATION ('sales2017_data.txt'),
     PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data3 LOCATION ('sales2018_data.txt'),
     PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data_acfs LOCATION ('sales2019_data.txt')
 );

SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'HYPT_TO_INT_TABLE';
HYB
---
YES

ALTER TABLE hypt_to_int_table DROP PARTITION sales_2016;
ALTER TABLE hypt_to_int_table DROP PARTITION sales_2017;
ALTER TABLE hypt_to_int_table DROP PARTITION sales_2018;
ALTER TABLE hypt_to_int_table DROP PARTITION sales_2019;

ALTER TABLE hypt_to_int_table DROP EXTERNAL PARTITION ATTRIBUTES();

SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'HYPT_TO_INT_TABLE';
HYB
---
NO

See Also:

Hybrid Partitioned Tables

4.7.4 Using ADO With Hybrid Partitioned Tables

You can use Automatic Data Optimization (ADO) policies with hybrid partitioned tables under some conditions.

In Example 4-47, note that ADO policies are only defined on the internal partitions of the table.

Example 4-47 Using ADO with a Hybrid Partitioned Table

SQL> CREATE TABLE hypt_ado_table
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    EXTERNAL PARTITION ATTRIBUTES (
     TYPE ORACLE_LOADER 
     DEFAULT DIRECTORY sales_data
       ACCESS PARAMETERS(
        FIELDS TERMINATED BY ','
        (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
       ) 
      REJECT LIMIT UNLIMITED
     ) 
    PARTITION BY RANGE (time_id)
    (PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
     PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
     PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) 
          EXTERNAL LOCATION ('sales2016_data.txt'),
     PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data2 LOCATION ('sales2017_data.txt'),
     PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data3 LOCATION ('sales2018_data.txt'),
     PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data4 LOCATION ('sales2019_data.txt')
 );
Table created.

SQL> SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'HYPT_ADO_TABLE';
HYB
---
YES

SQL> ALTER TABLE hypt_ado_table MODIFY PARTITION sales_2014 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 6 MONTHS OF NO MODIFICATION;
Table altered.

SQL> ALTER TABLE hypt_ado_table MODIFY PARTITION sales_2015 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 6 MONTHS OF NO MODIFICATION;
Table altered.

SQL> SELECT POLICY_NAME, POLICY_TYPE, ENABLED FROM USER_ILMPOLICIES;
POLICY_NAME    POLICY_TYPE     ENA
-------------  --------------- -----
P1             DATA MOVEMENT   YES
P2             DATA MOVEMENT   YES

See Also:

Using Automatic Data Optimization for information about ADO policies

4.7.5 Splitting Partitions in a Hybrid Partitioned Table

In Example 4-48, the default (MAXVALUE) partition is split into a two partitions: a new partition and the existing default position. You can split a default partition similar to splitting any other partition.

Example 4-48 Splitting the Default Partition in a Hybrid Partitioned Table

4.7.6 Exchanging Data in Hybrid Partitioned Tables

You can exchange data of an internal partition in a hybrid partitioned table with an external nonpartitioned table, and exchange data of external nonpartitioned table with an internal partition in a hybrid partitioned table. Oracle supports exchange between internal and external storage but does not support a move operation between these tiers. Moving data between internal and external storage is a separate operation prior to exchange. Such a data "move" is normally done by physically copying the data to its future location, doing the exchange, and then removing or dropping the data from the old location.

Example 4-49 Exchanging data of an internal partition of a hybrid partitioned table with an external nonpartioned table

In this example, data of an internal partition of an hybrid partitioned table is "moved" to external storage using EXCHANGE PARTITION with a nonpartitioned external table containing the exact same data. Older data is moved to external storage for archival purposes.

Create a hybrid partitioned table of TYPE ORACLE_DATAPUMP.

CREATE TABLE hybrid_datapump_sales
  ( prod_id  NUMBER     NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
  EXTERNAL PARTITION ATTRIBUTES
  (TYPE ORACLE_DATAPUMP
   DEFAULT DIRECTORY sales_data
   ACCESS PARAMETERS (NOLOGFILE)
  )
  PARTITION by range (time_id)
  (
   PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
   PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')),
   PARTITION sales_2020 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy')),
   PARTITION sales_future VALUES LESS THAN (MAXVALUE)
);

Populate the hybrid partitioned table hybrid_datapump_sales with some data for this example.

SELECT * FROM hybrid_datapump_sales PARTITION(sales_2018);

   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1001        100 10-FEB-18         10         15           500        7500
      1002        110 15-JUN-18         12         18           100        3200
      1002        110 30-MAR-18         10         15           500        6500
      2105        102 21-APR-18         18         12           100        2000
      1200        155 30-APR-18         20         20           300        3600

Create an external table with the same structure as the sales_2018 partition. The SELECT clause completes data movement. The data movement operation needs to be done prior to exchange.

CREATE TABLE year_2018_datapump
   ORGANIZATION EXTERNAL
   ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY sales_data
     ACCESS PARAMETERS (NOLOGFILE) LOCATION ('sales_2018.dmp')
    )
   AS SELECT * FROM hybrid_datapump_sales PARTITION(sales_2018);

Exchange the data in sales_2018 partition with the data in the external table.

ALTER TABLE hybrid_datapump_sales
      EXCHANGE PARTITION(sales_2018) WITH TABLE year_2018_datapump;

SELECT * FROM year_2018_datapump;

   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1001        100 10-FEB-18         10         15           500        7500
      1002        110 15-JUN-18         12         18           100        3200
      1002        110 30-MAR-18         10         15           500        6500
      2105        102 21-APR-18         18         12           100        2000
      1200        155 30-APR-18         20         20           300        3600

Example 4-50 Exchanging data of an external nonpartitioned table with an internal partition of a hybrid partitioned table

In this example, data of an external table is exchanged with an internal partition of a hybrid partitioned table to add new data to a partition of the hybrid partitioned table. The text data that has been loaded into the external table is first copied to a temporary nonpartitioned internal table. Then the nonpartitioned internal table is exchanged with an internal partition of a hybrid partitioned table.

Create a hybrid partitioned table of TYPE ORACLE_DATAPUMP.

CREATE TABLE hybrid_datapump_sales
    ( prod_id  NUMBER     NOT NULL,
      cust_id       NUMBER        NOT NULL,
      time_id       DATE          NOT NULL,
      channel_id    NUMBER        NOT NULL,
      promo_id      NUMBER        NOT NULL,
      quantity_sold NUMBER(10,2)  NOT NULL,
      amount_sold   NUMBER(10,2)  NOT NULL
     )
     EXTERNAL PARTITION ATTRIBUTES
      (TYPE ORACLE_DATAPUMP
       DEFAULT DIRECTORY sales_data
       ACCESS PARAMETERS (NOLOGFILE)
      )
     PARTITION by range (time_id)
     (
      PARTITION sales_old VALUES LESS THAN (TO_DATE('01-01-2018', 'dd-mm-yyyy'))
       EXTERNAL LOCATION ('sales_old.dmp'),
      PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
      PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')),
      PARTITION sales_2020 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy')),
      PARTITION sales_future VALUES LESS THAN (MAXVALUE)
  );

Note that the sales_2020 partition has no records.

SELECT * FROM hybrid_datapump_sales PARTITION(sales_2020);

no rows selected

For this example, create an external table and load it with a text file that has been generated by some application.

CREATE TABLE ext_sales_year_2020 
        ( prod_id       NUMBER        NOT NULL,
          cust_id       NUMBER        NOT NULL,
          time_id       DATE          NOT NULL,
          channel_id    NUMBER        NOT NULL,
          promo_id      NUMBER        NOT NULL,
          quantity_sold NUMBER(10,2)  NOT NULL,
          amount_sold   NUMBER(10,2)  NOT NULL
         )
        ORGANIZATION EXTERNAL
        (
         TYPE ORACLE_LOADER DEFAULT DIRECTORY sales_data
         ACCESS PARAMETERS (
         FIELDS TERMINATED BY ','
         (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
         )
         LOCATION ('sales2020_data.txt')
 );

SELECT * FROM ext_sales_year_2020;

   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1001        100 10-JAN-20         10         15           500        7500
      1002        110 15-JAN-20         12         18           100        3200
      1001        100 20-JAN-20         10         15           500        7500
      2105        101 15-FEB-20         12         19            10         300
      2105        102 21-MAR-20         18         12           100        2000
      1200        155 30-MAR-20         20         20           300        3600
      1400        165 05-JUN-20         22         15           100        4000
      2105        125 05-JUN-20         12         16            40        8500
      2105        302 15-SEP-20         10         11            75        4350
      2108        305 18-NOV-20         10         11            70        4250

10 rows selected.

Create a temporary internal table for the exchanging of data with the hybrid partitioned table.

Note that this next step is not necessarily a one-to-one copy as shown here.

CREATE TABLE sales_year_2020 AS SELECT * FROM ext_sales_year_2020;

SELECT * FROM sales_year_2020;

   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1001        100 10-JAN-20         10         15           500        7500
      1002        110 15-JAN-20         12         18           100        3200
      1001        100 20-JAN-20         10         15           500        7500
      2105        101 15-FEB-20         12         19            10         300
      2105        102 21-MAR-20         18         12           100        2000
      1200        155 30-MAR-20         20         20           300        3600
      1400        165 05-JUN-20         22         15           100        4000
      2105        125 05-JUN-20         12         16            40        8500
      2105        302 15-SEP-20         10         11            75        4350
      2108        305 18-NOV-20         10         11            70        4250

10 rows selected.

Exchange data in the temporary internal table with the sales_2020 partition to load the data into the hybrid partitioned table.

ALTER TABLE hybrid_datapump_sales
       EXCHANGE PARTITION(sales_2020) WITH TABLE sales_year_2020; 

SELECT * FROM hybrid_datapump_sales PARTITION(sales_2020);

   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1001        100 10-JAN-20         10         15           500        7500
      1002        110 15-JAN-20         12         18           100        3200
      1001        100 20-JAN-20         10         15           500        7500
      2105        101 15-FEB-20         12         19            10         300
      2105        102 21-MAR-20         18         12           100        2000
      1200        155 30-MAR-20         20         20           300        3600
      1400        165 05-JUN-20         22         15           100        4000
      2105        125 05-JUN-20         12         16            40        8500
      2105        302 15-SEP-20         10         11            75        4350
      2108        305 18-NOV-20         10         11            70        4250

10 rows selected.

4.8 Viewing Information About Partitioned Tables and Indexes

You can display information about partitioned tables and indexes with Oracle Database views.

Table 4-4 lists the views that contain information specific to partitioned tables and indexes:

Table 4-4 Views With Information Specific to Partitioned Tables and Indexes

View	Description
`DBA_PART_TABLES` `ALL_PART_TABLES` `USER_PART_TABLES`	`DBA` view displays partitioning information for all partitioned tables in the database. `ALL` view displays partitioning information for all partitioned tables accessible to the user. `USER` view is restricted to partitioning information for partitioned tables owned by the user.
`DBA_TAB_PARTITIONS` `ALL_TAB_PARTITIONS` `USER_TAB_PARTITIONS`	Display partition-level partitioning information, partition storage parameters, and partition statistics generated by the `DBMS_STATS` package or the `ANALYZE` statement.
`DBA_TAB_SUBPARTITIONS` `ALL_TAB_SUBPARTITIONS` `USER_TAB_SUBPARTITIONS`	Display subpartition-level partitioning information, subpartition storage parameters, and subpartition statistics generated by the `DBMS_STATS` package or the `ANALYZE` statement.
`DBA_PART_KEY_COLUMNS` `ALL_PART_KEY_COLUMNS` `USER_PART_KEY_COLUMNS`	Display the partitioning key columns for partitioned tables.
`DBA_SUBPART_KEY_COLUMNS` `ALL_SUBPART_KEY_COLUMNS` `USER_SUBPART_KEY_COLUMNS`	Display the subpartitioning key columns for composite-partitioned tables (and local indexes on composite-partitioned tables).
`DBA_PART_COL_STATISTICS` `ALL_PART_COL_STATISTICS` `USER_PART_COL_STATISTICS`	Display column statistics and histogram information for the partitions of tables.
`DBA_SUBPART_COL_STATISTICS` `ALL_SUBPART_COL_STATISTICS` `USER_SUBPART_COL_STATISTICS`	Display column statistics and histogram information for subpartitions of tables.
`DBA_PART_HISTOGRAMS` `ALL_PART_HISTOGRAMS` `USER_PART_HISTOGRAMS`	Display the histogram data (end-points for each histogram) for histograms on table partitions.
`DBA_SUBPART_HISTOGRAMS` `ALL_SUBPART_HISTOGRAMS` `USER_SUBPART_HISTOGRAMS`	Display the histogram data (end-points for each histogram) for histograms on table subpartitions.
`DBA_PART_INDEXES` `ALL_PART_INDEXES` `USER_PART_INDEXES`	Display partitioning information for partitioned indexes.
`DBA_IND_PARTITIONS` `ALL_IND_PARTITIONS` `USER_IND_PARTITIONS`	Display the following for index partitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the `DBMS_STATS` package or the `ANALYZE` statement.
`DBA_IND_SUBPARTITIONS` `ALL_IND_SUBPARTITIONS` `USER_IND_SUBPARTITIONS`	Display the following information for index subpartitions: partition-level partitioning information, storage parameters for the partition, statistics collected by the `DBMS_STATS` package or the `ANALYZE` statement.
`DBA_SUBPARTITION_TEMPLATES` `ALL_SUBPARTITION_TEMPLATES` `USER_SUBPARTITION_TEMPLATES`	Display information about existing subpartition templates.

See Also:

Oracle Database Reference for descriptions of database views
Oracle Database SQL Tuning Guide for information about histograms and generating statistics for tables
Oracle Database Administrator’s Guide for more information about analyzing tables, indexes, and clusters