Range & List-Partitioned Tables

A range partition allows Oracle to place data in a partition based on the input value being between two values. 'MaxValue' is used to hold everything higher than the previous value listed.

A list partition allows Oracle to place data in a partition based on the input value matching a value in the list. The 'Default' is used to hold everything not mentioned in the list.

For instance, in the range-partitioned table below, data is grouped based on the year

partition Part_2004 - any year less than 2005

partition Part_2005 - any year between 2005 and 2006

partition Part_Max - any year greater than 2006

SQL> Create Table Test_Range
  2   ( Id    Number(3)
  3   , Year  Number(4)
  4   )
  5    Partition By Range(Year)
  6    (
  7      Partition Part_2004 Values Less Than (2005)
  8    , Partition Part_2005 Values Less Than (2006)
  9    , Partition Part_Max  Values Less Than (MaxValue)
 10    );
 
SQL> Insert Into Test_Range Values (1,2003);
SQL> Insert Into Test_Range Values (2,2004);
SQL> Insert Into Test_Range Values (3,2005);
SQL> Insert Into Test_Range Values (4,2006);
SQL> Insert Into Test_Range Values (5,2007);

SQL> Select * From Test_Range;

ID       YEAR
-- ----------
 1       2003
 2       2004
 3       2005
 4       2006
 5       2007

SQL> Select * from Test_Range PARTITION (Part_2004);

ID       YEAR
-- ----------
 1       2003
 2       2004

SQL> Select * from Test_Range PARTITION (Part_2005);

ID       YEAR
-- ----------
 3       2005

SQL> Select * from Test_Range PARTITION (Part_Max);

ID       YEAR
-- ----------
 4       2006
 5       2007

---

In the List-partitioned table below, data is grouped based on continents:

partition Part_Europe - France or Italy

partition Part_America - Canada or United States

partition Part_Others - any country not listed in the other two partitions.

SQL> Create Table Test_List
  2   ( Id      Number(3)
  3   , Country Varchar2(30)
  4   )
  5    Partition By List(Country)
  6     (
  7       Partition Part_Europe  Values ('France', 'Italy')
  8     , Partition Part_America Values ('Canada', 'United States')
  9     , Partition Part_Others  Values (Default)
 10     );

SQL> Insert Into Test_List Values (1,'France');
SQL> Insert Into Test_List Values (2,'Italy');
SQL> Insert Into Test_List Values (3,'Canada');
SQL> Insert Into Test_List Values (4,'United States');
SQL> Insert Into Test_List Values (5,'Australia');
SQL> Insert Into Test_List Values (6,'China');

SQL> Select * from Test_List;

ID COUNTRY
-- -------------
 1 France
 2 Italy
 3 Canada
 4 United States
 5 Australia
 6 China
 
SQL> Select * from Test_List PARTITION (Part_Europe );

ID COUNTRY
-- -------------
 1 France
 2 Italy

SQL> Select * from Test_List PARTITION (Part_America);

ID COUNTRY
-- -------------
 3 Canada
 4 United States
 
SQL> Select * from Test_List PARTITION (Part_Others );

ID COUNTRY
-- -------------
 5 Australia
 6 China

For more information see the Oracle documentation on Partitioned Tables and Indexes