Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, October 15, 2015

Administer partitioned tables and indexes using appropriate methods and keys

Administer partitioned tables and indexes using appropriate methods and keys
Basics of Partitioning
Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics

Partitioning Key

Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key is comprised of one or more columns that determine the partition where each row will be stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partitioning key

Partitioned Tables

Any table can be partitioned into a million separate partitions except those tables containing columns with LONG or LONG RAW data types.

Partitioned Index-Organized Tables

Partitioned index-organized tables are very useful for providing improved performance, manageability, and availability for index-organized tables.
For partitioning an index-organized table:
  • Partition columns must be a subset of the primary key columns.
  • Secondary indexes can be partitioned (both locally and globally).
  • OVERFLOW data segments are always equi-partitioned with the table partitions

Benefits of Partitioning


Partition Pruning


Partition-Wise Joins

Partition-wise joins can be applied when two tables are being joined together and both tables are partitioned on the join key, or when a reference partitioned table is joined with its parent table. Partition-wise joins break a large join into smaller joins that occur between each of the partitions, completing the overall join in less time

Partitioning for Availability

Partitioned database objects provide partition independence

Partitioning for Manageability

Partitioning allows tables and indexes to be partitioned into smaller, more manageable units, providing database administrators with the ability to pursue a "divide and conquer" approach to data managemen


Partitioning Strategies

  • Range
  • Hash
  • List
Screen Shot 2015-09-20 at 5.56.56 PM.png


Hash Partitioning

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.
Hash partitioning is the ideal method for distributing data evenly across devices. Hash partitioning is also an easy-to-use alternative to range partitioning, especially when the data to be partitioned is not historical or has no obvious partitioning key.

List Partitioning

List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way. For a table with a region column as the partitioning key, the North America partition might contain values Canada, USA, and Mexico.
The DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate an error.

Composite Partitioning

Composite partitioning is a combination of the basic data distribution methods; a table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition together represent a logical subset of the data.

Composite Range-Range Partitioning

Composite range-range partitioning enables logical range partitioning along two dimensions; for example, partition by order_date and range subpartition by shipping_date.

Composite Range-Hash Partitioning

Composite range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning.

Composite Range-List Partitioning

Composite range-list partitioning partitions data using the range method, and within each partition, subpartitions it using the list method. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.

Composite List-Range Partitioning

Composite list-range partitioning enables logical range subpartitioning within a given list partitioning strategy; for example, list partition by country_id and range subpartition by order_date.

Composite List-Hash Partitioning

Composite list-hash partitioning enables hash subpartitioning of a list-partitioned object; for example, to enable partition-wise joins.

Composite List-List Partitioning

Composite list-list partitioning enables logical list partitioning along two dimensions; for example, list partition by country_id and list subpartition by sales_channel

Partitioning Extensions

In addition to the basic partitioning strategies, Oracle Database provides partitioning extensions:
  • Manageability Extensions

  • Partitioning Key Extensions

Manageability Extensions

These extensions significantly enhance the manageability of partitioned tables:

  • Interval Partitioning
  • Partition Advisor

Interval Partitioning

Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database 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.

When using interval partitioning, consider the following restrictions:
  • You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
  • Interval partitioning is not supported for index-organized tables.
  • You cannot create a domain index on an interval-partitioned table.
You can create single-level interval partitioned tables as well as the following composite partitioned tables:
  • Interval-range
  • Interval-hash
  • Interval-list

Partitioning Key Extensions

These extensions extend the flexibility in defining partitioning keys:
  • Reference Partitioning
  • Virtual Column-Based Partitioning

Reference Partitioning

Reference partitioning allows the partitioning of two tables related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints

Virtual Column-Based Partitioning

In Oracle Database 11g, virtual columns allow the partitioning key to be defined by an expression, using one or more existing columns of a table. The expression is stored as metadata only.

Virtual column-based partitioning is supported with all basic partitioning strategies, including reference Partitioning, as well as interval and interval-* composite partitioning.

Partition Advisor

The Partition Advisor is part of the SQL Access Advisor. The Partition Advisor can recommend a partitioning strategy for a table based on a supplied workload of SQL statements which can be supplied by the SQL Cache, a SQL Tuning set, or be defined by the user.

Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage

  1. If the table partitioning column is a subset of the index keys, use a local index.
  2. If the index is unique and does not include the partitioning key columns, then use a global index.
  3. If your priority is manageability, use a local index.
  4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index


Local Partitioned Indexes

Each partition of a local index is associated with exactly one partition of the table. This enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.

You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table

Global Partitioned Indexes

Oracle offers two types of global partitioned indexes: range partitioned and hash partitioned.

Global Range Partitioned Indexes

Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method.
The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.
You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you want to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

Global Hash Partitioned Indexes

Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:
ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE

When to Use Range or Interval Partitioning

Range partitioning is a convenient method for partitioning historical data. The boundaries of range partitions define the ordering of the partitions in the tables or indexes.
Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition

When to Use Hash Partitioning

