Home  >  Oracle Database Administration (DBA)  >  Maintenance  >  Table & Index Partitioning

Advantages of table partitioning

   Here are the advantages of partitioning:

   1)  Manageability: Each partition technically is treated as a table: could be dropped, added, imported, exported, could be put on different disk than the other partitions.

   2)  Better Performance: Sometimes instead having a full table scan on the whole table we will have a full scan on a partition only; we can split a DML on the whole table on each partition (not to have too much UNDO generated); we can take more advantage of parallel execution if the partitions are on different disks.  

   3)  Availability:  If one partition is unavailable the others are. 

RANGE Partitioning

Here is an example of RANGE partitioning:

               CREATE TABLE ORDERS_RANGE

                         (ORDER_ID NUMBER,

                          ORDER_DATE DATE,

                          CUSTOMER_ID NUMBER,

                          PRICE NUMBER)

               PARTITION BY RANGE(ORDER_DATE)

                           (    PARTITION P1 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')),

                                PARTITION P2 VALUES LESS THAN (TO_DATE('01-FEB-2008', 'DD-MON-YYYY'))

                           );

               Because the storage, tablespace information and so an are not mentioned, all the other values are taken by default (for that user). 

               Here is the whole definition of the new partitioned table (here we can see the parameters which can be set up at the partition level):

               CREATE TABLE ORDERS_RANGE

                       ( ORDER_ID NUMBER,

                         ORDER_DATE DATE,

                         CUSTOMER_ID NUMBER,

                         PRICE NUMBER )

                TABLESPACE USERS

                PCTUSED 0

                PCTFREE 10

                INITRANS 1

                MAXTRANS 255

                PARTITION BY RANGE (ORDER_DATE)

                          (    PARTITION P1 VALUES LESS THAN (TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',  

                                                                                                               'NLS_CALENDAR=GREGORIAN'))

                              LOGGING

                              TABLESPACE USERS

                              PCTFREE 10

                              INITRANS 1

                              MAXTRANS 255

                              STORAGE (

                                        INITIAL 64K

                                        MINEXTENTS 1

                                        MAXEXTENTS 2147483645

                                        BUFFER_POOL DEFAULT

                                                ),

                 PARTITION P2 VALUES LESS THAN (TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',

                                                                                                               'NLS_CALENDAR=GREGORIAN'))

                              LOGGING

                              TABLESPACE USERS

                              PCTFREE 10

                              INITRANS 1

                              MAXTRANS 255

                              STORAGE (

                                       INITIAL 64K

                                       MINEXTENTS 1

                                       MAXEXTENTS 2147483645

                                       BUFFER_POOL DEFAULT

                                                )

                   NOCOMPRESS

  The ORDER_RANGE table has 2 partitions P1 and P2. If a row has ORDER_DATE less than 01-APR-1999, this row will be put in the partition P1. If ORDER_DATE is between 01-APR-1999 and 01-FEB-2008 the row will be stored in the partition P2.  

If the ORDER_DATE value is bigger than 01-FEB-2008 an error will occurs:

To avoid such an error "less than(MAXVALUE)" must be used for the last (the most recent) partitioning key value.

HASH Partitioning

In HASH partitioning there is an internal algorithm which decide the partition a row will be stored. 

                     CREATE TABLE ORDERS_HASH

                                    (  ORDER_ID NUMBER,

                                       ORDER_DATE DATE,

                                       CUSTOMER_ID NUMBER,

                                       PRICE NUMBER  )

                    PARTITION BY HASH (ORDER_DATE)

                       (  PARTITION P1 TABLESPACE USERS,

                          PARTITION P2 TABLESPACE USERS);

             To understand the HASH partitioning behavior, here is an example:     

The advantage of the HASH partitioning is that we can put a table on n disks (using n partitions) without having a preference or  rule for the partitioning method. 

LIST Partitioning

Sometimes a particular column could have ONLY specific values. Supposing a company has only 4 customers and a huge amount of transactions with each. In that case we can use the LIST partitioning method for the ORDERS (ORDERS_LIST) table: 

                CREATE TABLE ORDERS_LIST

                           (  ORDER_ID NUMBER,

                              ORDER_DATE DATE,

                              CUSTOMER_ID NUMBER,

                              PRICE NUMBER  )

                PARTITION BY LIST (CUSTOMER_ID)

                      (  PARTITION P1 VALUES (1) TABLESPACE USERS,

                         PARTITION P2 VALUES (2) TABLESPACE USERS,

                         PARTITION P3 VALUES (3) TABLESPACE USERS,

                         PARTITION P4 VALUES (4) TABLESPACE USERS);

             Here is an example of LIST partition functioning:  

More information about  this subject ( Table & Index partitioning in Oracle database ) you can get from  www.in-oracle.com

Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.

More information on this subject you can take from here.