content_image/807256.jpg
<img src='content_image/807256.jpg'>

With LIST COLUMNS  partitioning, you can create a table for customer data that assigns a row to any of 4 partitions corresponding to these regions based on the name of the city where a customer resides, as shown here: 

CREATE TABLE customers_1 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE ) PARTITION BY LIST COLUMNS(city) ( PARTITION pRegion_1 VALUES IN(’Oskarshamn’, ’Högsby’, ’Mönsterås’), PARTITION pRegion_2 VALUES IN(’Vimmerby’, ’Hultsfred’, ’Västervik’), PARTITION pRegion_3 VALUES IN(’Nässjö’, ’Eksjö’, ’Vetlanda’), PARTITION pRegion_4 VALUES IN(’Uppvidinge’, ’Alvesta’, ’Växjo’) ); 

As with partitioning by RANGE COLUMNS , you do not need to use expressions in the COLUMNS()  clause to convert column values into integers. (In fact, the use of expressions other than column names is not permitted with COLUMNS() .) 

It is also possible to use DATE  and DATETIME  columns, as shown in the following example that uses the same name and columns as the customers_1  table shown previously, but employs LIST COLUMNS partitioning based on the renewal  column to store rows in one of 4 partitions depending on the week in February 2010 the customer’s account is scheduled to renew: 

CREATE TABLE customers_2 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE ) PARTITION BY LIST COLUMNS(renewal) ( PARTITION pWeek_1 VALUES IN(’2010-02-01’, ’2010-02-02’, ’2010-02-03’, ’2010-02-04’, ’2010-02-05’, ’2010-02-06’, ’2010-02-07’), PARTITION pWeek_2 VALUES IN(’2010-02-08’, ’2010-02-09’, ’2010-02-10’, ’2010-02-11’, ’2010-02-12’, ’2010-02-13’, ’2010-02-14’), PARTITION pWeek_3 VALUES IN(’2010-02-15’, ’2010-02-16’, ’2010-02-17’, ’2010-02-18’, ’2010-02-19’, ’2010-02-20’, ’2010-02-21’), PARTITION pWeek_4 VALUES IN(’2010-02-22’, ’2010-02-23’, ’2010-02-24’, ’2010-02-25’, ’2010-02-26’, ’2010-02-27’, ’2010-02-28’) ); 

This works, but becomes cumbersome to define and maintain if the number of dates involved grows very large; in such cases, it is usually more practical to employ RANGE  or RANGE COLUMNS  partitioning instead. In this case, since the column we wish to use as the partitioning key is a DATE  column, we use RANGE COLUMNS  partitioning, as shown here: 

CREATE TABLE customers_3 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), 

