Creating a Range Partitioned Table

View All Scripts Login to Run Script

Script Name Creating a Range Partitioned Table
Description Administering range partitioning
Area Partitioning
Referenced In Database VLDB and Partitioning Guide
Contributor Oracle
Created Monday October 05, 2015

Statement 1

The table is partitioned by range using the values of the sales_date column. The partition bound is determined by the VALUES LESS THAN clause. For example, a value for sales_date that is less than 01-OCT-2014 would be stored in the sales_q3_2014 partition.

Create the SALES_RANGE_PARTITION table

CREATE TABLE sales_range_partition ( 
    product_id       NUMBER(6), 
    customer_id      NUMBER, 
    channel_id       CHAR(1), 
    promo_id         NUMBER(6), 
    sale_date        DATE, 
    quantity_sold    INTEGER, 
    amount_sold      NUMBER(10,2) 
) 

   PARTITION BY RANGE (sale_date) 
(  
   PARTITION sales_q1_2014 VALUES LESS THAN (TO_DATE('01-APR-2014','dd-MON-yyyy')), 
   PARTITION sales_q2_2014 VALUES LESS THAN (TO_DATE('01-JUL-2014','dd-MON-yyyy')), 
   PARTITION sales_q3_2014 VALUES LESS THAN (TO_DATE('01-OCT-2014','dd-MON-yyyy')), 
   PARTITION sales_q4_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','dd-MON-yyyy')) 
)

Table created.

Statement 2

Display the partitions in the table with this SQL query.

SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_RANGE_PARTITION'

 
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
SALES_RANGE_PARTITION SALES_Q1_2014 1 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION SALES_Q2_2014 2 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION SALES_Q3_2014 3 TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION SALES_Q4_2014 4 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

4 rows selected.

TABLE_NAME	PARTITION_NAME	PARTITION_POSITION	HIGH_VALUE
SALES_RANGE_PARTITION	SALES_Q1_2014	1	TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION	SALES_Q2_2014	2	TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION	SALES_Q3_2014	3	TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION	SALES_Q4_2014	4	TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Statement 3

Add a new partition to the table.

ALTER TABLE sales_range_partition 
      ADD PARTITION sales_q1_2015 VALUES LESS THAN (TO_DATE('01-APR-2015','dd-MON-yyyy'))

Table altered.

Statement 4

Display the partitions in the table after adding the new partition.

SELECT TABLE_NAME,PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_RANGE_PARTITION'

 
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
SALES_RANGE_PARTITION SALES_Q1_2014 1 TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION SALES_Q2_2014 2 TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION SALES_Q3_2014 3 TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION SALES_Q4_2014 4 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION SALES_Q1_2015 5 TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

5 rows selected.

TABLE_NAME	PARTITION_NAME	PARTITION_POSITION	HIGH_VALUE
SALES_RANGE_PARTITION	SALES_Q1_2014	1	TO_DATE(' 2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION	SALES_Q2_2014	2	TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION	SALES_Q3_2014	3	TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION	SALES_Q4_2014	4	TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SALES_RANGE_PARTITION	SALES_Q1_2015	5	TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Statement 5

Insert values into the table.

INSERT INTO sales_range_partition VALUES (1001,100,'A',150,'10-FEB-2014',500,2000)

1 row(s) inserted.

Statement 6

Insert values into the table.

INSERT INTO sales_range_partition VALUES (1002,110,'B',180,'15-JUN-2014',100,1000)

1 row(s) inserted.

Statement 7

Insert values into the table.

INSERT INTO sales_range_partition VALUES (1001,100,'A',150,'20-AUG-2014',500,2000)

1 row(s) inserted.

Statement 8

Display all the data in the table.

SELECT * FROM sales_range_partition

 
PRODUCT_ID CUSTOMER_ID CHANNEL_ID PROMO_ID SALE_DATE QUANTITY_SOLD AMOUNT_SOLD
1001 100 A 150 10-FEB-14 500 2000
1002 110 B 180 15-JUN-14 100 1000
1001 100 A 150 20-AUG-14 500 2000

3 rows selected.

Statement 9
Display data from a specified partition in the table. When you are finished testing the example, you can clean up the environment by dropping the table (DROP TABLE sales_range_partition).
```
SELECT * FROM sales_range_partition PARTITION(sales_q3_2014)
```
```
 
PRODUCT_ID CUSTOMER_ID CHANNEL_ID PROMO_ID SALE_DATE QUANTITY_SOLD AMOUNT_SOLD
1001 100 A 150 20-AUG-14 500 2000
```

PRODUCT_ID	CUSTOMER_ID	CHANNEL_ID	PROMO_ID	SALE_DATE	QUANTITY_SOLD	AMOUNT_SOLD
1001	100	A	150	10-FEB-14	500	2000
1002	110	B	180	15-JUN-14	100	1000
1001	100	A	150	20-AUG-14	500	2000

PRODUCT_ID	CUSTOMER_ID	CHANNEL_ID	PROMO_ID	SALE_DATE	QUANTITY_SOLD	AMOUNT_SOLD
1001	100	A	150	20-AUG-14	500	2000

Additional Information

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