Partition Table

Hash Partitioning:

A hash algorithm is applied to the partitioning key to determine the partition for a given row

 SQL> create tablespace part_1

           datafile 'C:\oracle\product\10.2.0\oradata\dba20\part_1.DBF' SIZE 1M;

 Tablespace created.

 

SQL> create tablespace part_2

           datafile 'C:\oracle\product\10.2.0\oradata\dba20\part_2.DBF' SIZE 1M;

 Tablespace created.

 

SQL> create tablespace part_3

           datafile 'C:\oracle\product\10.2.0\oradata\dba20\part_3.DBF' SIZE 1M;

 Tablespace created.

 

SQL> create tablespace part_4

           datafile 'C:\oracle\product\10.2.0\oradata\dba20\part_4.DBF' SIZE 1M;

 Tablespace created.

 

SQL> grant create session,create table to alex;

 Grant succeeded.

 

SQL> create table hash_part(

           prof_history_id number(10),

          person_id number(10) not null,

          organization_id number(10) not null,

          record_date date not null,

          prof_hist_comments varchar2(2000))

          partition by hash(prof_history_id)

          partitions 3

          store in(part_1,part_2,part_3);

 Table created.

 

SQL> select partition_name,tablespace_name

           from user_tab_partitions

          where table_name='HASH_PART';

PARTITION_NAME       TABLESPACE_NAME

-------------------- ------------------------------

SYS_P21              PART_1

SYS_P22              PART_2

SYS_P23              PART_3


Range Partitioning:

Each partition is specified by a range of values of the partitioning key (for a table with a date column as the partitioning key, the 'January-2001' partition contains rows with the partitioning-key values from '01-JAN-2001' - '31-JAN-2001')

SQL> create table range_part(

  prof_history_id number(10),

  person_id number(10) not null,

  organization_id number(10) not null,

  record_date date not null,

  prof_hist_comments varchar2(2000))

  partition by range(record_date)(

  PARTITION yr0 VALUES LESS THAN(TO_DATE('01-JAN-2000','DD-MON-YYYY'))

  tablespace part_1,

  PARTITION yr1 VALUES LESS THAN(TO_DATE('01-JAN-2001','DD-MON-YYYY'))

  tablespace part_2,

  PARTITION yr2 VALUES LESS THAN(TO_DATE('01-JAN-2002','DD-MON-YYYY'))

  tablespace part_3,

  PARTITION yr4 VALUES LESS THAN(MAXVALUE) tablespace part_4

  );

Table created.

 

SQL> insert into range_part values(1,1,10,'21-JAN-1999','good');

1 row created.

 

SQL> insert into range_part values(1,1,10,'01-JAN-2000','good');

1 row created.

 

SQL> insert into range_part values(3,3,30,'21-MAR-2001','very good');

1 row created.

 

SQL> insert into range_part values(4,4,40,'21-APR-2002','BAD');

1 row created.

 

SQL> insert into range_part values(5,5,50,'17-APR-2009','FINE');

1 row created.

 

SQL> select * from range_part partition(yr0);

 

PROF_HISTORY_ID         PERSON_ID           ORGANIZATION_ID          RECORD_DA                 PROF_HIST_COMMENTS

------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

              1                                    1                                    10                                 21-JAN-99                                   good

SQL> select * from range_part partition(yr1);

 

PROF_HISTORY_ID          PERSON_ID               ORGANIZATION_ID      RECORD_DA                PROF_HIST_COMMENTS

----------------------------------------------------------------------------------------------------------------------------------------------------------------

              1                                    1                                        10                              01-JAN-00                             good

SQL> select * from range_part partition(yr2);

 

PROF_HISTORY_ID           PERSON_ID           ORGANIZATION_ID    RECORD_DA                  PROF_HIST_COMMENTS

-------------------------------------------------------------------------------------------------------------------------------------------------------------

              3                                      3                                   30                            21-MAR-01                               very good

 

SQL> select * from range_part partition(yr4);

 

PROF_HISTORY_ID           PERSON_ID            ORGANIZATION_ID   RECORD_DA                  PROF_HIST_COMMENTS

--------------------------------------------------------------------------------------------------------------------------------------------------------------

              4                                       4                                    40                        21-APR-02                                       BAD

              5                                      5                                     50                        17-APR-09                                       FINE

 

