Modifying the Partitioning Strategy of a Table

View All Scripts Login to Run Script

Script Name Modifying the Partitioning Strategy of a Table
Description Converting a range partitioned table to a composite range-hash partitioned table online
Area Partitioning
Referenced In Database VLDB and Partitioning Guide
Contributor Oracle
Created Wednesday January 16, 2019

Statement 1

Create the mod_sales_partitioning table as a range-partitioned table. Then create indexes and insert data.

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

Table created.

Statement 2

CREATE INDEX i1_cust_id_indx ON mod_sales_partitioning (cust_id) LOCAL

Index created.

Statement 3

CREATE INDEX i2_time_id_indx ON mod_sales_partitioning (time_id)

Index created.

Statement 4

CREATE INDEX i3_prod_id_indx ON mod_sales_partitioning (prod_id)

Index created.

Statement 5

INSERT INTO mod_sales_partitioning VALUES (1001, 100, '10-FEB-17', 10, 15, 500, 7500.00)

1 row(s) inserted.

Statement 6

INSERT INTO mod_sales_partitioning VALUES (1002, 110, '15-JUN-17', 12, 18, 100, 3200.00)

1 row(s) inserted.

Statement 7

INSERT INTO mod_sales_partitioning VALUES (1001, 100, '20-AUG-17', 10, 15, 500, 7500.00)

1 row(s) inserted.

Statement 8

INSERT INTO mod_sales_partitioning VALUES (2105, 101, '15-FEB-17', 12, 19,  10,  300.00)

1 row(s) inserted.

Statement 9

INSERT INTO mod_sales_partitioning VALUES (2105, 102, '21-APR-17', 18, 12, 100, 2000.00)

1 row(s) inserted.

Statement 10

INSERT INTO mod_sales_partitioning values (1200, 155, '31-MAY-17', 20, 20, 300, 3600.00)

1 row(s) inserted.

Statement 11

INSERT INTO mod_sales_partitioning values (1400, 165, '31-MAY-17', 22, 15, 100, 4000.00)

1 row(s) inserted.

Statement 12

INSERT INTO mod_sales_partitioning VALUES (2105, 125, '05-OCT-17', 12, 16,  40, 8500.00)

1 row(s) inserted.

Statement 13

INSERT INTO mod_sales_partitioning VALUES (2105, 302, '15-DEC-17', 10, 11,  75, 4350.00)

1 row(s) inserted.

Statement 14
```
COMMIT
```
```
Statement processed.
```

Statement 15

Verify the partitioning strategy of the table.

SELECT TABLE_NAME, PARTITIONING_TYPE FROM USER_PART_TABLES WHERE TABLE_NAME ='MOD_SALES_PARTITIONING'


TABLE_NAME PARTITIONING_TYPE
MOD_SALES_PARTITIONING RANGE

TABLE_NAME	PARTITIONING_TYPE
MOD_SALES_PARTITIONING	RANGE

Statement 16

Display the partitions in the table.

SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='MOD_SALES_PARTITIONING'


TABLE_NAME PARTITION_NAME
MOD_SALES_PARTITIONING SALES_Q1_2017
MOD_SALES_PARTITIONING SALES_Q2_2017
MOD_SALES_PARTITIONING SALES_Q3_2017
MOD_SALES_PARTITIONING SALES_Q4_2017

4 rows selected.

TABLE_NAME	PARTITION_NAME
MOD_SALES_PARTITIONING	SALES_Q1_2017
MOD_SALES_PARTITIONING	SALES_Q2_2017
MOD_SALES_PARTITIONING	SALES_Q3_2017
MOD_SALES_PARTITIONING	SALES_Q4_2017

Statement 17

Alter the mod_sales_partitioning table to a composite range-hash partitioned table.

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

Table altered.

Statement 18

Verify that the partitioning strategy has been modified.

SELECT TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE FROM USER_PART_TABLES WHERE TABLE_NAME ='MOD_SALES_PARTITIONING'


TABLE_NAME PARTITIONING_TYPE SUBPARTITIONING_TYPE
MOD_SALES_PARTITIONING RANGE HASH

TABLE_NAME	PARTITIONING_TYPE	SUBPARTITIONING_TYPE
MOD_SALES_PARTITIONING	RANGE	HASH

Statement 19

Display the partitions and subpartitions in the table.

SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME ='MOD_SALES_PARTITIONING'


TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
MOD_SALES_PARTITIONING SALES_Q1_2017 SYS_SUBP45481
MOD_SALES_PARTITIONING SALES_Q1_2017 SYS_SUBP45482
MOD_SALES_PARTITIONING SALES_Q1_2017 SYS_SUBP45483
MOD_SALES_PARTITIONING SALES_Q1_2017 SYS_SUBP45484
MOD_SALES_PARTITIONING SALES_Q1_2017 SYS_SUBP45485
MOD_SALES_PARTITIONING SALES_Q1_2017 SYS_SUBP45486
MOD_SALES_PARTITIONING SALES_Q1_2017 SYS_SUBP45487
MOD_SALES_PARTITIONING SALES_Q1_2017 SYS_SUBP45488
MOD_SALES_PARTITIONING SALES_Q2_2017 SYS_SUBP45489
MOD_SALES_PARTITIONING SALES_Q2_2017 SYS_SUBP45490
MOD_SALES_PARTITIONING SALES_Q2_2017 SYS_SUBP45491
MOD_SALES_PARTITIONING SALES_Q2_2017 SYS_SUBP45492
MOD_SALES_PARTITIONING SALES_Q2_2017 SYS_SUBP45493
MOD_SALES_PARTITIONING SALES_Q2_2017 SYS_SUBP45494
MOD_SALES_PARTITIONING SALES_Q2_2017 SYS_SUBP45495
MOD_SALES_PARTITIONING SALES_Q2_2017 SYS_SUBP45496
MOD_SALES_PARTITIONING SALES_Q3_2017 SYS_SUBP45497
MOD_SALES_PARTITIONING SALES_Q3_2017 SYS_SUBP45498
MOD_SALES_PARTITIONING SALES_Q3_2017 SYS_SUBP45499
MOD_SALES_PARTITIONING SALES_Q3_2017 SYS_SUBP45500
MOD_SALES_PARTITIONING SALES_Q3_2017 SYS_SUBP45501
MOD_SALES_PARTITIONING SALES_Q3_2017 SYS_SUBP45502
MOD_SALES_PARTITIONING SALES_Q3_2017 SYS_SUBP45503
MOD_SALES_PARTITIONING SALES_Q3_2017 SYS_SUBP45504
MOD_SALES_PARTITIONING SALES_Q4_2017 SYS_SUBP45505
MOD_SALES_PARTITIONING SALES_Q4_2017 SYS_SUBP45506
MOD_SALES_PARTITIONING SALES_Q4_2017 SYS_SUBP45507
MOD_SALES_PARTITIONING SALES_Q4_2017 SYS_SUBP45508
MOD_SALES_PARTITIONING SALES_Q4_2017 SYS_SUBP45509
MOD_SALES_PARTITIONING SALES_Q4_2017 SYS_SUBP45510
MOD_SALES_PARTITIONING SALES_Q4_2017 SYS_SUBP45511
MOD_SALES_PARTITIONING SALES_Q4_2017 SYS_SUBP45512

32 rows selected.

TABLE_NAME	PARTITION_NAME	SUBPARTITION_NAME
MOD_SALES_PARTITIONING	SALES_Q1_2017	SYS_SUBP45481
MOD_SALES_PARTITIONING	SALES_Q1_2017	SYS_SUBP45482
MOD_SALES_PARTITIONING	SALES_Q1_2017	SYS_SUBP45483
MOD_SALES_PARTITIONING	SALES_Q1_2017	SYS_SUBP45484
MOD_SALES_PARTITIONING	SALES_Q1_2017	SYS_SUBP45485
MOD_SALES_PARTITIONING	SALES_Q1_2017	SYS_SUBP45486
MOD_SALES_PARTITIONING	SALES_Q1_2017	SYS_SUBP45487
MOD_SALES_PARTITIONING	SALES_Q1_2017	SYS_SUBP45488
MOD_SALES_PARTITIONING	SALES_Q2_2017	SYS_SUBP45489
MOD_SALES_PARTITIONING	SALES_Q2_2017	SYS_SUBP45490
MOD_SALES_PARTITIONING	SALES_Q2_2017	SYS_SUBP45491
MOD_SALES_PARTITIONING	SALES_Q2_2017	SYS_SUBP45492
MOD_SALES_PARTITIONING	SALES_Q2_2017	SYS_SUBP45493
MOD_SALES_PARTITIONING	SALES_Q2_2017	SYS_SUBP45494
MOD_SALES_PARTITIONING	SALES_Q2_2017	SYS_SUBP45495
MOD_SALES_PARTITIONING	SALES_Q2_2017	SYS_SUBP45496
MOD_SALES_PARTITIONING	SALES_Q3_2017	SYS_SUBP45497
MOD_SALES_PARTITIONING	SALES_Q3_2017	SYS_SUBP45498
MOD_SALES_PARTITIONING	SALES_Q3_2017	SYS_SUBP45499
MOD_SALES_PARTITIONING	SALES_Q3_2017	SYS_SUBP45500
MOD_SALES_PARTITIONING	SALES_Q3_2017	SYS_SUBP45501
MOD_SALES_PARTITIONING	SALES_Q3_2017	SYS_SUBP45502
MOD_SALES_PARTITIONING	SALES_Q3_2017	SYS_SUBP45503
MOD_SALES_PARTITIONING	SALES_Q3_2017	SYS_SUBP45504
MOD_SALES_PARTITIONING	SALES_Q4_2017	SYS_SUBP45505
MOD_SALES_PARTITIONING	SALES_Q4_2017	SYS_SUBP45506
MOD_SALES_PARTITIONING	SALES_Q4_2017	SYS_SUBP45507
MOD_SALES_PARTITIONING	SALES_Q4_2017	SYS_SUBP45508
MOD_SALES_PARTITIONING	SALES_Q4_2017	SYS_SUBP45509
MOD_SALES_PARTITIONING	SALES_Q4_2017	SYS_SUBP45510
MOD_SALES_PARTITIONING	SALES_Q4_2017	SYS_SUBP45511
MOD_SALES_PARTITIONING	SALES_Q4_2017	SYS_SUBP45512

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database