There are times when it is not obvious into which partition data should reside, although the partitioning key can be identified. Rather than group similar data together, there are times when it is desirable to distribute data such that it does not correspond to a business or a logical view of the data, as it does in range partitioning. With hash partitioning, a row is placed into a partition based on the result of passing the partitioning key into a hashing algorithm.

When to Use List Partitioning

You should use list partitioning when you want to specifically map rows to partitions based on discrete values

When to Use Composite Partitioning

Composite partitioning offers the benefits of partitioning on two dimensions. From a performance perspective you can take advantage of partition pruning on one or two dimensions depending on the SQL statement, and you can take advantage of the use of full or partial partition-wise joins on either dimension

When to Use Composite Range-Hash Partitioning

Composite range-hash partitioning is particularly common for tables that store history, are very large as a result, and are frequently joined with other large tables. For these types of tables (typical of data warehouse systems), composite range-hash partitioning provides the benefit of partition pruning at the range level with the opportunity to perform parallel full or partial partition-wise joins at the hash level. Specific cases can benefit from partition pruning on both dimensions for specific SQL statements

When to Use Composite Range-Range Partitioning

Composite range-range partitioning is useful for applications that store time-dependent data on more than one time dimension. Often these applications do not use one particular time dimension to access the data, but rather another time dimension, or sometimes both at the same time.

When to Use Composite List-Hash Partitioning

Composite list-hash partitioning is useful for large tables that are usually accessed on one dimension, but (due to their size) still need to take advantage of parallel full or partial partition-wise joins on another dimension in joins with other large tables

When to Use Composite List-List Partitioning

Composite list-list partitioning is useful for large tables that are often accessed on different dimensions. You can specifically map rows to partitions on those dimensions based on discrete values

4 Partition Administration


SHAIKDB>col partition_name for a30
SHAIKDB>col high_value  for a30
SHAIKDB>set linesize 400
SHAIKDB>set lines 100

SHAIKDB>create table range_part (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 Q1 values less than (to_date('01-APR-2006','DD-MON-YYYY')) TABLESPACE TBS1,
   PARTITION Q2 VALUES LESS THAN (TO_DATE('01-JUL-2006','DD-MON-YYYY')) TABLESPACE TBS2,
   PARTITION Q3 VALUES LESS THAN (TO_DATE('01-DEC-2006','DD-MON-YYYY')) TABLESPACE TBS3);

Table created.


SHAIKDB>insert into range_part select * from sh.sales;

918843 rows created.



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.


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;
Create global partition index:


SHAIKDB>create index range_part_idx1 on range_part (amount_sold) tablespace idx1 global partition by range(amount_sold)
 (partition p1 values less than (100),  partition p2 values less than  (1000),partition p3 values less than (maxvalue));

Index created.


TABLE_NAME INDEX_NAME             PARTITION PARTITION_COUNT LOCALI COM PARTITION_NAME             STATUS
---------- ------------------------------ --------- --------------- ------ --- ------------------------------ --------
RANGE_PART RANGE_PART_IDX1         RANGE          3 GLOBAL NO  P1                 USABLE
RANGE_PART RANGE_PART_IDX1         RANGE          3 GLOBAL NO  P2                 USABLE
RANGE_PART RANGE_PART_IDX1         RANGE          3 GLOBAL NO  P3                 USABLE



Create a local index:

SHAIKDB>col table_name for a10
SHAIKDB>set linesize 400

SHAIKDB>create index range_part_idx1 on range_part(amount_sold) tablespace idx1;

Index created.

SHAIKDB>select table_name,index_name,index_type from dba_indexes where table_name='RANGE_PART';

TABLE_NAME              INDEX_NAME             INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
RANGE_PART              RANGE_PART_IDX1             NORMAL


SHAIKDB>select table_name,a.index_name,partitioning_type,partition_count,locality,a.composite,partition_name,status from dba_ind_partitions a,dba_part_indexes b where a.index_name=b.index_name and a.index_name='RANGE_PART_IDX1';

TABLE_NAME INDEX_NAME  PARTITION PARTITION_COUNT LOCALI COM PARTITION_NAME  STATUS
---------- ------------------------------ --------- --------------- ------ --- ------------------------------ --------
RANGE_PART RANGE_PART_IDX1         RANGE          3 LOCAL  NO  Q1                 USABLE
RANGE_PART RANGE_PART_IDX1         RANGE          3 LOCAL  NO  Q2                 USABLE
RANGE_PART RANGE_PART_IDX1         RANGE          3 LOCAL  NO  Q3                 USABLE


Creating Interval-Partitioned Tables


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 interval partitioning, the partitioning key can only be a single column name from the table and it must be of NUMBER or DATE typ



SHAIKDB>create table interval_part
   (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-2005','dd-mm-yyyy')),
   partition p1 values less than (to_date('1-1-2006','dd-mm-yyyy')),
   partition p2 values less than (to_date('1-1-2007','dd-mm-yyyy')));
