Creating a List Partitioned Table

View All Scripts Login to Run Script

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

Statement 1

The table is partitioned by list using the values of the state_code column. Partitioning is determined by specifying a list of discrete values for the partitioning key. For example, a value for state_code that is equal to CT would be stored in the region_east partition. List partitioning enables you to group and organize unordered and unrelated sets of data in a natural way.

Create the SALES_BY_REGION table

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

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_BY_REGION'

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

4 rows selected.

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

Statement 3

Add a new partition to the table.

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

Table altered.

Statement 4

Add a new partition to the table for NULL values.

ALTER TABLE sales_by_region 
   ADD PARTITION region_null VALUES (NULL)

Table altered.

Statement 5
Add a new partition to the table for values that do not map to any other partition.
```
ALTER TABLE sales_by_region 
   ADD PARTITION VALUES (DEFAULT)
```
```
Table altered.
```

Statement 6

Display the partitions in the table after adding new partitions.

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 'CT', 'MA', 'MD', 'ME', 'NH', 'NJ', 'NY', 'PA', 'VA'
SALES_BY_REGION REGION_WEST 2 'AZ', 'CA', 'CO', 'NM', 'NV', 'OR', 'UT', 'WA'
SALES_BY_REGION REGION_SOUTH 3 'AL', 'AR', 'GA', 'KY', 'LA', 'MS', 'TN', 'TX'
SALES_BY_REGION REGION_CENTRAL 4 'IA', 'IL', 'MO', 'MI', 'ND', 'OH', 'SD'
SALES_BY_REGION REGION_NONMAINLAND 5 'HI', 'PR'
SALES_BY_REGION REGION_NULL 6 NULL
SALES_BY_REGION SYS_P442 7 DEFAULT

7 rows selected.

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

Statement 7

Add new values to a list of partitioning keys.

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

Table altered.

Statement 8

Display the partitions in the table after modifying a partition.

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 'CT', 'MA', 'MD', 'ME', 'NH', 'NJ', 'NY', 'PA', 'VA'
SALES_BY_REGION REGION_WEST 2 'AZ', 'CA', 'CO', 'NM', 'NV', 'OR', 'UT', 'WA'
SALES_BY_REGION REGION_SOUTH 3 'AL', 'AR', 'GA', 'KY', 'LA', 'MS', 'TN', 'TX'
SALES_BY_REGION REGION_CENTRAL 4 'IA', 'IL', 'MO', 'MI', 'ND', 'OH', 'SD', 'OK', 'KS'
SALES_BY_REGION REGION_NONMAINLAND 5 'HI', 'PR'
SALES_BY_REGION REGION_NULL 6 NULL
SALES_BY_REGION SYS_P442 7 DEFAULT

7 rows selected.

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

Statement 9

Insert values into the table.

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

1 row(s) inserted.

Statement 10

Insert values into the table.

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

1 row(s) inserted.

Statement 11

Display all the data in the table.

SELECT * FROM sales_by_region

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

2 rows selected.

Statement 12
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_by_region).
```
SELECT * FROM sales_by_region PARTITION(region_east)
```
```
 
PRODUCT_ID QUANTITY_SOLD SALE_DATE STORE_NAME STATE_CODE
1001 100 25-AUG-14 My Store MA MA
```

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

PRODUCT_ID	QUANTITY_SOLD	SALE_DATE	STORE_NAME	STATE_CODE
1001	100	25-AUG-14	My Store MA	MA

Additional Information

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