List-Partition Tables

View All Scripts Login to Run Script

Script Name List-Partition Tables
Description This example creates and modifies a list-partitioned table. The partitions are created as groups of states.
Area Partitioning
Contributor Mike Hichwa (Oracle)
Created Monday October 05, 2015

Statement 1

CREATE TABLE sales_by_region ( 
             item_id INTEGER,  
             item_quantity INTEGER,  
             store_name VARCHAR(30),  
             state_code VARCHAR(2), 
             sale_date DATE) 
     PARTITION BY LIST (state_code)  
     ( 
     PARTITION region_east 
        VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ'), 
     PARTITION region_west 
        VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO'), 
     PARTITION region_south 
        VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'), 
     PARTITION region_central  
        VALUES ('OH','ND','SD','MO','IL','MI','IA') 
     )

Table created.

Statement 2

This SQL query displays the partitions in the specified table.

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

 
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
SALES_BY_REGION REGION_EAST 1 'MA', 'NY', 'CT', 'NH', 'ME', 'MD', 'VA', 'PA', 'NJ'
SALES_BY_REGION REGION_WEST 2 'CA', 'AZ', 'NM', 'OR', 'WA', 'UT', 'NV', 'CO'
SALES_BY_REGION REGION_SOUTH 3 'TX', 'KY', 'TN', 'LA', 'MS', 'AR', 'AL', 'GA'
SALES_BY_REGION REGION_CENTRAL 4 'OH', 'ND', 'SD', 'MO', 'IL', 'MI', 'IA'

4 rows selected.

TABLE_NAME	PARTITION_NAME	PARTITION_POSITION	HIGH_VALUE
SALES_BY_REGION	REGION_EAST	1	'MA', 'NY', 'CT', 'NH', 'ME', 'MD', 'VA', 'PA', 'NJ'
SALES_BY_REGION	REGION_WEST	2	'CA', 'AZ', 'NM', 'OR', 'WA', 'UT', 'NV', 'CO'
SALES_BY_REGION	REGION_SOUTH	3	'TX', 'KY', 'TN', 'LA', 'MS', 'AR', 'AL', 'GA'
SALES_BY_REGION	REGION_CENTRAL	4	'OH', 'ND', 'SD', 'MO', 'IL', 'MI', 'IA'

Statement 3

Add a new partition

ALTER TABLE sales_by_region  
   ADD PARTITION region_nonmainland VALUES ('HI', 'PR')

Table altered.

Statement 4

Add a null partition

ALTER TABLE sales_by_region  
   ADD PARTITION region_null VALUES (NULL)

Table altered.

Statement 5

Add a default partition

ALTER TABLE sales_by_region  
   ADD PARTITION VALUES (DEFAULT)

Table altered.

Statement 6

Again display the partitions in the specified table.

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

 
TABLE_NAME PARTITION_NAME PARTITION_POSITION HIGH_VALUE
SALES_BY_REGION REGION_EAST 1 'MA', 'NY', 'CT', 'NH', 'ME', 'MD', 'VA', 'PA', 'NJ'
SALES_BY_REGION REGION_WEST 2 'CA', 'AZ', 'NM', 'OR', 'WA', 'UT', 'NV', 'CO'
SALES_BY_REGION REGION_SOUTH 3 'TX', 'KY', 'TN', 'LA', 'MS', 'AR', 'AL', 'GA'
SALES_BY_REGION REGION_CENTRAL 4 'OH', 'ND', 'SD', 'MO', 'IL', 'MI', 'IA'
SALES_BY_REGION REGION_NONMAINLAND 5 'HI', 'PR'
SALES_BY_REGION REGION_NULL 6 NULL
SALES_BY_REGION SYS_P449 7 DEFAULT

7 rows selected.

TABLE_NAME	PARTITION_NAME	PARTITION_POSITION	HIGH_VALUE
SALES_BY_REGION	REGION_EAST	1	'MA', 'NY', 'CT', 'NH', 'ME', 'MD', 'VA', 'PA', 'NJ'
SALES_BY_REGION	REGION_WEST	2	'CA', 'AZ', 'NM', 'OR', 'WA', 'UT', 'NV', 'CO'
SALES_BY_REGION	REGION_SOUTH	3	'TX', 'KY', 'TN', 'LA', 'MS', 'AR', 'AL', 'GA'
SALES_BY_REGION	REGION_CENTRAL	4	'OH', 'ND', 'SD', 'MO', 'IL', 'MI', 'IA'
SALES_BY_REGION	REGION_NONMAINLAND	5	'HI', 'PR'
SALES_BY_REGION	REGION_NULL	6	NULL
SALES_BY_REGION	SYS_P449	7	DEFAULT

Statement 7

ALTER TABLE sales_by_region 
   MODIFY PARTITION region_south 
      ADD VALUES ('OK', 'KS')

Table altered.

Statement 8

Insert values in the specified table.

INSERT INTO sales_by_region VALUES (1001,100,'My Store MA','MA','25-AUG-2014')

1 row(s) inserted.

Statement 9

INSERT INTO sales_by_region VALUES (1002,200,'My Store OK','OK','26-AUG-2014')

1 row(s) inserted.

Statement 10

Display data in the specified table.

SELECT * FROM sales_by_region

 
ITEM_ID ITEM_QUANTITY STORE_NAME STATE_CODE SALE_DATE
1001 100 My Store MA MA 25-AUG-14
1002 200 My Store OK OK 26-AUG-14

2 rows selected.

ITEM_ID	ITEM_QUANTITY	STORE_NAME	STATE_CODE	SALE_DATE
1001	100	My Store MA	MA	25-AUG-14
1002	200	My Store OK	OK	26-AUG-14

Statement 11

SELECT * FROM sales_by_region PARTITION(region_east)

 
ITEM_ID ITEM_QUANTITY STORE_NAME STATE_CODE SALE_DATE
1001 100 My Store MA MA 25-AUG-14

ITEM_ID	ITEM_QUANTITY	STORE_NAME	STATE_CODE	SALE_DATE
1001	100	My Store MA	MA	25-AUG-14

Additional Information

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