amount_sold number(10,2)) partition by range(time_id) interval(numtoyminterbal(1,'MONTH'))
                                                  

Table created.

The high bound of partition p2 represents the transition point. p2 and all partitions below it (p0 and  p1in this example) are in the range section while all partitions above it fall into the interval section


SHAIKDB>insert into interval_part values (123,1,sysdate,'A',456,1,10);

1 row created.

SHAIKDB>commit;

Commit complete.

SHAIKDB>col partition_name for a5
SHAIKDB>select partition_name,composite,high_value,partition_position,num_rows,interval from dba_tab_partitions where table_name='INTERVAL_PART';

PARTI COM HIGH_VALUE       PARTITION_POSITION    NUM_ROWS INT
----- --- -------------------------------------------------------------------------------- ------------------ ---------- ---
P0    NO  TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           1        NO
P1    NO  TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           2        NO
P2    NO  TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           3        NO
SYS_P NO  TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           4        YES
21


SHAIKDB>insert into interval_part(time_id) values (to_date('2014-01-31','yyyy-mm-dd'));

1 row created.

SHAIKDB>commit;

Commit complete.


SHAIKDB>select partition_name,composite,high_value,partition_position,num_rows,interval from dba_tab_partitions where table_name='INTERVAL_PART';

PARTI COM HIGH_VALUE           PARTITION_POSITION    NUM_ROWS INT
----- --- -------------------------------------------------------------------------------- ------------------ ---------- ---
P0    NO  TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           1        NO
P1    NO  TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           2        NO
P2    NO  TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           3        NO
SYS_P NO  TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           4        YES
22

SYS_P NO  TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           5        YES
21




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

SHAIKDB>create table hash_part1 (id number,name varchar2(10)) partition by hash (id) partitions 6
   store in (tbs1,tbs2,tbs3);

Table created

SHAIKDB>begin
 2  for i in 1..1000 loop
 3  insert into hash_part1 values (i,'AA');
 4  commit;
 5  end loop;
 6  end;
 7  /

PL/SQL procedure successfully completed.

SHAIKDB>select count(*) from hash_part1;

 COUNT(*)
----------
     1000

SHAIKDB>col high_value for a10
select partition_name,composite,high_value,partitioning_type,status,def_tablespace_name,partition_count
   from dba_tab_partitions a , dba_part_tables b where a.table_name=b.table_name and a.table_name='HASH_PART1';



PARTITION_NAME              COM HIGH_VALUE PARTITION STATUS     DEF_TABLESPACE_NAME       PARTITION_COUNT
------------------------------ --- ---------- --------- -------- ------------------------------ ---------------
SYS_P23               NO          HASH    VALID     SYSTEM                      6
SYS_P24               NO          HASH    VALID     SYSTEM                      6
SYS_P25               NO          HASH    VALID     SYSTEM                      6
SYS_P26               NO          HASH    VALID     SYSTEM                      6
SYS_P27               NO          HASH    VALID     SYSTEM                      6
SYS_P28               NO          HASH    VALID     SYSTEM                      6


SHAIKDB>create table dept_hash (id number,name varchar2(10)) storage (initial 10k) tablespace tbs1
   partition by hash (id)
   partitions 2;

Table created.

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


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

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

Creating Reference-Partitioned Tables

To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement. This 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.
The following example 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.
CREATE TABLE orders
   ( order_id           NUMBER(12),
     order_date         TIMESTAMP WITH LOCAL TIME ZONE,
     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);

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 will have one partition for each subpartition of its parent; otherwise the table will have 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 will inherit its name from the corresponding partition in the parent table, unless this inherited name conflicts with one of the explicit names given. In this case, the partition will have a system-generated name.


Creating Composite Partitioned Tables

To create a composite partitioned table, you start by using the PARTITION BY [ RANGE | LIST ] clause of a CREATE TABLE statement. Next, you specify a SUBPARTITION BY [ RANGE | LIST | HASH ] clause that follows similar syntax and rules as the PARTITION BY [ RANGE | LIST | HASH ] clause. The individual PARTITION and SUBPARTITION or SUBPARTITIONS clauses, and optionally a SUBPARTITION TEMPLATE clause, follow.

Creating Composite Range-Hash Partitioned Tables

The following statement creates a range-hash partitioned table. In this example, four range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (ts1, ...,ts4).
CREATE TABLE sales
 ( prod_id       NUMBER(6)
 , cust_id       NUMBER
 , time_id       DATE
 , channel_id    CHAR(1)
 , promo_id      NUMBER(6)
 , quantity_sold NUMBER(3)
 , amount_sold   NUMBER(10,2)
 )
PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
 SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);


CREATE TABLE emp (deptno NUMBER, empname VARCHAR(32), grade NUMBER)   
    PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
       SUBPARTITIONS 8 STORE IN (ts1, ts3, ts5, ts7)
   (PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000)
       STORE IN (ts2, ts4, ts6, ts8),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
      (SUBPARTITION p3_s1 TABLESPACE ts4,
       SUBPARTITION p3_s2 TABLESPACE ts5));



