Partition and its types

Partition and its types

1. Range Partition
2. List Partition
3. Hash Partition
4. Composite Partition 
5. Interval Partition (Extend of Range in 11g)
6. Reference Partition
7. Virtual Column-Based Partition
8. System Partition

Range Partitioning

CREATE TABLE sales_range

(salesman_id  NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount  NUMBER(10),

sales_date    DATE)

PARTITION BY RANGE(sales_date)

(

PARTITION sales_jan2000 VALUES LESS THAN (TO_DATE('02/01/2000','MM/DD/YYYY')),

PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY')),

PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY')),

PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))

);

List Partitioning

CREATE TABLE sales_list

(salesman_id  NUMBER(5),

salesman_name VARCHAR2(30),

sales_state   VARCHAR2(20),

sales_amount  NUMBER(10),

sales_date    DATE)

PARTITION BY LIST(sales_state)

(

PARTITION sales_west VALUES('California', 'Hawaii'),

PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),

PARTITION sales_central VALUES('Texas', 'Illinois'),

PARTITION sales_other VALUES(DEFAULT)

);

Hash Partitioning

CREATE TABLE sales_hash

(salesman_id  NUMBER(5),

salesman_name VARCHAR2(30),

sales_amount  NUMBER(10),

week_no       NUMBER(2))

PARTITION BY HASH(salesman_id)

PARTITIONS 4

STORE IN (ts1, ts2, ts3, ts4);

Composite Partitioning

Range-Hash Example

CREATE TABLE sales_composite

(salesman_id  NUMBER(5),

 salesman_name VARCHAR2(30),

 sales_amount  NUMBER(10),

 sales_date    DATE)

PARTITION BY RANGE(sales_date)

SUBPARTITION BY HASH(salesman_id)

SUBPARTITION TEMPLATE(

SUBPARTITION sp1 TABLESPACE ts1,

SUBPARTITION sp2 TABLESPACE ts2,

SUBPARTITION sp3 TABLESPACE ts3,

SUBPARTITION sp4 TABLESPACE ts4)

(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','MM/DD/YYYY'))

 PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','MM/DD/YYYY'))

 PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','MM/DD/YYYY'))

 PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','MM/DD/YYYY'))

 PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','MM/DD/YYYY')));

When you use a template, Oracle names the sub partitions by concatenating the partition name, an underscore, and the sub partition name from the template.

Range-List

CREATE TABLE bimonthly_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 east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,

SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,

SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)

(

PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')),

PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),

PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY'))

);

Composite Range-Range Partitioning

Composite Range-Hash Partitioning

Composite Range-List Partitioning

Composite List-Range Partitioning

Composite List-Hash Partitioning

Composite List-List Partitioning

Interval Partitioning

Interval partitioning is an extension of range partitioning, where the system can create new partitions as they are required. The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.

CREATE TABLE interval_tab (

  id           NUMBER,

  code         VARCHAR2(10),

  description  VARCHAR2(50),

  created_date DATE

)

PARTITION BY RANGE (created_date)

INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(

   PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))

);

Querying the USER_TAB_PARTITIONS view shows there is only a single partition.

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');   ---11g

COLUMN table_name FORMAT A20

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A40

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          0

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

1 row selected.

SQL>

Provided we insert data with a created_date value less than '01-NOV-2007' the data will be placed in the existing partition and no new partitions will be created.

INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));

COMMIT;

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

1 row selected.

SQL>

If we add data beyond the range of the existing partition, a new partition is created.

INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));

COMMIT;

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P44              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

11g

2 rows selected.

SQL>

If we insert data for two months after the current largest transition point, only the required partition is created, not the intervening partitions.

INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));

COMMIT;

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P44              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P45              TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

3 rows selected.

SQL>

Notice that a partition to hold the December (less than 1st January 2008) data has not been created. If we insert data for this time period, the missing partition is created.

