Database Design

List partitioned tables

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Thursday, 18 March 2010 22:45
Last Updated on Friday, 30 March 2012 17:43
Published on Thursday, 18 March 2010 22:45
Written by Guy Lambregts
Hits: 3446

List partitioned tables

 

See also Partition Administration

List partitioning allows us to control which rows go to which partition. We specify a distinct list of values for the partition key.
Local and global index approach is similar to the other partition methods. Note that in the examples below I specify a default partition, rows which are not mapped to any of the specified partitions go to the default partition.
It is not mandatory to specify a default partition. It depends on whether you want a failure or not when there is an attempt to insert a row with a not listed value for the partition key.

Advantages of list partitioning are

1. Possible partition pruning, if we query the table with in the where clause the partition key.

2. Possible partition wise joins if we join tables and query the tables with in the where clause the partition key.

3. Possible to store every partition in its dedicated tablespace, on a separate device ( IO )

We create a list partitioned table with a non partitioned primary key index

 

SQL> create table table_t5 ( T5_ID number(18),

T5_somefield1 varchar2(3) not null,

T5_somefield2 varchar2(100),

T5_somefield3 varchar2(100),

T5_somedate date ) partition by list (T5_somefield1)

( partition be values ('032'),

partition nl values ('031'),

 

partition fr values ('033'),

 

partition ot values DEFAULT));

Table created.

SQL> alter table table_T5 add constraint pk_t5 primary key (T5_ID) using index tablespace users;

Table altered.


SQL> create index T5_IDX1 on table_T5 (T5_somefield1,T5_somedate) local tablespace users;

Index created.




We create a list partitioned table with a global hash partitioned primary key index

 

 

SQL> create table table_t6 ( T6_ID number(18), T6_somefield1 varchar2(3) not null, T6_somefield2 varchar2(100), T6_somefield3 varchar2(100), T6_somedate date )

partition by list (T6_somefield1)
  2  (
  3  partition be values ('032'),
  4  partition nl values ('031'),
  5  partition fr values ('033'),
  6  partition ot values (DEFAULT)
  7  );

Table created.

SQL> alter table table_T6 add constraint pk_t6 primary key (T6_ID) using index global partition by hash (T6_ID) partitions 10 tablespace users;

Table altered.



Here we we insert into partition be

SQL> insert into table_T6 values (1,'032',dbms_random.string('U',50),dbms_random.string('U',50),sysdate);


1 row created.

SQL> select * from table_T6 partition(be);

         1 032
ENXXKNOGATLJTRUKVQEYJRMAJEVGWOEFQDAJQPTFHMMBYGDVMU
NDWISNRSEHCSMQXLSJHNQCDRZIXQVVYJJSXXTIWEQSIGWEYFLF
25/03/08




Here we we insert into the default partition

SQL> insert into table_T6 values (2,'017',dbms_random.string('U',50),dbms_random.string('U',50),sysdate);


1 row created.

SQL> select * from table_T6 partition(ot);

         2 017
EQATUQWOJITPMGUWGQAJUUTBLDWCYPJEMOZCXMAHRYGMIORZOG
EPUQCPTXWSGFSVXSSJTVEYOHWCJKBZKBSTFLTGLVVDSTDOQZWB
25/03/08