Friday, February 27, 2009

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

    No comments:

    Post a Comment