INSERT INTO interval_tab VALUES (7, 'SEVEN', 'Seven', TO_DATE('01-DEC-2007', 'DD-MON-YYYY'));

INSERT INTO interval_tab VALUES (8, 'EIGHT', 'Eight', TO_DATE('31-DEC-2007', 'DD-MON-YYYY'));

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P44              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P45              TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

INTERVAL_TAB         SYS_P46              TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M          2

                                          M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

4 rows selected.

The following restrictions apply to interval partitioned tables:

·       Interval partitioning is restricted to a single partition key that must be a numerical or date range.

·       At least one partition must be defined when the table is created.

·       Interval partitioning is not supported for index-organized tables.

·       You cannot create a domain index on an interval partitioned table.

·       Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can't be used at the sub partition level.

·       A MAXVALUE partition cannot be defined for an interval partitioned table.

·       NULL values are not allowed in the partition column.

Reference Partitioning

Reference partitioning provides the ability to partition a table based on the partitioning scheme of the table referenced in its referential constraint

Reference partitioning allows tables related by foreign keys to be logically equi-partitioned. The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns. Partition maintenance operations performed on the parent table are reflected on the child table, but no partition maintenance operations are allowed on the child table.

The benefit of this is that tables with a parent/child relationship can be logically equi-partitioned by inheriting the partitioning key from the parent table without duplicating the key columns.

The logical dependency also automatically cascades partition maintenance operations, making application development easier and less error prone.

This partitioning method can be useful for nested table partitioning

CREATE TABLE parent_tab (

  id           NUMBER NOT NULL,

  code         VARCHAR2(10) NOT NULL,

  description  VARCHAR2(50),

  created_date DATE,

  CONSTRAINT parent_tab_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (created_date)

(

   PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),

   PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))

);

CREATE TABLE child_tab (

  id             NUMBER NOT NULL,

  parent_tab_id  NUMBER NOT NULL,

  code           VARCHAR2(10),

  description    VARCHAR2(50),

  created_date   DATE,

  CONSTRAINT child_tab_pk PRIMARY KEY (id),

  CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)

                               REFERENCES parent_tab (id)

)

PARTITION BY REFERENCE (child_parent_tab_fk);

Child records that foreign key to rows in the first partition of the parent table should be placed in the first partition of the child table. So we insert two rows into the first partition and one row into the second of the parent table. We then insert three rows into the child table, with one foreign keyed to a row in the first partition and two foreign keyed to a row in the second partition of the master table.

INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);

INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);

INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));

INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE);

INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE);

INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE);

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'PARENT_TAB');

EXEC DBMS_STATS.gather_table_stats(USER, 'CHILD_TAB');

We now expect the parent table to have 2 records in the 2007 partition and 1 in the 2008 partition, while the child table should have 1 row in the 2007 partition and 2 rows in the 2008 partition. The following query confirms out expectation.

COLUMN table_name FORMAT A25

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A40

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS

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

CHILD_TAB                 PART_2007                                                              1

CHILD_TAB                 PART_2008                                                              2

PARENT_TAB                PART_2007            TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M          2

                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

PARENT_TAB                PART_2008            TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M          1

                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

4 rows selected.

SQL>

The following conditions and restrictions apply to reference partitioning:

·       The child table must specify a referential integrity constraint defined on the table being created.

·       This constraint must be in ENABLE VALIDATE NOT DEFERRABLE state (the default) and refer to a primary or unique key on the parent table.

·       The foreign key columns referenced in constraint must be NOT NULL.

·       The constraint cannot use the ON DELETE SET NULL clause.

·       The parent table referenced must be an existing partitioned table. All partitioning methods except interval partitioning are supported.

·       The foreign key cannot contain any virtual columns.

·       The referenced primary key or unique constraint on the parent table cannot contain any virtual columns.

·       Reference partitioning cannot be used for index-organized tables, external tables, or domain index storage tables.

·       A chain of reference partitioned tables can be created, but constraint used can't be self-referencing.

