Skip to main content

Search

Welcome to Tibero GTS!

Table Partition Usage Guide

Document Type | Technical Information

Category | Administration

Applicable Product Version | Tibero 7.2.4

Document Number | TADTI172

 

Overview

Table partitioning is a method of dividing a table into smaller units called partitions to prevent performance degradation of large tables due to continuous data growth and to facilitate easier operation and management.

Partitioning supports both tables and indexes. Each partition shares the definition of the table's columns and constraints but can be managed as a separate segment with different physical attributes.

Below is an explanation of the advantages and disadvantages of partitioned tables.

CategoryDescription
Advantages
  • Improves performance by reducing the scope of access when querying data.
  • Physically dividing into multiple areas reduces the possibility of overall data corruption and improves data availability.
  • Backup and recovery operations can be performed per partition.
  • Distributes Disk I/O at the partition level to reduce I/O contention.
Disadvantages
  • When performing modification operations (Drop, Split) on partitions containing data, global indexes become unusable.
  • An error occurs if data outside the partition range is inserted.
  • Periodic creation or deletion of partitions is required.

 

 

Methods

Range Partitioned Table

This method partitions a table based on the range of a specific column and is suitable mainly for tables managed by date. It is advantageous for data management as you can identify which partition the data belongs to based on the partition key.

However, since data distribution depends on the partition key, data skew can occur in certain partitions.

 

Creation Method

CREATE TABLE <table_name> (
      <column1>,
      <column2>,
      <column3>
)
PARTITION BY RANGE(<column>) (
  PARTITION <partition_name> VALUES LESS THAN (<value>),
  PARTITION <partition_name> VALUES LESS THAN (<value>),
  PARTITION <partition_name> VALUES LESS THAN (MAXVALUE))
ENABLE ROW MOVEMENT;

 

Example Creation