CREATE INDEX emp_ix ON emp(deptno)
    LOCAL STORE IN (ts7, ts8, ts9);
This local index is equipartitioned with the base table as follows:
  • It consists of as many partitions as the base table.
  • Each index partition consists of as many subpartitions as the corresponding base table partition.
  • Index entries for rows in a given subpartition of the base table are stored in the corresponding subpartition of the index



how range-list partitioning might be used. The example tracks sales data of products by quarters and within each quarter, groups it by specified states.
CREATE TABLE quarterly_regional_sales
     (deptno number, item_no varchar2(20),
      txn_date date, txn_amount number, state varchar2(2))
 TABLESPACE ts4
 PARTITION BY RANGE (txn_date)
   SUBPARTITION BY LIST (state)
     (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
        (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
         SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
         SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
         SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
         SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
         SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
        ),
      PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
        (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
         SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
         SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
         SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
         SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
         SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
        ),
      PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
        (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
         SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
         SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
         SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
         SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
         SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
        ),
      PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
        (SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
         SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
         SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
         SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
         SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
         SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
        )
     );
The following example creates a table that specifies a tablespace at the partition and subpartition levels. The number of subpartitions within each partition varies, and default subpartitions are specified.
CREATE TABLE sample_regional_sales
     (deptno number, item_no varchar2(20),
      txn_date date, txn_amount number, state varchar2(2))
 PARTITION BY RANGE (txn_date)
   SUBPARTITION BY LIST (state)
     (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
         TABLESPACE tbs_1
        (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
         SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
         SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
         SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
         SUBPARTITION q1_others VALUES (DEFAULT) TABLESPACE tbs_4
        ),
      PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
         TABLESPACE tbs_2
        (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
         SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
         SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
         SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
         SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
         SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
        ),
      PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
         TABLESPACE tbs_3
        (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
         SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
         SUBPARTITION q3_others VALUES (DEFAULT) TABLESPACE tbs_4
        ),
      PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
         TABLESPACE tbs_4
     );


Creating Composite Range-Range Partitioned Tables

The range partitions of a range-range composite partitioned table are described as for non-composite range partitioned tables. This allows that optional subclauses of a PARTITION clause can specify physical and other attributes, including tablespace, specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.
The range subpartition descriptions, in the SUBPARTITION clauses, are described as for non-composite range partitions, except the only physical attribute that can be specified is an optional tablespace. Subpartitions inherit all other physical attributes from the partition description.
The following example illustrates how range-range partitioning might be used. The example tracks shipments. The service level agreement with the customer states that every order will be delivered in the calendar month after the order was placed. The following types of orders are identified:
  • E (EARLY): orders that are delivered before the 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)
 )
);


