Creating a Multicolumn List-Partitioned Table

View All Scripts Login to Run Script

Script Name Creating a Multicolumn List-Partitioned Table
Description Administering multicolumn list-partitioned tables
Area Partitioning
Referenced In Database VLDB and Partitioning Guide
Contributor Oracle
Created Wednesday January 23, 2019

Statement 1

Create a multicolumn list-partitioned table on state and channel

CREATE TABLE sales_by_region_and_channel  
 (dept_number      NUMBER NOT NULL,   
  dept_name        VARCHAR2(20),  
  quarterly_sales  NUMBER(10,2),  
  state            VARCHAR2(2),  
  channel          VARCHAR2(1)  
 )  
 PARTITION BY LIST (state, channel)  
 (  
  PARTITION yearly_west_direct VALUES (('OR','D'),('UT','D'),('WA','D')),  
  PARTITION yearly_west_indirect VALUES (('OR','I'),('UT','I'),('WA','I')),  
  PARTITION yearly_south_direct VALUES (('AZ','D'),('TX','D'),('GA','D')),  
  PARTITION yearly_south_indirect VALUES (('AZ','I'),('TX','I'),('GA','I')),  
  PARTITION yearly_east_direct VALUES (('PA','D'), ('NC','D'), ('MA','D')),  
  PARTITION yearly_east_indirect VALUES (('PA','I'), ('NC','I'), ('MA','I')),  
  PARTITION yearly_north_direct VALUES (('MN','D'),('WI','D'),('MI','D')),  
  PARTITION yearly_north_indirect VALUES (('MN','I'),('WI','I'),('MI','I')),  
  PARTITION yearly_ny_direct VALUES ('NY','D'),  
  PARTITION yearly_ny_indirect VALUES ('NY','I'),  
  PARTITION yearly_ca_direct VALUES ('CA','D'),  
  PARTITION yearly_ca_indirect VALUES ('CA','I'),  
  PARTITION rest VALUES (DEFAULT)  
 )

Table created.

Statement 2

View the partition structure of the table

SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_BY_REGION_AND_CHANNEL'


PARTITION_NAME HIGH_VALUE
REST DEFAULT
YEARLY_CA_DIRECT ( 'CA', 'D' )
YEARLY_CA_INDIRECT ( 'CA', 'I' )
YEARLY_EAST_DIRECT ( 'PA', 'D' ), ( 'NC', 'D' ), ( 'MA', 'D' )
YEARLY_EAST_INDIRECT ( 'PA', 'I' ), ( 'NC', 'I' ), ( 'MA', 'I' )
YEARLY_NORTH_DIRECT ( 'MN', 'D' ), ( 'WI', 'D' ), ( 'MI', 'D' )
YEARLY_NORTH_INDIRECT ( 'MN', 'I' ), ( 'WI', 'I' ), ( 'MI', 'I' )
YEARLY_NY_DIRECT ( 'NY', 'D' )
YEARLY_NY_INDIRECT ( 'NY', 'I' )
YEARLY_SOUTH_DIRECT ( 'AZ', 'D' ), ( 'TX', 'D' ), ( 'GA', 'D' )
YEARLY_SOUTH_INDIRECT ( 'AZ', 'I' ), ( 'TX', 'I' ), ( 'GA', 'I' )
YEARLY_WEST_DIRECT ( 'OR', 'D' ), ( 'UT', 'D' ), ( 'WA', 'D' )
YEARLY_WEST_INDIRECT ( 'OR', 'I' ), ( 'UT', 'I' ), ( 'WA', 'I' )

13 rows selected.

PARTITION_NAME	HIGH_VALUE
REST	DEFAULT
YEARLY_CA_DIRECT	( 'CA', 'D' )
YEARLY_CA_INDIRECT	( 'CA', 'I' )
YEARLY_EAST_DIRECT	( 'PA', 'D' ), ( 'NC', 'D' ), ( 'MA', 'D' )
YEARLY_EAST_INDIRECT	( 'PA', 'I' ), ( 'NC', 'I' ), ( 'MA', 'I' )
YEARLY_NORTH_DIRECT	( 'MN', 'D' ), ( 'WI', 'D' ), ( 'MI', 'D' )
YEARLY_NORTH_INDIRECT	( 'MN', 'I' ), ( 'WI', 'I' ), ( 'MI', 'I' )
YEARLY_NY_DIRECT	( 'NY', 'D' )
YEARLY_NY_INDIRECT	( 'NY', 'I' )
YEARLY_SOUTH_DIRECT	( 'AZ', 'D' ), ( 'TX', 'D' ), ( 'GA', 'D' )
YEARLY_SOUTH_INDIRECT	( 'AZ', 'I' ), ( 'TX', 'I' ), ( 'GA', 'I' )
YEARLY_WEST_DIRECT	( 'OR', 'D' ), ( 'UT', 'D' ), ( 'WA', 'D' )
YEARLY_WEST_INDIRECT	( 'OR', 'I' ), ( 'UT', 'I' ), ( 'WA', 'I' )

