In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Oracle Partion (on tables and indexes)

 Oracle Partion (on tables and indexes)

Here are the main topics for this article:

• Advantages of table partitioning

• RANGE Partitioning

• HASH Partitioning

• LIST Partitioning

• Index partitioning

• Composite Range 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:  

Index partitioning

The indexes could be indexed for the same reason the tables are. If the index is partitioned in the same manner as the base table there is a local index. A Global Partitioned Index is an index on a partitioned or non-partitioned table using a different partitioning key as the table. If the index is not partitioned the index is named Global Non-Partitioned index.

Composite Range partitioning

A Range partition could be sub-partitioned using Hash or List partitioning. In these cases we can have Composite Range-Hash Partitioning or Composite Range-List Partitioning. 

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> Oracle Partion (on tables and indexes)