Oracle List Partition Table

About Partitioned Tables and Indexes
Modern enterprises frequently run mission-critical databases containing upwards of several hundred gigabytes and, in many cases, several terabytes of data. These enterprises are challenged by the support and maintenance requirements of very large databases (VLDB), and must devise methods to meet those challenges.

One way to meet VLDB demands is to create and use partitioned tables and indexes. Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Indexes can be partitioned in similar fashion. Each partition is stored in its own segment and can be managed individually. It can function independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.

If you are using parallel execution, partitions provide another means of parallelization. Operations on partitioned tables and indexes are performed in parallel by assigning different parallel execution servers to different partitions of the table or index.

Partitions and subpartitions of a table or index all share the same logical attributes. For example, all partitions (or subpartitions) in a table share the same column and constraint definitions, and all partitions (or subpartitions) of an index share the same index options. They can, however, have different physical attributes (such as TABLESPACE).

Although you are not required to keep each table or index partition (or subpartition) in a separate tablespace, it is to your advantage to do so.Storing partitions in separate tablespaces enables you to:
Reduce the possibility of data corruption in multiple partitions
Back up and recover each partition independently
Control the mapping of partitions to disk drives (important for balancing I/O load)
Improve manageability, availability, and performance
Partitioning is transparent to existing applications and standard DML statements run against partitioned tables. However, an application can be programmed to take advantage of partitioning by using partition-extended table or index names in DML.
You can use SQL*Loader and the import and export utilities to load or unload data stored in partitioned tables. These utilities are all partition and subpartition aware.

When to Use List Partitioning
Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to partition mapping.
The list partitioning method is specifically designed for modeling data distributions that follow discrete values. This cannot be easily done by range or hash partitioning because:
Range partitioning assumes a natural range of values for the partitioning column. It is not possible to group together out-of-range values partitions.
Hash partitioning allows no control over the distribution of data because the data is distributed over the various partitions using the system hash function. Again, this makes it impossible to logically group together discrete values for the partitioning columns into partitions.
Further, list partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally.
Unlike the range and hash partitioning methods, multicolumn partitioning is not supported for list partitioning. If a table is partitioned by list, the partitioning key can consist only of a single column of the table. Otherwise all columns that can be partitioned by the range or hash methods can be partitioned by the list partitioning method.

When creating list partitions, you must specify:
Partitioning method: list
Partitioning column
Partition descriptions, each specifying a list of literal values (a value list), which are the discrete values of the partitioning column that qualify a row to be included in the partition

The following example creates a list-partitioned table. It creates table q1_sales_by_region which is partitioned by regions consisting of groups of states.

CREATE TABLE q1_sales_by_region(deptno number, deptname varchar2(20),quarterly_sales number(10, 2),state varchar2(2))PARTITION BY LIST (state)(PARTITION q1_northwest VALUES ('OR', 'WA'),PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'),PARTITION q1_southeast VALUES ('FL', 'GA'),PARTITION q1_northcentral VALUES ('SD', 'WI'),PARTITION q1_southcentral VALUES ('OK', 'TX'));

A row is mapped to a partition by checking whether the value of the partitioning column for a row matches a value in the value list that describes the partition.

For example, some sample rows are inserted as follows:
(10, 'accounting', 100, 'WA') maps to partition q1_northwest
(20, 'R&D', 150, 'OR') maps to partition q1_northwest
(30, 'sales', 100, 'FL') maps to partition q1_southeast
(40, 'HR', 10, 'TX') maps to partition q1_southwest
(50, 'systems engineering', 10, 'CA') does not map to any partition in the table and raises an error

Unlike range partitioning, with list partitioning, there is no apparent sense of order between partitions. You can also specify a default partition into which rows that do not map to any other partition are mapped. If a default partition were specified in the preceding example, the state CA would map to that partition.
Oracle supports partitioning only for tables, indexes on tables, materialized views, and indexes on materialized views. Oracle does not support partitioning of clustered tables or indexes on clustered tables.
Tables can be partitioned into up to 64,000 separate partitions. Any table can be partitioned except those tables containing columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.