·       The ROW MOVEMENT setting for both tables must match.

·       Reference partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.

Virtual Column-Based Partitioning

Oracle 11g supports the concept of virtual columns on tables. These virtual columns are not physically stored in the table, but derived from data in the table. These virtual columns can be used in the partition key in all basic partitioning schemes.

The values for virtual columns are not physically stored in the table row on disk, rather they are evaluated on demand.

Virtual columns can be used like any other table columns. They can be indexed, and used in queries, DML, and DDL statements. Tables and indexes can be partitioned on a virtual column and statistics can be gathered upon them.

 

In Oracle Database 11g, a new feature called Virtual Columns allows you to create a column that is not stored in the table but rather is computed at run time.
The example below creates a table that is list partitioned on a virtual column that represents the first letter in the username column of the table.

CREATE TABLE users (

  id           NUMBER,

  username     VARCHAR2(20),

  first_letter VARCHAR2(1)

    GENERATED ALWAYS AS

      (

        UPPER(SUBSTR(TRIM(username), 1, 1))

      ) VIRTUAL

)

PARTITION BY LIST (first_letter)

(

  PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),

  PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),

  PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),

  PARTITION part_v_z VALUES ('V','W','X','Y','Z')

);

System Partitioning

As you would expect, system partitioning allows large tables to be broken down into smaller partitions, but unlike other partitioning schemes, the database has no control over the placement of rows during insert operations. The following example shows the creation of a system partitioned table.

CREATE TABLE system_partitioned_tab (

  id           NUMBER,

  code         VARCHAR2(10),

  description  VARCHAR2(50),

  created_date DATE

)

PARTITION BY SYSTEM

(

  PARTITION part_1,

  PARTITION part_2

);

The partition must be explicitly defined in all insert statements or an error is produced.

The PARTITION clause is used to define which partition the row should be placed in.

INSERT INTO system_partitioned_tab PARTITION (part_1) VALUES (1, 'ONE', 'One', SYSDATE);

INSERT INTO system_partitioned_tab PARTITION (part_2) VALUES (2, 'TWO', 'Two', SYSDATE);

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB');

COLUMN table_name FORMAT A25

COLUMN partition_name FORMAT A20

COLUMN high_value FORMAT A10

SELECT table_name, partition_name, high_value, num_rows

FROM   user_tab_partitions

ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE   NUM_ROWS

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

SYSTEM_PARTITIONED_TAB    PART_1                                   1

SYSTEM_PARTITIONED_TAB    PART_2                                   1

2 rows selected.

Conditions and restrictions on system partitioning include:

·       If you specify the PARTITION BY SYSTEM clause, but don't define partitions, a single partition is created with the name in the format of "SYS_Pn".

·       If you specify PARTITION BY SYSTEM PARTITIONS n clause, the database creates "n" partitions with the name in the format of "SYS_Pn". The range of allowable values for "n" is from 1 to 1024K-1.

·       System partitioning is not available for index-organized tables or a table that is part of a cluster.

·       System partitioning can play no part in composite partitioning.

·       You cannot split a system partition.

·       System partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.

·       To insert data into a system-partitioned table using an INSERT INTO ... AS subquery statement, you must use partition-extended syntax to specify the partition into which the values returned by the subquery will be inserted.

  The following operations are supported for system-partitioned tables:

• Partition maintenance operations and other DDLs (see exceptions below)
• Creation of local indexes
• Creation of local bitmapp indexes
• Creation of global indexes
• All data manipulation language (DML) operations
• INSERT. . .SELECT with partition-extended syntax.

Because of the peculiar requirements of system partitioning, the following operations are not supported for system partitioning:

• Unique local indexes are not supported because they require a partitioning key.
• CREATE TABLE AS SELECT is not supported because there is no partitioning method. It is not possible to distribute rows to partitions. Instead, you should first create the table and then insert rows into each partition.
• SPLIT PARTITION operations