range-partition tables TEST

View All Scripts Login to Run Script

Script Name range-partition tables TEST
Description This example creates and modifies a range-partitioned table. One partition is created for each quarter of the sales year.
Area Partitioning
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

The values of time_id constitute the partitioning key of a specific row. The VALUES LESS THAN clause determines the partition bound. Rows with partitioning key values that compare less than the ordered list of values specified by the clause are stored in the partition.

CREATE TABLE sales_range_partition 
  ( prod_id       NUMBER(6), 
    cust_id       NUMBER, 
    time_id       DATE,     /* time_id is the partitioning column */ 
    channel_id    CHAR(1), 
    promo_id      NUMBER(6), 
    quantity_sold NUMBER(3), 
    amount_sold   NUMBER(10,2) 
  ) 
   PARTITION BY RANGE (time_id) 
 ( 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

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

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

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 INTO sales_range_partition VALUES (1001,100,'10-FEB-2014','A',150,500,2000)

1 row(s) inserted.

Statement 6

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

1 row(s) inserted.

Statement 7

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

1 row(s) inserted.

Statement 8

SELECT * FROM sales_range_partition

 
PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
1001 100 10-FEB-14 A 150 500 2000
1002 110 15-JUN-14 B 180 100 1000
1001 100 20-AUG-14 A 150 500 2000

3 rows selected.

PROD_ID	CUST_ID	TIME_ID	CHANNEL_ID	PROMO_ID	QUANTITY_SOLD	AMOUNT_SOLD
1001	100	10-FEB-14	A	150	500	2000
1002	110	15-JUN-14	B	180	100	1000
1001	100	20-AUG-14	A	150	500	2000

Statement 9

SELECT * FROM sales_range_partition PARTITION(sales_q3_2014)

 
PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
1001 100 20-AUG-14 A 150 500 2000

PROD_ID	CUST_ID	TIME_ID	CHANNEL_ID	PROMO_ID	QUANTITY_SOLD	AMOUNT_SOLD
1001	100	20-AUG-14	A	150	500	2000

Statement 10

DROP TABLE sales_range_partition

Table dropped.

Additional Information

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