Partitioning -- 5 : List Partitioning

List Partitioning allows you to specify a value (or a set of values) for the Partition Key to map to each Partition.

This example shows List Partitioning.

SQL> create table request_queue
  2  (request_id  number primary key,
  3   request_submision_time timestamp,
  4   requestor  number,
  5   request_arg_1 varchar2(255),
  6   request_arg_2 varchar2(255),
  7   request_arg_3 varchar2(255),
  8   request_status varchar2(10),
  9   request_completion_time timestamp)
 10  partition by list (request_status)
 11  (partition p_submitted values ('SUBMITTED'),
 12   partition p_running values ('RUNNING'),
 13   partition p_errored values ('ERRORED'),
 14   partition p_completed values ('COMPLETED'),
 15   partition p_miscell values ('RECHECK','FLAGGED','UNKNOWN'),
 16   partition p_default values (DEFAULT)
 17  )
 18  /

Table created.

SQL> 

Note how the P_MISCELL Partition can host multiple values for the REQUEST_STATUS column.
The last Partition, has is specified as a DEFAULT Partition (note that DEFAULT is a keyword, not a value like the others) to hold rows for REQUEST_STATUS for values not mapped to any of the other Partitions.  With List Partitioning, you should always have a DEFAULT Partition (it can have any name, e.g. P_UNKNOWN) so that unmapped rows can be captured.

If you go back to my previous post on Row Movement, you should realise the danger of capturing changing values (e.g. from "SUBMITTED" to "RUNNING" to "COMPLETED") in different Partitions.  What is the impact of updating a Request from the "SUBMITTED" status to the "RUNNING" status and then to the "COMPLETED" status ?  It is not simply an update of the REQUEST_STATUS column alone but a physical reinsertion of the entire row (with the consequent update to all indexes) at each change of status.

SQL> insert into request_queue
  2  values (request_id_seq.nextval,systimestamp,101,   
  3  'FAC1','NOTE',null,'SUBMITTED',null)
  4  /

1 row created.

SQL>
SQL> commit;

Commit complete.

.... sometime later ....

SQL> update request_queue
  2  set request_status = 'RUNNING'
  3  where request_id=1001
  4  /
update request_queue
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL> 

So, although now we know that we must ENABLE ROW MOVEMENT, we must suffer the impact of the physical reinsertion of the entire row into a new Partition.

SQL> alter table request_queue enable row movement;

Table altered.

SQL> update request_queue
  2  set request_status = 'RUNNING'
  3  where request_id=1001
  4  /

1 row updated.

SQL> commit;

Commit complete.

SQL> 
.... sometime later ....

SQL> update request_queue
  2  set request_status = 'COMPLETED',
  3  request_completion_time=systimestamp
  4  where request_id=1001
  5  /

1 row updated.

SQL> commit;

Commit complete.

SQL> 

(Note that all the previous "Partitioning 3a to 3d" posts about Indexing apply to List Partitioning as well)