Statement 3

Insert data into some of the partitions in the table

INSERT INTO sales_by_region_and_channel VALUES (005, 'AUTO DIRECT', 701000, 'OR', 'D' )

1 row(s) inserted.

Statement 4

INSERT INTO sales_by_region_and_channel VALUES (006, 'AUTO INDIRECT', 1201000, 'OR', 'I' )

1 row(s) inserted.

Statement 5

INSERT INTO sales_by_region_and_channel VALUES (005, 'AUTO DIRECT', 625000, 'WA', 'D' )

1 row(s) inserted.

Statement 6

INSERT INTO sales_by_region_and_channel VALUES (006, 'AUTO INDIRECT', 945000, 'WA', 'I' )

1 row(s) inserted.

Statement 7

INSERT INTO sales_by_region_and_channel VALUES (005, 'AUTO DIRECT', 595000, 'UT', 'D' )

1 row(s) inserted.

Statement 8

INSERT INTO sales_by_region_and_channel VALUES (006, 'AUTO INDIRECT', 825000, 'UT', 'I' )

1 row(s) inserted.

Statement 9

INSERT INTO sales_by_region_and_channel VALUES (003, 'AUTO DIRECT', 1950000, 'CA', 'D' )

1 row(s) inserted.

Statement 10

INSERT INTO sales_by_region_and_channel VALUES (004, 'AUTO INDIRECT', 5725000, 'CA', 'I' )

1 row(s) inserted.

Statement 11

Insert a value that will be in the DEFAULT partition

INSERT INTO sales_by_region_and_channel VALUES (010, 'AUTO DIRECT', 925000, 'IL', 'D' )

1 row(s) inserted.

Statement 12

Insert a value that will be in the DEFAULT partition

INSERT INTO sales_by_region_and_channel VALUES (010, 'AUTO INDIRECT', 3250000, 'IL', 'I' )

1 row(s) inserted.

Statement 13

Display the data that has been inserted into the table

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(yearly_west_direct)


DEPT_NUMBER DEPT_NAME QUARTERLY_SALES STATE CHANNEL
5 AUTO DIRECT 701000 OR D
5 AUTO DIRECT 625000 WA D
5 AUTO DIRECT 595000 UT D

3 rows selected.

DEPT_NUMBER	DEPT_NAME	QUARTERLY_SALES	STATE	CHANNEL
5	AUTO DIRECT	701000	OR	D
5	AUTO DIRECT	625000	WA	D
5	AUTO DIRECT	595000	UT	D

Statement 14

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(yearly_west_indirect)


DEPT_NUMBER DEPT_NAME QUARTERLY_SALES STATE CHANNEL
6 AUTO INDIRECT 1201000 OR I
6 AUTO INDIRECT 945000 WA I
6 AUTO INDIRECT 825000 UT I

3 rows selected.

DEPT_NUMBER	DEPT_NAME	QUARTERLY_SALES	STATE	CHANNEL
6	AUTO INDIRECT	1201000	OR	I
6	AUTO INDIRECT	945000	WA	I
6	AUTO INDIRECT	825000	UT	I

Statement 15

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(yearly_ca_direct)


DEPT_NUMBER DEPT_NAME QUARTERLY_SALES STATE CHANNEL
3 AUTO DIRECT 1950000 CA D

DEPT_NUMBER	DEPT_NAME	QUARTERLY_SALES	STATE	CHANNEL
3	AUTO DIRECT	1950000	CA	D

Statement 16

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(yearly_ca_indirect)


DEPT_NUMBER DEPT_NAME QUARTERLY_SALES STATE CHANNEL
4 AUTO INDIRECT 5725000 CA I

DEPT_NUMBER	DEPT_NAME	QUARTERLY_SALES	STATE	CHANNEL
4	AUTO INDIRECT	5725000	CA	I

Statement 17

Display the values in the DEFAULT partition

SELECT DEPT_NUMBER, DEPT_NAME, QUARTERLY_SALES, STATE, CHANNEL FROM SALES_BY_REGION_AND_CHANNEL PARTITION(rest)


DEPT_NUMBER DEPT_NAME QUARTERLY_SALES STATE CHANNEL
10 AUTO DIRECT 925000 IL D
10 AUTO INDIRECT 3250000 IL I

2 rows selected.

DEPT_NUMBER	DEPT_NAME	QUARTERLY_SALES	STATE	CHANNEL
10	AUTO DIRECT	925000	IL	D
10	AUTO INDIRECT	3250000	IL	I

Additional Information

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