SQL> select table_name,tablespace_name,partitioned

           from user_tables

          where table_name='RANGE_PART';

 

TABLE_NAME                     TABLESPACE_NAME                PAR

------------------------------ ------------------------------ ---

RANGE_PART                                                    YES

 

SQL> select partition_name,tablespace_name,high_value

           from user_tab_partitions

           where table_name='RANGE_PART';

 

PARTITION_NAME    TABLESPACE_NAME                      HIGH_VALUE

------------------------------------------------------------------------------------------------

YR0                                      PART_1                         TO_DATE(' 2000-01-01 00:00:00', 

                                                                                   'SYYYY-MM-DD HH24:MI:SS', 

                                                                                    'NLS_CALENDAR=GREGORIA

YR1                                      PART_2                        TO_DATE(' 2001-01-01 00:00:00',

                                                                                    'SYYYY-MM-DD HH24:MI:SS',    

                                                                                    'NLS_CALENDAR=GREGORIA

YR2                                      PART_3                        TO_DATE(' 2002-01-01 00:00:00',

                                                                                    'SYYYY-MM-DD HH24:MI:SS',

                                                                                    'NLS_CALENDAR=GREGORIA

YR4                                     PART_4                          MAXVALUE

 

Move Partition:

SQL> alter table range_part

           move partition yr2

           tablespace part_4;

Table altered.

 

SQL> select partition_name,tablespace_name,high_value

           from user_tab_partitions

           where table_name='RANGE_PART';

 

PARTITION_NAME    TABLESPACE_NAME                      HIGH_VALUE

------------------------------------------------------------------------------------------------

YR0                                      PART_1                         TO_DATE(' 2000-01-01 00:00:00', 

                                                                                   'SYYYY-MM-DD HH24:MI:SS', 

                                                                                    'NLS_CALENDAR=GREGORIA

YR1                                      PART_2                        TO_DATE(' 2001-01-01 00:00:00',

                                                                                    'SYYYY-MM-DD HH24:MI:SS',    

                                                                                    'NLS_CALENDAR=GREGORIA

YR2                                      PART_4                        TO_DATE(' 2002-01-01 00:00:00',

                                                                                    'SYYYY-MM-DD HH24:MI:SS',

                                                                                    'NLS_CALENDAR=GREGORIA

YR4                                     PART_4                          MAXVALUE

 

Range Partition by Alpha:

SQL> create table students(

           student_id number(6),

          student_fn varchar2(25),

          student_ln varchar2(25),

          primary key(student_id))

          partition by range(student_ln)(

          partition student_ae values less than('F%') tablespace part_1,

          partition student_fl values less than('M%') tablespace part_2,

          partition student_mr values less than('S%') tablespace part_3,

          partition student_sz values less than(maxvalue) tablespace part_4

           );

Table created.

 

SQL> SELECT table_name, tablespace_name, partitioned

            FROM user_tables

            WHERE TABLE_NAME='STUDENTS';

 

TABLE_NAME                     TABLESPACE_NAME                PAR

------------------------------ ------------------------------------------------------------

STUDENTS                                                                                   YES

 

SQL> SELECT partition_name, tablespace_name, high_value

           FROM user_tab_partitions

            WHERE Table_name='STUDENTS';

 

PARTITION_NAME        TABLESPACE_NAME                   HIGH_VALUE

--------------------------------------------------------------------------------------------------

STUDENT_AE                     PART_1                                                 'F%'

STUDENT_FL                      PART_2                                                 'M%'

STUDENT_MR                     PART_3                                                 'S%'

STUDENT_SZ                     PART_4                                            MAXVALUE


LIST PARTTITION TABLE:

Each partition is specified by a list of values of the partitioning key (for a table with a region column as the partitioning key, the 'North America' partition may contain values 'Canada', 'USA', and 'Mexico')


SQL> CREATE TABLE list_part (

    deptno NUMBER(10),

    deptname VARCHAR2(20) ,

    quarterly_sales NUMBER(10,2),

    state VARCHAR2(3))

    PARTITION BY LIST (state)(

    PARTITION q1_northwest VALUES ('DK', 'CH') TABLESPACE part_1,

    PARTITION q1_southwest VALUES ('KL', 'BR', 'MM') TABLESPACE part_2,

    PARTITION q1_northeast VALUES ('SYL', 'BOG', 'JP') TABLESPACE part_1,

    PARTITION q1_southeast VALUES ('TN', 'GA') TABLESPACE part_2,

   PARTITION q1_northcent VALUES ('MN', 'WI') TABLESPACE part_1,

   PARTITION q1_southcent VALUES ('OK', 'TX') TABLESPACE part_2);

 

SQL> SELECT partition_name, tablespace_name, high_value

           FROM user_tab_partitions

           WHERE Table_name='LIST_PART';

 

PARTITION_NAME        TABLESPACE_NAME                    HIGH_VALUE

--------------------------------------------------------------------------------------------------

Q1_NORTHWEST                   PART_1                                          'DK', 'CH'

Q1_SOUTHWEST                    PART_2                                      'KL', 'BR', 'MM'

Q1_NORTHEAST                    PART_1                                      'SYL', 'BOG', 'JP'

Q1_SOUTHEAST                    PART_2                                             'TN', 'GA'

Q1_NORTHCENT                   PART_1                                             'MN', 'WI'

Q1_SOUTHCENT                   PART_2                                              'OK', 'TX'


6 rows selected.

 

SQL> insert into list_part values(10,'CSE',30,'DK');

1 row created.

 

SQL> insert into list_part values(20,'BBA',30,'SYL');

1 row created.

 

SQL> select * from list_part;

 

    DEPTNO DEPTNAME             QUARTERLY_SALES STA

------------------------------------------------------------------------------------

        10 CSE                               30 DK

        20 BBA                               30 SYL

 

SQL> select * from list_part partition(q1_northwest);

 

    DEPTNO DEPTNAME             QUARTERLY_SALES STA

-----------------------------------------------------------------------------------

        10 CSE                               30 DK

 

SQL> select * from list_part partition(q1_northeast);

 

    DEPTNO DEPTNAME             QUARTERLY_SALES STA

-----------------------------------------------------------------------------------

        20 BBA                               30 SYL

 

SUB PARTITION (RANGE & LIST):

 Composite Range-Hash Partitioning: A combination of the Range and Hash partitioning technique. The table is first range-partitioned, and then each individual range-partition is further sub-partitioned using the hash partitioning technique. All sub-partitions for a given range partition together represent a logical subset of the data.

 

SQL> CREATE TABLE composite_rng_list (

cust_id     NUMBER(10),

cust_name   VARCHAR2(25),

cust_state  VARCHAR2(2),

time_id     DATE)

PARTITION BY RANGE(time_id)

SUBPARTITION BY LIST (cust_state)

SUBPARTITION TEMPLATE(

SUBPARTITION west VALUES ('OR', 'WA') TABLESPACE part_1,

SUBPARTITION east VALUES ('NY', 'CT') TABLESPACE part_2,

SUBPARTITION cent VALUES ('OK', 'TX') TABLESPACE part_3) (

PARTITION pert_1 VALUES LESS THAN                  (TO_DATE('01/01/2000','DD/MM/YYYY')),

PARTITION pert_2 VALUES LESS THAN (TO_DATE('01/01/2005','DD/MM/YYYY')),

PARTITION pert_3 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')),

PARTITION part_4 VALUES LESS THAN(MAXVALUE));

Table created.

 

SQL> SELECT table_name,partition_name,subpartition_name,high_value

            FROM user_tab_subpartitions

            WHERE TABLE_NAME='COMPOSITE_RNG_LIST';

 

TABLE_NAME                   PARTITION_NAME      SUBPARTITION_NAME         HIGH_VALUE

-------------------------------------------------------------------------------------------------------------------------

COMPOSITE_RNG_LIST             PART_4                PART_4_WEST             'OR', 'WA'

COMPOSITE_RNG_LIST             PART_4                 PART_4_EAST              'NY', 'CT'

COMPOSITE_RNG_LIST             PART_4                 PART_4_CENT              'OK', 'TX'

COMPOSITE_RNG_LIST             PERT_1                  PERT_1_WEST              'OR', 'WA'

COMPOSITE_RNG_LIST             PERT_1                  PERT_1_EAST               'NY', 'CT'

COMPOSITE_RNG_LIST             PERT_1                  PERT_1_CENT               'OK', 'TX'

COMPOSITE_RNG_LIST             PERT_2                   PERT_2_WEST              'OR', 'WA'

COMPOSITE_RNG_LIST             PERT_2                   PERT_2_EAST                'NY', 'CT'

COMPOSITE_RNG_LIST             PERT_2                   PERT_2_CENT                'OK', 'TX'

COMPOSITE_RNG_LIST             PERT_3                   PERT_3_WEST                'OR', 'WA'

COMPOSITE_RNG_LIST             PERT_3                   PERT_3_EAST                  'NY', 'CT'

COMPOSITE_RNG_LIST             PERT_3                   PERT_3_CENT                 'OK', 'TX'

 

12 rows selected.

 

SQL>CREATE TABLE range_list(

   cust_id NUMBER(10),

   channel_id NUMBER(3),

   amount_sold NUMBER(10,2),

   time_id DATE)

   PARTITION BY RANGE(time_id)

   SUBPARTITION BY LIST(channel_id)

   SUBPARTITION TEMPLATE(

   SUBPARTITION sp1 VALUES (2,3) TABLESPACE part_1,

   SUBPARTITION sp2 VALUES (4,5) TABLESPACE part_2,

   SUBPARTITION sp3 VALUES (6,7) TABLESPACE part_3,

   SUBPARTITION sp4 VALUES (8,9) TABLESPACE part_4)

   (PARTITION sp98 VALUES LESS THAN(TO_DATE( '01/01/1998' ,'DD/MM/YYYY' )),

   PARTITION s98 VALUES LESS THAN(TO_DATE( '01/01/1999' ,'DD/MM/YYYY' )),

   PARTITION s99 VALUES LESS THAN(TO_DATE( '01/01/2000' ,'DD/MM/YYYY' )),

   PARTITION s2K VALUES LESS THAN(TO_DATE( '01/01/2001' ,'DD/MM/YYYY' )),

   PARTITION s01 VALUES LESS THAN(TO_DATE( '01/01/2002' ,'DD/MM/YYYY' )),

   PARTITION sf VALUES LESS THAN(MAXVALUE) );

Table created.

 

SQL> SELECT partition_name,subpartition_name, tablespace_name,high_value

            FROM user_tab_subpartitions

            WHERE table_name ='RANGE_LIST';

 

PARTITION_NAME       SUBPARTITION_NAME    TABLESPACE_NAME          HIGH_VALUE

------------------------------------------------------------------------------------------------------------------------------

S01                                            S01_SP1                        PART_1                              2, 3

S01                                            S01_SP2                        PART_2                              4, 5

S01                                            S01_SP3                        PART_3                              6, 7

S01                                            S01_SP4                        PART_4                              8, 9

S2K                                           S2K_SP1                       PART_1                               2, 3

S2K                                           S2K_SP2                       PART_2                              4, 5

S2K                                           S2K_SP3                       PART_3                             6, 7

S2K                                           S2K_SP4                       PART_4                              8, 9

S98                                            S98_SP1                       PART_1                                2, 3

S98                                            S98_SP2                       PART_2                                4, 5

S98                                            S98_SP3                        PART_3                               6, 7

S98                                            S98_SP4                       PART_4                               8, 9

S99                                            S99_SP1                       PART_1                                2, 3

S99                                            S99_SP2                        PART_2                              4, 5

S99                                            S99_SP3                        PART_3                              6, 7

S99                                            S99_SP4                        PART_4                              8, 9

SF                                                   SF_SP1                       PART_1                                 2, 3

SF                                                   SF_SP2                       PART_2                                 4, 5

SF                                                   SF_SP3                       PART_3                                  6, 7

SF                                                  SF_SP4                        PART_4                                  8, 9

SP98                                             SP98_SP1                     PART_1                                   2, 3

SP98                                             SP98_SP2                    PART_2                                     4, 5

SP98                                             SP98_SP3                     PART_3                                   6, 7

SP98                                            SP98_SP4                      PART_4                                  8, 9

 

24 rows selected.

 

Marge partition:

SQL> ALTER TABLE range_list

           MERGE SUBPARTITIONS sp98_sp1, sp98_sp2 INTO SUBPARTITION sp12

           TABLESPACE part_1;

Table altered.

 

SQL> SELECT partition_name,subpartition_name, tablespace_name,high_value

           FROM user_tab_subpartitions

           WHERE table_name ='RANGE_LIST';

 

PARTITION_NAME            SUBPARTITION_NAME    TABLESPACE_NAME                HIGH_VALUE

--------------------------------------------------------------------------------------------------------------------------------------------

S01                                        S01_SP1                        PART_1                               2, 3

S01                                        S01_SP2                        PART_2                               4, 5

S01                                        S01_SP3                        PART_3                               6, 7

S01                                        S01_SP4                        PART_4                                8, 9

S2K                                       S2K_SP1                        PART_1                               2, 3

S2K                                       S2K_SP2                        PART_2                               4, 5

S2K                                       S2K_SP3                        PART_3                              6, 7

S2K                                       S2K_SP4                         PART_4                             8, 9

S98                                        S98_SP1                          PART_1                             2, 3

S98                                         S98_SP2                         PART_2                             4, 5

S98                                         S98_SP3                         PART_3                             6, 7

S98                                        S98_SP4                          PART_4                              8, 9

S99                                         S99_SP1                          PART_1                              2, 3

S99                                        S99_SP2                          PART_2                               4, 5

S99                                        S99_SP3                           PART_3                               6, 7

S99                                        S99_SP4                           PART_4                               8, 9

SF                                            SF_SP1                           PART_1                               2, 3

SF                                            SF_SP2                           PART_2                               4, 5

SF                                           SF_SP3                            PART_3                               6, 7

SF                                            SF_SP4                           PART_4                                8, 9

SP98                                      SP98_SP3                         PART_3                                6, 7

SP98                                           SP98_SP4                            PART_4                            8, 9

SP98                                                  SP12                              PART_1                          2, 3, 4, 5

 

23 rows selected.

 

Global index creation:

SQL> CREATE INDEX gi_range_list_cust_id ON range_list(cust_id);

Index created.

 

Local index creation:

SQL> CREATE INDEX li_range_list_cust_id

           ON range_list(cust_id)

           LOCAL;

Index created.

 

Truncate Partition:

SQL> insert into range_list values(1,2,199,'01-JAN-1997');

1 row created.

 

SQL> select * from range_list partition (sp98);

 

   CUST_ID CHANNEL_ID AMOUNT_SOLD TIME_ID

---------- ---------- ----------- ---------

         1          2         199 01-JAN-97

 

SQL> ALTER TABLE range_list

           TRUNCATE PARTITION sp98

           DROP STORAGE;

Table truncated.

 

SQL> select * from range_list partition (sp98);

no rows selected

 

Drop Partition:

SQL> ALTER TABLE range_list DROP PARTITION sp98 UPDATE GLOBAL INDEXES;

Table altered.

 

SQL> SELECT partition_name,subpartition_name, tablespace_name,high_value

           FROM user_tab_subpartitions

           WHERE table_name ='RANGE_LIST';

 

PARTITION_NAME            SUBPARTITION_NAME    TABLESPACE_NAME                HIGH_VALUE

--------------------------------------------------------------------------------------------------------------------------------------------

S01                                        S01_SP1                        PART_1                               2, 3

S01                                        S01_SP2                        PART_2                               4, 5

S01                                        S01_SP3                        PART_3                               6, 7

S01                                        S01_SP4                        PART_4                                8, 9

S2K                                       S2K_SP1                        PART_1                               2, 3

S2K                                       S2K_SP2                        PART_2                               4, 5

S2K                                       S2K_SP3                        PART_3                              6, 7

S2K                                       S2K_SP4                         PART_4                             8, 9

S98                                        S98_SP1                          PART_1                             2, 3

S98                                         S98_SP2                         PART_2                             4, 5

S98                                         S98_SP3                         PART_3                             6, 7

S98                                        S98_SP4                          PART_4                              8, 9

S99                                         S99_SP1                          PART_1                              2, 3

S99                                        S99_SP2                          PART_2                               4, 5

S99                                        S99_SP3                           PART_3                               6, 7

S99                                        S99_SP4                           PART_4                               8, 9

SF                                            SF_SP1                           PART_1                               2, 3

SF                                            SF_SP2                           PART_2                               4, 5

SF                                           SF_SP3                            PART_3                               6, 7

SF                                            SF_SP4                           PART_4                                8, 9 

Comments