Create and Manage Partition Table in Oracle

Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data.

Partitioning Key: Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key consists of one or more columns that determine the partition where each row is stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition with the partitioning key.

Partitioned Tables:

This sections contains the following topics:

When to Partition a Table
When to Partition an Index

When to Partition a Table:

Here are some suggestions for situations when you should consider partitioning a table:

Tables that are greater than 2 GB: These tables should always be considered as candidates for partitioning.
Tables that contain historical data, in which new data is added into the newest partition: A typical example is a historical table where only the current month’s data is updatable and the other 11 months are read only.
Tables whose contents must be distributed across different types of storage devices.

When to Partition an Index:

Here are some suggestions for when to consider partitioning an index:

Avoid index maintenance when data is removed.
Perform maintenance on parts of the data without invalidating the entire index.
Reduce the effect of index skew caused by an index on a column with a monotonically increasing value.

Type of Partitions:

Range Partition
List Partition
Hash Partition
Interval Partition
Reference Partition
Composite Partition
Using Multi-column Partition
Virtual Column Based Partitioning

Range Partition:

Example of Creating Range Partition:

CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','dd-MON-yyyy')) TABLESPACE tbs_sales_2017
, PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy')) TABLESPACE tbs_sales_2017
, PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','dd-MON-yyyy')) TABLESPACE tbs_sales_2017
, PARTITION sales_2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','dd-MON-yyyy')) TABLESPACE tbs_sales_2017)
ENABLE ROW MOVEMENT;

List Partition:

Example of Creating List Partition:

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

Hash Partition:

Example of Creating Hash Partition:

CREATE TABLE TBL_Hash_Partition
(id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (id)
PARTITIONS 4
STORE IN (part1, part2, part3, part4);

Interval Partition:

CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')));

Reference 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_2019 VALUES LESS THAN (TO_DATE('01-APR-2019','DD-MON-YYYY')),
PARTITION Q2_2019 VALUES LESS THAN (TO_DATE('01-JUL-2019','DD-MON-YYYY')),
PARTITION Q3_2019 VALUES LESS THAN (TO_DATE('01-OCT-2019','DD-MON-YYYY')),
PARTITION Q4_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','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);

Composite Partition:

Composite Partition:
- Range-hash partitioning
- Range-list partitioning
- Range-range partitioning
- List-range partitioning
- List-hash partitioning
- List-list partitioning
- Hash-hash partitioning
- Interval-list partitioning
- Interval-range partitioning
- Interval-hash partitioning

Range-Hash partitioning:

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 (tbs_q1_2019, tbs_q2_2019, tbs_q3_2019, tbs_q4_2019)
 ( PARTITION sales_q1_2019 VALUES LESS THAN (TO_DATE('01-APR-2019','dd-MON-yyyy'))
 , PARTITION sales_q2_2019 VALUES LESS THAN (TO_DATE('01-JUL-2019','dd-MON-yyyy'))
 , PARTITION sales_q3_2019 VALUES LESS THAN (TO_DATE('01-OCT-2019','dd-MON-yyyy'))
 , PARTITION sales_q4_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','dd-MON-yyyy'))
 );

Range-List Partitioning:

CREATE TABLE sales
(deptno number, 
item_no varchar2(20),
txn_date date, 
txn_amount number, 
state varchar2(2)) TABLESPACE ts_sales
  PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
      (PARTITION 2017 VALUES LESS THAN (TO_DATE('1-JAN-2018','DD-MON-YYYY'))
         (SUBPARTITION sales_2017_northwest VALUES ('OR', 'WA'),
          SUBPARTITION sales_2017_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION sales_2017_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION sales_2017_southeast VALUES ('FL', 'GA'),
          SUBPARTITION sales_2017_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION sales_2017_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION 2018 VALUES LESS THAN ( TO_DATE('1-JAN-2019','DD-MON-YYYY'))
         (SUBPARTITION sales_2018_northwest VALUES ('OR', 'WA'),
          SUBPARTITION sales_2018_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION sales_2018_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION sales_2018_southeast VALUES ('FL', 'GA'),
          SUBPARTITION sales_2018_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION sales_2018_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION 2019 VALUES LESS THAN (TO_DATE('1-JAN-2020','DD-MON-YYYY'))
         (SUBPARTITION sales_2019_northwest VALUES ('OR', 'WA'),
          SUBPARTITION sales_2019_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION sales_2019_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION sales_2019_southeast VALUES ('FL', 'GA'),
          SUBPARTITION sales_2019_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION sales_2019_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION 2020 VALUES LESS THAN ( TO_DATE('1-JAN-2021','DD-MON-YYYY'))
         (SUBPARTITION sales_2020_northwest VALUES ('OR', 'WA'),
          SUBPARTITION sales_2020_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION sales_2020_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION sales_2020_southeast VALUES ('FL', 'GA'),
          SUBPARTITION sales_2020_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION sales_2020_southcentral VALUES ('OK', 'TX')
         )
      );

Range-Range Partitioning:

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

List-Range Partitioning:

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;

List-Hash Partitioning:

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

List-List Partitioning:

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

Hash-Hash Partitioning:

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

Interval-List Partitioning:

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-Range Partitioning:

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;

Interval-Hash Partitioning:

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;

Using Multi-Column Partition:

Virtual Column Based Partitioning:

Create and Manage Partition Table in Oracle

About Me

Categories

Archives

VISITORS