SQL> CREATE TABLE DEPT_PART_R (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     PARTITION BY RANGE(DEPTNO) (
       PARTITION PART1 VALUES LESS THAN (11) ,
       PARTITION PART2 VALUES LESS THAN (21) ,
       PARTITION PART3 VALUES LESS THAN (31) ,
       PARTITION PART4 VALUES LESS THAN (41) ,
       PARTITION PARTMAX VALUES LESS THAN (MAXVALUE))
     ENABLE ROW MOVEMENT;

Table 'DEPT_PART_R' created.

 

Data Insertion and Verification of Range Partitioned Table Creation

1. Insert data.

SQL> INSERT INTO DEPT_PART_R
     SELECT LEVEL, CHR(65+MOD(LEVEL,26)), LEVEL, LEVEL FROM DUAL
     CONNECT BY LEVEL <= 60;

60 rows inserted.

SQL> COMMIT;

Commit completed.

2. Collect statistics.

SQL> begin
        dbms_stats.gather_table_stats(OWNNAME=>'tibero',
                                      TABNAME=>'DEPT_PART_R',
                                      ESTIMATE_PERCENT=>100);
     end;
     /

PSM completed

3. Verify the number of rows loaded and the partitioned table creation.

SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col BOUND format a10
SQL> col NUM_ROWS format 999,999,999
SQL> SELECT TABLE_NAME, PARTITION_NAME, BOUND,
     NUM_ROWS FROM USER_TAB_PARTITIONS
     WHERE table_name = 'DEPT_PART_R';

TABLE_NAME      PARTITION_NAME  BOUND          NUM_ROWS
--------------- --------------- ---------- ------------
DEPT_PART_R     PART1           11                    0
DEPT_PART_R     PART2           21                    0
DEPT_PART_R     PART3           31                    0
DEPT_PART_R     PART4           41                    0
DEPT_PART_R     PARTMAX         MAXVALUE              0

5 rows selected.

 

Hash Partitioned Table

This method partitions data by applying a hash function to the partition key value and is mainly used to improve performance rather than for data management purposes.

Since data is distributed based on the hash key, it is not possible to know which data resides in which partition, but data is evenly distributed across partitions, so columns with evenly distributed partition keys are effective.

 

Creation Method

CREATE TABLE <table_name> (
    <column1>,
    <column2>,
    <column3>
)
PARTITION BY HASH(<column>) (
    PARTITION <partition_name>,
    PARTITION <partition_name>,
    PARTITION <partition_name>,
    PARTITION <partition_name>)
ENABLE ROW MOVEMENT;

 

Example Creation

SQL> CREATE TABLE DEPT_PART_H (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
)
PARTITION BY HASH(DEPTNO) (
       PARTITION HASH1,
       PARTITION HASH2,
       PARTITION HASH3,
       PARTITION HASH4)
ENABLE ROW MOVEMENT;

Table 'DEPT_PART_H' created.

 

Data Insertion and Verification of Hash Partitioned Table Creation

1. Insert data.

SQL> INSERT INTO DEPT_PART_H
     SELECT LEVEL, CHR(65+MOD(LEVEL,26)), LEVEL, LEVEL FROM DUAL
     CONNECT BY LEVEL <= 48;

48 rows inserted.

SQL> COMMIT;

Commit completed.

2. Collect statistics.

SQL> begin
       dbms_stats.gather_table_stats(OWNNAME=>'tibero',
                                     TABNAME=>'DEPT_PART_H',
                                     ESTIMATE_PERCENT=>100);
     end;
     /

PSM completed.

3. Verify the number of rows loaded and the partitioned table creation.

SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col BOUND format a10
SQL> col NUM_ROWS format 999,999,999
SQL> SELECT TABLE_NAME, PARTITION_NAME, BOUND,
     NUM_ROWS FROM USER_TAB_PARTITIONS
     WHERE TABLE_NAME = 'DEPT_PART_H';

TABLE_NAME      PARTITION_NAME  BOUND          NUM_ROWS
--------------- --------------- ---------- ------------
DEPT_PART_H     HASH1                                14
DEPT_PART_H     HASH2                                13
DEPT_PART_H     HASH3                                10
DEPT_PART_H     HASH4                                11

4 rows selected.

 

Composite Partitioned Table

This method further partitions each partition by applying hash or list partitioning methods as subpartitions.

For example, using range-hash partitioning allows you to leverage the data management advantages of range partitioning along with the performance benefits of hash partitioning.

 

Range-Range Method

Example Creation

SQL> CREATE TABLE DEPT_PART_RR (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     PCTFREE 10
     INITRANS 2
     PARTITION BY RANGE(DEPTNO)
     SUBPARTITION BY RANGE(MGRNO)
     (
     PARTITION R_RANGE1 VALUES LESS THAN (10)
       (SUBPARTITION R_RANGE1_SUB1 VALUES LESS THAN (10),
       SUBPARTITION R_RANGE1_SUB2 VALUES LESS THAN (30),
       SUBPARTITION R_RANGE1_SUB_MAX VALUES LESS THAN (MAXVALUE)
     ),
     PARTITION R_RANGE2 VALUES LESS THAN (20)
       (SUBPARTITION R_RANGE2_SUB1 VALUES LESS THAN (10),
       SUBPARTITION R_RANGE2_SUB2 VALUES LESS THAN (30),
       SUBPARTITION R_RANGE2_SUB_MAX VALUES LESS THAN (MAXVALUE)
     ),
     PARTITION R_RANGE3 VALUES LESS THAN (30)
       (SUBPARTITION R_RANGE3_SUB1 VALUES LESS THAN (10),
       SUBPARTITION R_RANGE3_SUB2 VALUES LESS THAN (30),
       SUBPARTITION R_RANGE3_SUB_MAX VALUES LESS THAN (MAXVALUE)
     ),
     PARTITION R_RANGE4 VALUES LESS THAN (40)
       (SUBPARTITION R_RANGE4_SUB1 VALUES LESS THAN (10),
       SUBPARTITION R_RANGE4_SUB2 VALUES LESS THAN (30),
       SUBPARTITION R_RANGE4_SUB_MAX VALUES LESS THAN (MAXVALUE)
     ),
     PARTITION R_RANGEMAX VALUES LESS THAN (MAXVALUE)
       (SUBPARTITION R_RANGEMAX_SUB1 VALUES LESS THAN (10),
       SUBPARTITION R_RANGEMAX_SUB2 VALUES LESS THAN (30),
       SUBPARTITION R_RANGEMAX_SUB_MAX VALUES LESS THAN (MAXVALUE)
     ))
     ENABLE ROW MOVEMENT;

Table 'DEPT_PART_RR' created.

 

Example Verification

SQL> set LINESIZE 150
SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col SUBPARTITION_NAME format a20
SQL> col SUBPARTITION_NO format 999,999
SQL> col BOUND format a10
SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_NO,
     BOUND FROM USER_TAB_SUBPARTITIONS
     WHERE TABLE_NAME = 'DEPT_PART_RR';
     
TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME    SUBPARTI BOUND
--------------- --------------- -------------------- -------- ----------
DEPT_PART_RR    R_RANGE1        R_RANGE1_SUB1               1 10
DEPT_PART_RR    R_RANGE1        R_RANGE1_SUB2               2 30
DEPT_PART_RR    R_RANGE1        R_RANGE1_SUB_MAX            3 MAXVALUE
DEPT_PART_RR    R_RANGE2        R_RANGE2_SUB1               1 10
DEPT_PART_RR    R_RANGE2        R_RANGE2_SUB2               2 30
DEPT_PART_RR    R_RANGE2        R_RANGE2_SUB_MAX            3 MAXVALUE
DEPT_PART_RR    R_RANGE3        R_RANGE3_SUB1               1 10
DEPT_PART_RR    R_RANGE3        R_RANGE3_SUB2               2 30
DEPT_PART_RR    R_RANGE3        R_RANGE3_SUB_MAX            3 MAXVALUE
DEPT_PART_RR    R_RANGE4        R_RANGE4_SUB1               1 10
DEPT_PART_RR    R_RANGE4        R_RANGE4_SUB2               2 30
DEPT_PART_RR    R_RANGE4        R_RANGE4_SUB_MAX            3 MAXVALUE
DEPT_PART_RR    R_RANGEMAX      R_RANGEMAX_SUB1             1 10
DEPT_PART_RR    R_RANGEMAX      R_RANGEMAX_SUB2             2 30
DEPT_PART_RR    R_RANGEMAX      R_RANGEMAX_SUB_MAX          3 MAXVALUE

15 rows selected.

 

Range-List Method

Example Creation

SQL> CREATE TABLE DEPT_PART_RL (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     PCTFREE 10
     INITRANS 2
     PARTITION BY RANGE(DEPTNO)
     SUBPARTITION BY LIST(MGRNO)
     (
       PARTITION R_LIST1 VALUES LESS THAN (10)
         (SUBPARTITION R_LIST1_SUB1 VALUES (10),
         SUBPARTITION R_LIST1_SUB2 VALUES (30)
       ),
       PARTITION R_LIST2 VALUES LESS THAN (20)
         (SUBPARTITION R_LIST2_SUB1 VALUES (10),
         SUBPARTITION R_LIST2_SUB2 VALUES (30)
       ),
       PARTITION R_LIST3 VALUES LESS THAN (30)
         (SUBPARTITION R_LIST3_SUB1 VALUES (10),
         SUBPARTITION R_LIST3_SUB2 VALUES (30)
       ),
       PARTITION R_LIST4 VALUES LESS THAN (40)
         (SUBPARTITION R_LIST4_SUB1 VALUES (10),
         SUBPARTITION R_LIST4_SUB2 VALUES (30)
       ),
       PARTITION R_LISTMAX VALUES LESS THAN (MAXVALUE)
         (SUBPARTITION R_LISTMAX_SUB1 VALUES (10),
         SUBPARTITION R_LISTMAX_SUB2 VALUES (30)
       ))
       ENABLE ROW MOVEMENT;

Table 'DEPT_PART_RL' created.

Example Verification

SQL> set LINESIZE 150
SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col SUBPARTITION_NAME format a20
SQL> col SUBPARTITION_NO format 999,999
SQL> col BOUND format a10
SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_NO,
     BOUND FROM USER_TAB_SUBPARTITIONS
     WHERE TABLE_NAME = 'DEPT_PART_RL';

TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME    SUBPARTI BOUND
--------------- --------------- -------------------- -------- ----------
DEPT_PART_RL    R_LIST1         R_LIST1_SUB1               1 10
DEPT_PART_RL    R_LIST1         R_LIST1_SUB2               2 30
DEPT_PART_RL    R_LIST2         R_LIST2_SUB1               1 10
DEPT_PART_RL    R_LIST2         R_LIST2_SUB2               2 30
DEPT_PART_RL    R_LIST3         R_LIST3_SUB1               1 10
DEPT_PART_RL    R_LIST3         R_LIST3_SUB2               2 30
DEPT_PART_RL    R_LIST4         R_LIST4_SUB1               1 10
DEPT_PART_RL    R_LIST4         R_LIST4_SUB2               2 30
DEPT_PART_RL    R_LISTMAX       R_LISTMAX_SUB1             1 10
DEPT_PART_RL    R_LISTMAX       R_LISTMAX_SUB2             2 30

10 rows selected.

 

Range-Hash Method

Example Creation

SQL> CREATE TABLE DEPT_PART_RH (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     PCTFREE 10
     INITRANS 2
     PARTITION BY RANGE(DEPTNO)
     SUBPARTITION BY HASH(MGRNO) (
         PARTITION R_HASH1 VALUES LESS THAN (10) SUBPARTITIONS 4,
         PARTITION R_HASH2 VALUES LESS THAN (20) SUBPARTITIONS 4,
         PARTITION R_HASH3 VALUES LESS THAN (30) SUBPARTITIONS 4,
         PARTITION R_HASH4 VALUES LESS THAN (40) SUBPARTITIONS 4,
         PARTITION R_HASHMAX VALUES LESS THAN (MAXVALUE) SUBPARTITIONS 4
     )
     ENABLE ROW MOVEMENT;

Table 'DEPT_PART_RH' created.

Example Verification

SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col SUBPARTITION_NO format 999,999,999
SQL> SELECT TABLE_NAME, PARTITION_NAME,
     SUBPARTITION_NO FROM USER_TAB_SUBPARTITIONS
     WHERE TABLE_NAME = 'DEPT_PART_RH';

TABLE_NAME      PARTITION_NAME  SUBPARTI
--------------- --------------- --------
DEPT_PART_RH    R_HASH1                1
DEPT_PART_RH    R_HASH1                2
DEPT_PART_RH    R_HASH1                3
DEPT_PART_RH    R_HASH1                4
DEPT_PART_RH    R_HASH2                1
DEPT_PART_RH    R_HASH2                2
DEPT_PART_RH    R_HASH2                3
DEPT_PART_RH    R_HASH2                4
DEPT_PART_RH    R_HASH3                1
DEPT_PART_RH    R_HASH3                2
DEPT_PART_RH    R_HASH3                3
DEPT_PART_RH    R_HASH3                4
DEPT_PART_RH    R_HASH4                1
DEPT_PART_RH    R_HASH4                2
DEPT_PART_RH    R_HASH4                3
DEPT_PART_RH    R_HASH4                4
DEPT_PART_RH    R_HASHMAX              1
DEPT_PART_RH    R_HASHMAX              2
DEPT_PART_RH    R_HASHMAX              3
DEPT_PART_RH    R_HASHMAX              4

20 rows selected.

 

List-Range Method

Example Creation

SQL> CREATE TABLE DEPT_PART_LR (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     PCTFREE 10
     INITRANS 2
     PARTITION BY LIST(DEPTNO)
     SUBPARTITION BY RANGE(MGRNO)
     (
       PARTITION L_RANGE1 VALUES (10)
         (SUBPARTITION L_RANGE1_SUB1 VALUES LESS THAN (10),
         SUBPARTITION L_RANGE1_SUB2 VALUES LESS THAN (30),
         SUBPARTITION L_RANGE1_SUB_MAX VALUES LESS THAN (MAXVALUE)
       ),
       PARTITION L_RANGE2 VALUES (20)
         (SUBPARTITION L_RANGE2_SUB1 VALUES LESS THAN (10),
         SUBPARTITION L_RANGE2_SUB2 VALUES LESS THAN (30),
         SUBPARTITION L_RANGE2_SUB_MAX VALUES LESS THAN (MAXVALUE)
       ),
       PARTITION L_RANGE3 VALUES (30)
         (SUBPARTITION L_RANGE3_SUB1 VALUES LESS THAN (10),
         SUBPARTITION L_RANGE3_SUB2 VALUES LESS THAN (30),
         SUBPARTITION L_RANGE3_SUB_MAX VALUES LESS THAN (MAXVALUE)
         ),
       PARTITION L_RANGE4 VALUES (40)
         (SUBPARTITION L_RANGE4_SUB1 VALUES LESS THAN (10),
         SUBPARTITION L_RANGE4_SUB2 VALUES LESS THAN (30),
         SUBPARTITION L_RANGE4_SUB_MAX VALUES LESS THAN (MAXVALUE)
       ))
       ENABLE ROW MOVEMENT;

Table 'DEPT_PART_LR' created.

Example Verification

SQL> set LINESIZE 150
SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col SUBPARTITION_NAME format a20
SQL> col SUBPARTITION_NO format 999,999
SQL> col BOUND format a10
SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_NO,
     BOUND FROM USER_TAB_SUBPARTITIONS
     WHERE TABLE_NAME = 'DEPT_PART_LR';


TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME    SUBPARTI BOUND
--------------- --------------- -------------------- -------- ----------
DEPT_PART_LR    L_RANGE1        L_RANGE1_SUB1               1 10
DEPT_PART_LR    L_RANGE1        L_RANGE1_SUB2               2 30
DEPT_PART_LR    L_RANGE1        L_RANGE1_SUB_MAX            3 MAXVALUE
DEPT_PART_LR    L_RANGE2        L_RANGE2_SUB1               1 10
DEPT_PART_LR    L_RANGE2        L_RANGE2_SUB2               2 30
DEPT_PART_LR    L_RANGE2        L_RANGE2_SUB_MAX            3 MAXVALUE
DEPT_PART_LR    L_RANGE3        L_RANGE3_SUB1               1 10
DEPT_PART_LR    L_RANGE3        L_RANGE3_SUB2               2 30
DEPT_PART_LR    L_RANGE3        L_RANGE3_SUB_MAX            3 MAXVALUE
DEPT_PART_LR    L_RANGE4        L_RANGE4_SUB1               1 10
DEPT_PART_LR    L_RANGE4        L_RANGE4_SUB2               2 30
DEPT_PART_LR    L_RANGE4        L_RANGE4_SUB_MAX            3 MAXVALUE

12 rows selected.

 

List-List Method

Example Creation

SQL> CREATE TABLE DEPT_PART_LL (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     PCTFREE 10
     INITRANS 2
     PARTITION BY LIST(DEPTNO)
     SUBPARTITION BY LIST(MGRNO)
     (
       PARTITION L_LIST1 VALUES (10)
       (SUBPARTITION L_LIST1_SUB1 VALUES (10),
       SUBPARTITION L_LIST1_SUB2 VALUES (30)
     ),
       PARTITION L_LIST2 VALUES (20)
       (SUBPARTITION L_LIST2_SUB1 VALUES (10),
       SUBPARTITION L_LIST2_SUB2 VALUES (30)
     ),
     PARTITION L_LIST3 VALUES (30)
       (SUBPARTITION L_LIST3_SUB1 VALUES (10),
       SUBPARTITION L_LIST3_SUB2 VALUES (30)
     ),
     PARTITION L_LIST4 VALUES (40)
       (SUBPARTITION L_LIST4_SUB1 VALUES (10),
       SUBPARTITION L_LIST4_SUB2 VALUES (30)
     ))
     ENABLE ROW MOVEMENT;

Table 'DEPT_PART_LL' created.

Example Verification

SQL> set LINESIZE 150
SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col SUBPARTITION_NAME format a20
SQL> col SUBPARTITION_NO format 999,999
SQL> col BOUND format a10
SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_NO,
     BOUND FROM USER_TAB_SUBPARTITIONS
     WHERE TABLE_NAME = 'DEPT_PART_LL';


TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME    SUBPARTI BOUND
--------------- --------------- -------------------- -------- ----------
DEPT_PART_LL    L_LIST1         L_LIST1_SUB1                1 10
DEPT_PART_LL    L_LIST1         L_LIST1_SUB2                2 30
DEPT_PART_LL    L_LIST2         L_LIST2_SUB1                1 10
DEPT_PART_LL    L_LIST2         L_LIST2_SUB2                2 30
DEPT_PART_LL    L_LIST3         L_LIST3_SUB1                1 10
DEPT_PART_LL    L_LIST3         L_LIST3_SUB2                2 30
DEPT_PART_LL    L_LIST4         L_LIST4_SUB1                1 10
DEPT_PART_LL    L_LIST4         L_LIST4_SUB2                2 30

8 rows selected.

 

List-Hash Method

Example Creation

SQL> CREATE TABLE DEPT_PART_LH (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     PCTFREE 10
     INITRANS 2
     PARTITION BY LIST(DEPTNO)
     SUBPARTITION BY HASH(MGRNO)
     (
       PARTITION L_HASH1 VALUES (10) SUBPARTITIONS 4,
       PARTITION L_HASH2 VALUES (20) SUBPARTITIONS 4,
       PARTITION L_HASH3 VALUES (30) SUBPARTITIONS 4,
       PARTITION L_HASH4 VALUES (40) SUBPARTITIONS 4
     )
     ENABLE ROW MOVEMENT;

Table 'DEPT_PART_LH' created.

Example Verification

SQL> set LINESIZE 150
SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col SUBPARTITION_NAME format a20
SQL> col SUBPARTITION_NO format 999,999
SQL> col BOUND format a10
SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_NO,
     BOUND FROM USER_TAB_SUBPARTITIONS
     WHERE TABLE_NAME = 'DEPT_PART_LH';

TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME    SUBPARTI BOUND
--------------- --------------- -------------------- -------- ----------
DEPT_PART_LH    L_HASH1         _TIBERO_SUBP500400          1
DEPT_PART_LH    L_HASH1         _TIBERO_SUBP500500          2
DEPT_PART_LH    L_HASH1         _TIBERO_SUBP500600          3
DEPT_PART_LH    L_HASH1         _TIBERO_SUBP500700          4
DEPT_PART_LH    L_HASH2         _TIBERO_SUBP500900          1
DEPT_PART_LH    L_HASH2         _TIBERO_SUBP501000          2
DEPT_PART_LH    L_HASH2         _TIBERO_SUBP501100          3
DEPT_PART_LH    L_HASH2         _TIBERO_SUBP501200          4
DEPT_PART_LH    L_HASH3         _TIBERO_SUBP501400          1
DEPT_PART_LH    L_HASH3         _TIBERO_SUBP501500          2
DEPT_PART_LH    L_HASH3         _TIBERO_SUBP501600          3
DEPT_PART_LH    L_HASH3         _TIBERO_SUBP501700          4
DEPT_PART_LH    L_HASH4         _TIBERO_SUBP501900          1
DEPT_PART_LH    L_HASH4         _TIBERO_SUBP502000          2
DEPT_PART_LH    L_HASH4         _TIBERO_SUBP502100          3
DEPT_PART_LH    L_HASH4         _TIBERO_SUBP502200          4


16 rows selected.

 

Hash-Range Method

Example Creation

SQL> CREATE TABLE DEPT_PART_HR (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     PCTFREE 10
     INITRANS 2
     PARTITION BY HASH(DEPTNO)
     SUBPARTITION BY RANGE(MGRNO)
     (
     PARTITION H_RANGE1
       (SUBPARTITION H_RANGE1_SUB1 VALUES LESS THAN (10),
       SUBPARTITION H_RANGE1_SUB2 VALUES LESS THAN (30),
       SUBPARTITION H_RANGE1_SUB_MAX VALUES LESS THAN (MAXVALUE)
     ),
     PARTITION H_RANGE2
       (SUBPARTITION H_RANGE2_SUB1 VALUES LESS THAN (10),
       SUBPARTITION H_RANGE2_SUB2 VALUES LESS THAN (30),
       SUBPARTITION H_RANGE2_SUB_MAX VALUES LESS THAN (MAXVALUE)
     ),
     PARTITION H_RANGE3
       (SUBPARTITION H_RANGE3_SUB1 VALUES LESS THAN (10),
       SUBPARTITION H_RANGE3_SUB2 VALUES LESS THAN (30),
       SUBPARTITION H_RANGE3_SUB_MAX VALUES LESS THAN (MAXVALUE)
     ),
     PARTITION H_RANGE4
       (SUBPARTITION H_RANGE4_SUB1 VALUES LESS THAN (10),
       SUBPARTITION H_RANGE4_SUB2 VALUES LESS THAN (30),
       SUBPARTITION H_RANGE4_SUB_MAX VALUES LESS THAN (MAXVALUE)
     ))
     ENABLE ROW MOVEMENT;

Table 'DEPT_PART_HR' created.

Example Verification

SQL> set LINESIZE 150
SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col SUBPARTITION_NAME format a20
SQL> col SUBPARTITION_NO format 999,999
SQL> col BOUND format a10
SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_NO,
     BOUND FROM USER_TAB_SUBPARTITIONS
     WHERE TABLE_NAME = 'DEPT_PART_HR';


TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME    SUBPARTI BOUND
--------------- --------------- -------------------- -------- ----------
DEPT_PART_HR    H_RANGE1        H_RANGE1_SUB1               1 10
DEPT_PART_HR    H_RANGE1        H_RANGE1_SUB2               2 30
DEPT_PART_HR    H_RANGE1        H_RANGE1_SUB_MAX            3 MAXVALUE
DEPT_PART_HR    H_RANGE2        H_RANGE2_SUB1               1 10
DEPT_PART_HR    H_RANGE2        H_RANGE2_SUB2               2 30
DEPT_PART_HR    H_RANGE2        H_RANGE2_SUB_MAX            3 MAXVALUE
DEPT_PART_HR    H_RANGE3        H_RANGE3_SUB1               1 10
DEPT_PART_HR    H_RANGE3        H_RANGE3_SUB2               2 30
DEPT_PART_HR    H_RANGE3        H_RANGE3_SUB_MAX            3 MAXVALUE
DEPT_PART_HR    H_RANGE4        H_RANGE4_SUB1               1 10
DEPT_PART_HR    H_RANGE4        H_RANGE4_SUB2               2 30
DEPT_PART_HR    H_RANGE4        H_RANGE4_SUB_MAX            3 MAXVALUE

12 rows selected.

 

Hash-List Method

Example Creation

SQL> CREATE TABLE DEPT_PART_HL (
       DEPTNO NUMBER(4),
       DEPTNAME VARCHAR(30 BYTE),
       MGRNO NUMBER(6),
       LOCNO NUMBER(4)
     )
     PCTFREE 10
     INITRANS 2
     PARTITION BY HASH(DEPTNO)
     SUBPARTITION BY LIST(MGRNO)
     (
     PARTITION H_LIST1
       (SUBPARTITION H_LIST1_SUB1 VALUES (10),
       SUBPARTITION H_LIST1_SUB2 VALUES (30)
     ),
     PARTITION H_LIST2
       (SUBPARTITION H_LIST2_SUB1 VALUES (10),
       SUBPARTITION H_LIST2_SUB2 VALUES (30)
     ),
     PARTITION H_LIST3
       (SUBPARTITION H_LIST3_SUB1 VALUES (10),
       SUBPARTITION H_LIST3_SUB2 VALUES (30)
     ),
     PARTITION H_LIST4
       (SUBPARTITION H_LIST4_SUB1 VALUES (10),
       SUBPARTITION H_LIST4_SUB2 VALUES (30)
     ))
     ENABLE ROW MOVEMENT;

Table 'DEPT_PART_HL' created.

Example Verification

SQL> set LINESIZE 150
SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col SUBPARTITION_NAME format a20
SQL> col SUBPARTITION_NO format 999,999
SQL> col BOUND format a10
SQL> SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_NO,
     BOUND FROM USER_TAB_SUBPARTITIONS
     WHERE TABLE_NAME = 'DEPT_PART_HL';
     
TABLE_NAME      PARTITION_NAME  SUBPARTITION_NAME    SUBPARTI BOUND
--------------- --------------- -------------------- -------- ----------
DEPT_PART_HL    H_LIST1         H_LIST1_SUB1                1 10
DEPT_PART_HL    H_LIST1         H_LIST1_SUB2                2 30
DEPT_PART_HL    H_LIST2         H_LIST2_SUB1                1 10
DEPT_PART_HL    H_LIST2         H_LIST2_SUB2                2 30
DEPT_PART_HL    H_LIST3         H_LIST3_SUB1                1 10
DEPT_PART_HL    H_LIST3         H_LIST3_SUB2                2 30
DEPT_PART_HL    H_LIST4         H_LIST4_SUB1                1 10
DEPT_PART_HL    H_LIST4         H_LIST4_SUB2                2 30

8 rows selected.

 

Partition Table Management

Drop Partition

Usage

ALTER TABLE <table_name> DROP PARTITION <partition_name>

Example

SQL> ALTER TABLE DEPT_PART_R DROP PARTITION PARTMAX;

Table 'DEPT_PART_R' altered.

 

Add Partition

Usage

ALTER TABLE <table_name> ADD PARTITION <partition_name> VALUES LESS THAN (<value>)

Example

SQL> ALTER TABLE DEPT_PART_R ADD PARTITION PART6 VALUES LESS THAN (61);

Table 'DEPT_PART_R' altered.

 

Split Partition

Usage

ALTER TABLE <table_name> SPLIT PARTITION <partition_to_split> AT (<value>)
INTO (<partition_name_for_values_less_than>, <partition_name_for_values_greater_or_equal>)

Example

SQL> ALTER TABLE DEPT_PART_R SPLIT PARTITION PART6 AT (51)
INTO (PARTITION PART5, PARTITION PART6);

Table 'DEPT_PART_R' altered.

 

Rename Partition

Usage

ALTER TABLE <table_name> RENAME PARTITION <partition_name> TO <new_partition_name>

Example

SQL> ALTER TABLE DEPT_PART_R RENAME PARTITION PART1 TO PART1_NEW;

Table 'DEPT_PART_R' altered.

 

Change Partition Tablespace

Usage

ALTER TABLE <table_name> MOVE PARTITION <partition_name> TABLESPACE <tablespace_name>

Example

SQL> ALTER TABLE DEPT_PART_R MOVE PARTITION PART1 TABLESPACE NEW_TS;

Table 'DEPT_PART_R' altered.

 

Truncate Partition

Usage

ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>

Example

SQL> ALTER TABLE DEPT_PART_R TRUNCATE PARTITION PART2;

Table 'DEPT_PART_R' altered.

 

 

Related to