Creating Composite List-Hash Partitioned Tables
The following example shows an accounts table that is list partitioned by region and subpartitioned using hash by customer identifier.
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')
);
To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".
Creating Composite List-List Partitioned Tables
The following example shows an accounts table that is list partitioned by region and subpartitioned using list by account status.
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')
 )
);
To learn how using a subpartition template can simplify the specification of a composite partitioned table, see "Using Subpartition Templates to Describe Composite Partitioned Tables".
Creating Composite List-Range Partitioned Tables
The following example shows an accounts table that is list partitioned by region and subpartitioned using range by account balance. Note that row movement is enabled. Subpartitions for different list partitions could have different ranges specified.
CREATE TABLE accounts
( id             NUMBER
, account_number NUMBER
, customer_id    NUMBER
, balance        NUMBER
, branch_id      NUMBER
, region         VARCHAR(2)
, status         VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY RANGE (balance)
( PARTITION p_northwest VALUES ('OR', 'WA')
 ( SUBPARTITION p_nw_low VALUES LESS THAN (1000)
 , SUBPARTITION p_nw_average VALUES LESS THAN (10000)
 , SUBPARTITION p_nw_high VALUES LESS THAN (100000)
 , SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
 ( SUBPARTITION p_sw_low VALUES LESS THAN (1000)
 , SUBPARTITION p_sw_average VALUES LESS THAN (10000)
 , SUBPARTITION p_sw_high VALUES LESS THAN (100000)
 , SUBPARTITION p_sw_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
 ( SUBPARTITION p_ne_low VALUES LESS THAN (1000)
 , SUBPARTITION p_ne_average VALUES LESS THAN (10000)
 , SUBPARTITION p_ne_high VALUES LESS THAN (100000)
 , SUBPARTITION p_ne_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_southeast VALUES ('FL', 'GA')
 ( SUBPARTITION p_se_low VALUES LESS THAN (1000)
 , SUBPARTITION p_se_average VALUES LESS THAN (10000)
 , SUBPARTITION p_se_high VALUES LESS THAN (100000)
 , SUBPARTITION p_se_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_northcentral VALUES ('SD', 'WI')
 ( SUBPARTITION p_nc_low VALUES LESS THAN (1000)
 , SUBPARTITION p_nc_average VALUES LESS THAN (10000)
 , SUBPARTITION p_nc_high VALUES LESS THAN (100000)
 , SUBPARTITION p_nc_extraordinary VALUES LESS THAN (MAXVALUE)
 )
, PARTITION p_southcentral VALUES ('OK', 'TX')
 ( SUBPARTITION p_sc_low VALUES LESS THAN (1000)
 , SUBPARTITION p_sc_average VALUES LESS THAN (10000)
 , SUBPARTITION p_sc_high VALUES LESS THAN (100000)
 , SUBPARTITION p_sc_extraordinary VALUES LESS THAN (MAXVALUE)
 )
) ENABLE ROW MOVEMENT;

Creating Composite Interval-* Partitioned Tables

The concepts of interval-* composite partitioning are similar to the concepts for range-* partitioning. However, you extend the PARTITION BY RANGE clause to include the INTERVAL definition. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point.
The subpartitions for intervals in an interval-* partitioned table will be created when the database creates the interval. You can specify the definition of future subpartitions only through the use of a subpartition template. To learn more about how to use a subpartition template, see "Using Subpartition Templates to Describe Composite Partitioned Tables".
Creating Composite Interval-Hash Partitioned Tables
You can create an interval-hash partitioned table with multiple hash partitions using one of the following methods:
  • Specify a number of hash partitions in the PARTITIONS clause.
  • Use a subpartition template.
If you do not use either of these methods, then future interval partitions will only get 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. Note that this example specifies a number of hash partitions, without any specific tablespace assignment to the individual hash partitions.
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;
The following 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 will be stored in separate tablespaces. Note that the subpartition template is used in order to define the tablespace assignment for future hash subpartitions. To learn more about how to use a subpartition template, see "Using Subpartition Templates to Describe Composite Partitioned Tables".
CREATE TABLE sales
 ( prod_id       NUMBER(6)
 , cust_id       NUMBER
 , time_id       DATE
 , channel_id    CHAR(1)
 , promo_id      NUMBER(6)
 , quantity_sold NUMBER(3)
 , amount_sold   NUMBER(10,2)
 )
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY hash(cust_id)
  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;
Creating Composite Interval-List Partitioned Tables
The only way to define list subpartitions for future interval partitions is through the use of the subpartition template. If you do not use the subpartitioning template, then the only subpartition that will be created for every interval partition is a DEFAULT subpartition. To learn more about how to use a subpartition template, see "Using Subpartition Templates to Describe Composite Partitioned Tables".
The following example shows the sales table, interval partitioned using daily intervals on time_id, with list subpartitions by channel_id.
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;
Creating Composite Interval-Range Partitioned Tables
The only way to define range subpartitions for future interval partitions is through the use of the subpartition template. If you do not use the subpartition template, then the only subpartition that will be created for every interval partition is a range subpartition with the MAXVALUE upper boundary. To learn more about how to use a subpartition template, see "Using Subpartition Templates to Describe Composite Partitioned Tables".
The following example shows the sales table, interval partitioned using daily intervals on time_id, with range subpartitions by amount_sold.
CREATE TABLE sales
 ( prod_id       NUMBER(6)
 , cust_id       NUMBER
 , time_id       DATE
 , channel_id    CHAR(1)
 , promo_id      NUMBER(6)
 , quantity_sold NUMBER(3)
 , amount_sold   NUMBER(10,2)
 )
PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY LIST (channel_id)
  SUBPARTITION TEMPLATE
  ( SUBPARTITION p_catalog VALUES ('C')
  , SUBPARTITION p_internet VALUES ('I')
  , SUBPARTITION p_partners VALUES ('P')
  , SUBPARTITION p_direct_sales VALUES ('S')
  , SUBPARTITION p_tele_sales VALUES ('T')
  )
( PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy')))
PARALLEL;
Interval Partitioning:

Let us consider the following example:
create table sales
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
(
partition p0901 values less than (to_date('2009-02-01','yyyy-mm-dd')),
partition p0902 values less than (to_date('2009-03-01','yyyy-mm-dd'))
);

Only two partitions are defined here January 2009 and February 2009.
Now if a new record is inserted having sales_dt value as March 2009, it will fail with following error:

ORA-14400: inserted partition key does not map to any partition

Prior to 11g,it is required to add a partition for March2009,then only the record can be inserted.
Often creation of partitions beforehand is not affordable.


11g, Oracle introduced new partition type called INTERVAL PARTITIONING. let us have a look at the benefits of interval partitioning :
create table sales
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))
( partition p0901 values less than (to_date('2009-02-01','yyyy-mm-dd')) );


SQL> insert into sales values (1,'01-jun-09');
You can see that this time it did not generate the ORA_14400 errors. Let see what oracle did to insert the data over the partitions limit.

Here we go.
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES';

PARTITION_NAME HIGH_VALUE
--------------- ----------------------------------------------------------------
P0701 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA

SYS_P41 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA
Note:  The partition named SYS_P41 with a high value of July 1, 2009,  will hold data up to the end of June. This partition was created dynamically by Oracle and has a system generated name.


Let us insert a value lower than highest value ,for example May1,2009.
SQL> insert into sales6 values (1,'01-may-09');

1 row created.
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES';

PARTITION_NAME HIGH_VALUE
--------------- ----------------------------------------------------------------
P0701 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA

SYS_P41 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA

SYS_P42 TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C
ALENDAR=GREGORIA


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


The following example specifies that above the transition point of January 1, 2007, partitions are created with a width of one week.
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(numtodsinterval(7,'day'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) );


The high bound of partition p0 represents the transition point. p0 is in the range section while all partitions above it fall into the interval section.


Interval Partitioning Essentials - Common Questions - Top Issues (Doc ID 1479115.1)

Specifying a Subpartition Template for a *-Hash Partitioned Table

In the case of [range | interval | list]-hash partitioned tables, the subpartition template can describe the subpartitions in detail, or it can specify just the number of hash subpartitions.
The following example creates a range-hash partitioned table using a subpartition template:
CREATE TABLE emp_sub_template (deptno NUMBER, empname VARCHAR(32), grade NUMBER)
    PARTITION BY RANGE(deptno) SUBPARTITION BY HASH(empname)
    SUBPARTITION TEMPLATE
        (SUBPARTITION a TABLESPACE ts1,
         SUBPARTITION b TABLESPACE ts2,
         SUBPARTITION c TABLESPACE ts3,
         SUBPARTITION d TABLESPACE ts4
        )
   (PARTITION p1 VALUES LESS THAN (1000),
    PARTITION p2 VALUES LESS THAN (2000),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
   );
This example produces the following table description:
  • Every partition has four subpartitions as described in the subpartition template.
  • Each subpartition has a tablespace specified. It is required that if a tablespace is specified for one subpartition in a subpartition template, then one must be specified for all.
  • The names of the subpartitions, unless you use interval-* subpartitioning, are generated by concatenating the partition name with the subpartition name in the form:
  • partition name_subpartition name
  • For interval-* subpartitioning, the subpartition names are system-generated in the form:
  • SYS_SUBPn
The following query displays the subpartition names and tablespaces:
SQL> SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME
 2  FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP_SUB_TEMPLATE'
 3  ORDER BY TABLESPACE_NAME;

TABLESPACE_NAME PARTITION_NAME  SUBPARTITION_NAME
--------------- --------------- ------------------
TS1             P1              P1_A
TS1             P2              P2_A
TS1             P3              P3_A
TS2             P1              P1_B
TS2             P2              P2_B
TS2             P3              P3_B
TS3             P1              P1_C
TS3             P2              P2_C
TS3             P3              P3_C
TS4             P1              P1_D
TS4             P2              P2_D
TS4             P3              P3_D

12 rows selected.

Specifying a Subpartition Template for a *-List Partitioned Table

The following example, for a range-list partitioned table, illustrates how using a subpartition template can help you stripe data across tablespaces. In this example, a table is created where the table subpartitions are vertically striped, meaning that subpartition n from every partition is in the same tablespace.
CREATE TABLE stripe_regional_sales
           ( deptno number, item_no varchar2(20),
             txn_date date, txn_amount number, state varchar2(2))
  PARTITION BY RANGE (txn_date)
  SUBPARTITION BY LIST (state)
  SUBPARTITION TEMPLATE
     (SUBPARTITION northwest VALUES ('OR', 'WA') TABLESPACE tbs_1,
      SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE tbs_2,
      SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE tbs_3,
      SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE tbs_4,
      SUBPARTITION midwest VALUES ('SD', 'WI') TABLESPACE tbs_5,
      SUBPARTITION south VALUES ('AL', 'AK') TABLESPACE tbs_6,
      SUBPARTITION others VALUES (DEFAULT ) TABLESPACE tbs_7
     )
 (PARTITION q1_1999 VALUES LESS THAN ( TO_DATE('01-APR-1999','DD-MON-YYYY')),
  PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-JUL-1999','DD-MON-YYYY')),
  PARTITION q3_1999 VALUES LESS THAN ( TO_DATE('01-OCT-1999','DD-MON-YYYY')),
  PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
 );
If you specified the tablespaces at the partition level (for example, tbs_1 for partition q1_1999, tbs_2 for partition q2_1999, tbs_3 for partition q3_1999, and tbs_4 for partition q4_1999) and not in the subpartition template, then the table would be horizontally striped. All subpartitions would be in the tablespace of the owning partition.

Using Multicolumn Partitioning Keys

For range-partitioned and hash-partitioned tables, you can specify up to 16 partitioning key columns. Multicolumn partitioning should be used 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 nth column will therefore be 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, will be 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 will determine that the row does not fit in this partition and will consider the next partition for a match.
In the case of 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 example 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:
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);
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 one of the partitioning key columns, 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).
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.
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
Every row with supplier_id < 10 will be stored in partition p1, regardless of the partnum value. The column partnum will be evaluated only if supplier_id =10, and the corresponding rows will be 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.

Using Virtual Column-Based Partitioning

In the context of 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 that you want to use as the partitioning column cannot use calls to a PL/SQL function.
See Also:
Oracle Database SQL Language Reference for the syntax on how to create a virtual column
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.
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;


ALTER TABLE Maintenance Operations for Table Partitions

Maintenance Operation
RangeComposite Range-*
IntervalComposite Interval-*
Hash
ListComposite List-*
Reference
Adding Partitions
ADD PARTITION
ADD PARTITION
ADD PARTITION
ADD PARTITION
N/AFoot 1
Coalescing Partitions
N/A
N/A
COALESCE PARTITION
N/A
N/AFootref 1
Dropping Partitions
DROP PARTITION
DROP PARTITION
N/A
DROP PARTITION
N/AFootref 1
Exchanging Partitions
EXCHANGE PARTITION
EXCHANGE PARTITION
EXCHANGE PARTITION
EXCHANGE PARTITION
EXCHANGE PARTITION
Merging Partitions
MERGE PARTITIONS
MERGE PARTITIONS
N/A
MERGE PARTITIONS
N/AFootref 1
Modifying Default Attributes
MODIFY DEFAULT ATTRIBUTES
MODIFY DEFAULT ATTRIBUTES
MODIFY DEFAULT ATTRIBUTES
MODIFY DEFAULT ATTRIBUTES
MODIFY DEFAULT ATTRIBUTES
Modifying Real Attributes of Partitions
MODIFY PARTITION
MODIFY PARTITION
MODIFY PARTITION
MODIFY PARTITION
MODIFY PARTITION
Modifying List Partitions: Adding Values
N/A
N/A
N/A
MODIFY PARTITION ... ADD VALUES
N/A
Modifying List Partitions: Dropping Values
N/A
N/A
N/A
MODIFY PARTITION ... DROP VALUES
N/A
Moving Partitions
MOVE SUBPARTITION
MOVE SUBPARTITION
MOVE PARTITION
MOVE SUBPARTITION
MOVE PARTITION
Renaming Partitions
RENAME PARTITION
RENAME PARTITION
RENAME PARTITION
RENAME PARTITION
RENAME PARTITION
Splitting Partitions
SPLIT PARTITION
SPLIT PARTITION
N/A
SPLIT PARTITION
N/AFootref 1
Truncating Partitions
TRUNCATE PARTITION
TRUNCATE PARTITION
TRUNCATE PARTITION
TRUNCATE PARTITION
TRUNCATE PARTITION



ALTER TABLE Maintenance Operations for Table Subpartitions

Maintenance Operation
Composite *-Range
Composite *-Hash
Composite *-List
Adding Partitions
MODIFY PARTITION ... ADD SUBPARTITION
MODIFY PARTITION ... ADD SUBPARTITION
MODIFY PARTITION ... ADD SUBPARTITION
Coalescing Partitions
N/A
MODIFY PARTITION ... COALESCE SUBPARTITION
N/A
Dropping Partitions
DROP SUBPARTITION
N/A
DROP SUBPARTITION
Exchanging Partitions
EXCHANGE SUBPARTITION
N/A
EXCHANGE SUBPARTITION
Merging Partitions
MERGE SUBPARTITIONS
N/A
MERGE SUBPARTITIONS
Modifying Default Attributes
MODIFY DEFAULT ATTRIBUTES FOR PARTITION
MODIFY DEFAULT ATTRIBUTES FOR PARTITION
MODIFY DEFAULT ATTRIBUTES FOR PARTITION
Modifying Real Attributes of Partitions
MODIFY SUBPARTITION
MODIFY SUBPARTITION
MODIFY SUBPARTITION
Modifying List Partitions: Adding Values
N/A
N/A
MODIFY SUBPARTITION ... ADD VALUES
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 Partitions
MOVE SUBPARTITION
MOVE SUBPARTITION
MOVE SUBPARTITION
Renaming Partitions
RENAME SUBPARTITION
RENAME SUBPARTITION
RENAME SUBPARTITION
Splitting Partitions
SPLIT SUBPARTITION
N/A
SPLIT SUBPARTITION
Truncating Partitions
TRUNCATE SUBPARTITION
TRUNCATE SUBPARTITION
TRUNCATE SUBPARTITION





ALTER INDEX Maintenance Operations for Index Partitions


Maintenance Operation
Type of Index
Type of Index Partitioning
Range
Hash and List
Composite
Adding Index Partitions
Global
-
ADD PARTITION (hash only)
-

Local
N/A
N/A
N/A
Dropping Index Partitions
Global
DROP PARTITION
-
-

Local
N/A
N/A
N/A
Modifying Default Attributes of Index Partitions
Global
MODIFY DEFAULT ATTRIBUTES
-
-

Local
MODIFY DEFAULT ATTRIBUTES
MODIFY DEFAULT ATTRIBUTES
MODIFY DEFAULT ATTRIBUTES
MODIFY DEFAULT ATTRIBUTES FOR PARTITION
Modifying Real Attributes of Index Partitions
Global
MODIFY PARTITION
-
-

Local
MODIFY PARTITION
MODIFY PARTITION
MODIFY PARTITION
MODIFY SUBPARTITION
Rebuilding Index Partitions
Global
REBUILD PARTITION
-
-

Local
REBUILD PARTITION
REBUILD PARTITION
REBUILD SUBPARTITION
Renaming Index Partitions
Global
RENAME PARTITION
-
-

Local
RENAME PARTITION
RENAME PARTITION
RENAME PARTITION
RENAME SUBPARTITION
Splitting Index Partitions
Global
SPLIT PARTITION
-
-

Local
N/A
N/A
N/A


Partition Maintenance Operations:

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

Adding a Partition:


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;

Adding a Partition to a Hash-Partitioned Table



ALTER TABLE scubagear ADD PARTITION;

ALTER TABLE scubagear
     ADD PARTITION p_named TABLESPACE gear5;

Adding a Partition to a List-Partitioned Table

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

ALTER TABLE q1_sales_by_region
  ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
     STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
     NOLOGGING;

Adding a Partition to an Interval-Partitioned Table

You cannot explicitly add a partition to an interval-partitioned table unless you first lock the partition, which triggers the creation of the partition. The database automatically creates a partition for an interval when data for that interval is inserted. In general, you only need to explicitly create interval partitions for a partition exchange load scenario.
To change the interval for future partitions, use the SET INTERVAL clause of the ALTER TABLE statement. This clause changes the interval for partitions beyond the current highest boundary of all materialized interval partitions.
You also use the SET INTERVAL clause to migrate an existing range partitioned or range-* composite partitioned table into an interval or interval-* partitioned table. If you want to disable the creation of future interval partitions, and effectively revert back to a range-partitioned table, then use an empty value in the SET INTERVAL clause. Created interval partitions will then be transformed into range partitions with their current high values.
To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:
ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');

ORA-14767: Cannot specify this interval with existing high bounds
You need to create another daily partition with a high bound of February 1, 2007 in order to successfully change to a monthly interval:
LOCK TABLE transactions PARTITION FOR(TO_DATE('31-JAN-2007','dd-MON-yyyy') IN SHARE MODE;

ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');
The lower partitions of an interval-partitioned table are range partitions. You can split range partitions in order to add more partitions in the range portion of the interval-partitioned table.
In order to disable interval partitioning on the transactions table, use:
ALTER TABLE transactions SET INTERVAL ();


Use one of the following statements to drop a table partition or subpartition:
  • ALTER TABLE ... DROP PARTITION to drop a table partition
  • ALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a composite *-[range | list] partitioned table

Dropping 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 to drop the table partition.
Method 1
Leave the global indexes in place during the ALTER TABLE ... DROP PARTITION statement. Afterward, you must rebuild any global indexes (whether partitioned or not) because the index (or index partitions) will have been marked UNUSABLE. The following statements provide an example of dropping partition dec98 from the sales table, then rebuilding its global non-partitioned 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, feb99_ix, mar99_ix, ..., 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.
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 causes the global index to be updated at the time the partition is dropped.
ALTER TABLE sales DROP PARTITION dec98
    UPDATE INDEXES;
Dropping 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 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 you want 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 make sure to remove all the referencing data in order to be able to 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.

Dropping Interval Partitions

You can drop interval partitions in an interval-partitioned table. This operation will drop the data for the interval only and leave the interval definition in tact. If data is inserted in the interval just dropped, then the database will again create an interval partition.
You can also drop range partitions in an interval-partitioned table. The rules for dropping a range partition in an interval-partitioned table follow the rules for dropping a range partition in a range-partitioned table. If you drop a range partition in the middle of a set of range partitions, then the lower boundary for the next range partition shifts to the lower boundary of the range partition you just dropped. You cannot drop the highest range partition in the range-partitioned section of an interval-partitioned table.
The following example drops the September 2007 interval partition from the sales table. There are only local indexes so no indexes will be invalidated.
ALTER TABLE sales DROP PARTITION FOR(TO_DATE('01-SEP-2007','dd-MON-yyyy'));

Dropping Index Partitions

You cannot explicitly drop a partition of a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.
If a global index partition is empty, then you can explicitly drop it by issuing the ALTER INDEX ... DROP PARTITION statement. But, if a global index partition contains data, then dropping the partition causes the next highest partition to be marked UNUSABLE. For example, you would like to drop the index partition P1, and P2 is the next highest partition. You must issue the following statements:
ALTER INDEX npr DROP PARTITION P1;
ALTER INDEX npr REBUILD PARTITION P2;


Documentation:

Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2)
Part Number E10837-02

No comments: