Partitioning in Oracle

 Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition". From an "Application Development" perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to access a partitioned table if that application was initially written on non partitioned tables.

 

·       The process of decomposing a large table or index into multiple number of smaller units is called as partitioning, where each unit is called as a partition and treated as separate segment (space occupying object in database).

·       All partitions of a table or index must have same logical attributes like column name, data type. Constraints etc. and may have physical attributes like pctfree, pctused, tablespace etc.

 

·       All partitions of a table or index must be in tablespaces of same blocksize.

 

·       In oracle 9i, a table can be partitioned maximum up to 64000 partitions.

·       Any table can be partitioned except those tables containing columns having datatype long and longraw.

·       An index organized table can also be partitioned but only range partitioning and hash partitioning is applicable.

 

·       Advantage of partitioning is

o   Better query performance

o   Better manageability

o   Reduced recovery time

o   Import / export can be done at the “Partition Level".

 

When to partition:

o   If the size of the table is big (more than 2gb)

o   If the table contains historical data i.e. suppose a table contains data of 12 months, where only the current month data is updatable and all 11 months data are read-only.

Partitioning method

 

There are different types of partitioning method such as

 

·       Range partitioning

·       List partitioning

·       Hash partitioning

·       Composite partitioning

o   Range List partitioning

o   Range hash partitioning

 

Range partitioning

 

Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition.

Example:

 

SQL> CREATE TABLE SALES_2009 (

SALESMAN_ID VARCHAR2 (10) PRIMARY KEY,

SALESMAN_NAME VARCHAR2 (20),

SALES_AMOUNT NUMBER,

SALES_DATE DATE)

PARTITION BY RANGE (SALES_DATE)

(

PARTITION FIRST_QUARTER VALUES LESS THAN (TO_DATE (’01-APR-09’,’DD-MON-YYYY’)) TABLESPACE TB11,

PARTITION SECOND_QUARTER VALUES LESS THAN (TO_DATE (’01-APR-09’,’DD-MON-YYYY’)) TABLESPACE TB22,

PARTITION THIRD_QUARTER VALUES LESS THAN (TO_DATE (’01-APR-09’,’DD-MON-YYYY’)) TABLESPACE TB33,

PARTITION FOURTH_QUARTER VALUES LESS THAN (MAXVALUE) TABLESPACE TB44);

 

List partitioning

 

List partitioning allows greater flexibility in the mapping of rows to partitions than range partitioning. Since the data is distributed based on discrete column values, unordered and unrelated sets of data can be grouped together with no relationship between the partitions.

 

Example:

 

SQL> CREATE TABLE SALES_LIST (

SALESMAN_ID VARCHAR2 (10) PRIMARY KEY,

NAME VARCHAR2 (20),

SALES_AMOUNT NUMBER,

LOCATIONS VARCHAR2 (20))

PARTITION BY LIST (LOCATIONS)

(

PARTITION SALES_EAST VALUES (‘BHUBANESWAR’,’KOLKATA’) TABLESPACE TB11,

PARTITION SALES_WEST VALUES (‘MUMBAI’,’GOA’) TABLESPACE TB22,

PARTITION SALES_SOUTH VALUES (‘HYDERABAD’,’CHENAI’) TABLESPACE TB33,

PARTITION SALES_NORTH VALUES (DEFAULT) TABLESPACE TB44);

 

Splitting existing partition:

Example:

 

SQL> ALTER TABLE SALES_LIST

SPLIT PARTITION SALES_NORTH VALUES (‘DELHI’,’CHANDIGARH’)

INTO (PARTITION SALES_NORTH, PARTITION SALES_ABROAD TABLESPACE TB55);

 

NOTE: the existing partition sales_north will be divided into two partitions i.e. sales_north ad sales_abroad.

 

If the location column contains value DELHI or CHANDIGARH, which will come under the partition SALES_NORTH and that will be stored in tablespace TB44 and all other values will come under the partition SALES_ABROAD and that will be stored in tablespace TB55.

 

Hash partitioning

Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16...) and can be specified by the PARTITIONS...STORE IN clause

 

SQL> CREATE TABLE STUDENT (

ROLLNO VARCHAR2 (10) PRIMARY KEY,

NAME VARCHAR2 (20),

ADDRESS VARCHAR2 (20))

PARTITION BY HASH (ROLLNO)

(

PARTITION P1 TABLESPACE TB11,

PARTITION P2 TABLESPACE TB22,

PARTITION P3 TABLESPACE TB33,

PARTITION P4 TABLESPACE TB44) ;

 

NOTE: Here 4 partitions will be created such as p1, p2, p3, p4 and those will be stored in tablespaces TB1, TB2, TB3, TB4 respectively.

 

Composite partitioning

Range hash partitioning

This is basically a combination of range and hash partitions. So basically, the first step is that the data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data. 

Example:

 

SQL> CREATE TABLE STUDENT (

ROLLNO VARCHAR2 (10) PRIMARY KEY,

NAME VARCHAR2 (10),

ADDRESS VARCHAR2 (20),

GRADE VARCHAR2 (20))

PARTITION BY RANGE (GRADE)

SUBPARTITION BY HASH (ROLLNO)

SUBPARTITION TEMPLATE

(

SUBPARTITION SP1 TABLESPACE TB11,

SUBPARTITION SP2 TABLESPACE TB22,

SUBPARTITION SP3 TABLESPACE TB33,

SUBPARTITION SP4 TABLESPACE TB44)

(

PARTITION P1 VALUES LESS THAN (‘B’),

PARTITION P2 VALUES LESS THAN (‘C’),

PARTITION P3 VALUES LESS THAN (‘D’),

PARTITION P4 VALUES LESS THAN (MAXVALUE));

 

NOTE: First student table will be partitioned by range partitioning method into 4 partitions such as P1, P2, P3, and P4. Then, each partition will again partitioned by using hash partitioning method.

For range partitioning method, grade is the partition key and for hash partitioning method rollno is the partition key.

When we use the template, oracle names the subpartition by combining the partition name, an underscore and the subpartition name from the template. Then oracle places this subpartition in the tablespace specified in the template such as

 

P1_SP1 è TB11

P2_SP2 è TB22

P3_SP3 è TB33

P4_SP4 è TB44

 

Range list partitioning

 

This is also a combination of Range and List Partitions, basically first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using List key values. Each sub partitions individually represent logical subset of the data not like composite Range-Hash Partition.

 

Example:

 

SQL> CREATE TABLE CUSTOMER_PART (

CUSTOMER_ID VARCHAR2 (10) PRIMARY KEY,

NAME VARCHAR2 (20),

LOCATION VARCHAR2 (20),

CREDIT_LIMIT NUMBER)

PARTITION BY RANGE (CREDIT_LIMIT)

SUBPARTITION BY LIST (LOCATION)

SUBPARTITION TEMPLATE

(

SUBPARTITION EAST VALUES (‘BHUBANESWAR’,’KOLKATA’) TABLESPACE TB11,

SUBPARTITION WEST VALUES (‘MUMBAI’,’GOA’) TABLESPACE TB22,

SUBPARTITION SOUTH VALUES (‘HYDERABAD’,’CHENAI’) TABLESPACE TB33,

SUBPARTITION NORTH VALUES (‘DELHI’,’CHANDIGARH’) TABLESPACE TB44)

(

PARTITION P1 VALUES LESS THAN (10000),

PARTITION P2 VALUES LESS THAN (20000),

PARTITION P3 VALUES LESS THAN (30000),

PARTITION P4 VALUES LESS THAN (40000)));

 

Partitioning existing table:

 

The ALTER TABLE ... EXCAHNGE PARTITION ... syntax can be used to partition an existing table, as shown by the following example. First we must create a non-partitioned table to act as our starting point.

 

SQL> CREATE TABLE STUDENT (

     ROLLNO VARCHAR2 (10) PRIMARY KEY,

      NAME VARCHAR2 (20));

 

SQL> CREATE TABLE NEW_STUDENT (

     ROLLNO VARCHAR2 (10) PRIMARY KEY,

     NAME VARCHAR2 (20))

     PARTITION BY RANGE (ROLLNO)

     (

     PARTITION P30 VALUES LESS THAN (30) TABLESPACE TB11,

     PARTITION P60 VALUES LESS THAN (60) TABLESPACE TB22,

     PARTITION P100 VALUES LESS THAN (MAXVALUE) TABLESPACE TB33);

SQL> ALTER TABLE NEW_STUDENT

     EXCHANGE PARTITION P30

     WITH TABLE STUDENT

     WITHOUT VALIDATION;

 

SQL> ALTER TABLE NEW_STUDENT

     EXCHANGE PARTITION P60

     WITH TABLE STUDENT

     WITHOUT VALIDATION;

 

SQL> ALTER TABLE NEW_STUDENT

     EXCHANGE PARTITION P100

     WITH TABLE STUDENT

     WITHOUT VALIDATION;

 

 

SQL> SELECT TABLE_NAME, PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='NEW_STUDENT';

 

OUTPUT:

 

TABLE_NAME                     PARTITION_NAME

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

NEW_STUDENT                    P100

NEW_STUDENT                    P30

NEW_STUDENT                    P60

 

SQL> select * from student;

 

OUTPUT:

 

no rows selected

 

SQL> select * from new_student;

OUTPUT:

 

ROLLNO     NAME

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

1          SANTANU

20         SAAN

50         BUBU

90         BUBUN

 

SQL> drop table student;

 

OUTPUT:

 

Table dropped.

 

SQL> rename new_student to student;

 

OUTPUT:

 

Table renamed.

 

SQL> select * from student;

 

OUTPUT:

 

ROLLNO     NAME

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

1          SANTANU

20         SAAN

50         BUBU

90         BUBUN

 

 

SQL> SELECT TABLE_NAME, PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='STUDENT';

 

OUTPUT:

 

TABLE_NAME                     PARTITION_NAME

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

STUDENT                        P100

STUDENT                        P30

STUDENT                        P60

 

ADDING NEW PARTITION:

 

RANGE PARTITION:

 

SQL> ALTER TABLE <table_name>

      ADD PARTITION <partition_name> VALUES LESS THAN (‘<value>‘)

      TABLESPACE <tablespace_name>;

 

LIST PARTITION:

SQL> ALTER TABLE <table_name>

   ADD PARTITION <partition_name> VALUES ('<value>', '<value>')

      TABLESPACE <tablespace_name>;

 

 

HASH PARTITION:

 

SQL> ALTER TABLE <table_name>

      ADD PARTITION <partition_name> TABLESPACE <tablespace_name>;

DROPPING PARTITION AND SUBPARTITION:

 

SQL> ALTER TABLE <table_name>

      DROP PARTITION <partition_name>;

 

SQL> ALTER TABLE <table_name>

      DROP SUBPARTITION <subpartition_name>;

 

SQL> ALTER TABLE <table_name>

      DROP PARTITION <partition_name> UPDATE GLOBAL INDEXES;

 

ADDING/DROPPING VALUES FOR PARTITION:

 

SQL> ALTER TABLE <table_name>

   MODIFY PARTITION <partition_name>

      ADD VALUES ('<value>', '<value>');

 

SQL> ALTER TABLE <table_name>

   MODIFY SUBPARTITION <subpartition_name>

      ADD VALUES ('<value>', '<value>');

SQL> ALTER TABLE <table_name>

   MODIFY PARTITION <partition_name>

      DROP VALUES ('<value>', '<value>');

MOVING TABLE PARTITION:

SQL> ALTER TABLE <table_name> MOVE PARTITION <partition_name>

     TABLESPACE <tablespace_name>;

SQL> ALTER TABLE <table_name> MOVE SUBPARTITION <subpartition_name>

     TABLESPACE <tablespace_name>;

 

RENAMING TABLE PARTITION:

 

SQL> ALTER TABLE <table_name> RENAME PARTITION <old_name> TO <new_name>;

SQL> ALTER TABLE <table_name> RENAME SUBPARTITION <old_name> TO <new_name>;

Information Regarding Partitioned Tables:

 

o   DBA_TAB_PARTITIONS

o   ALL_TAB_PARTITIONS

o   USER_TAB_PARTITIONS

o   DBA_TAB_SUBPARTITIONS

o   ALL_TAB_SUBPARTITIONS

o   USER_TAB_SUBPARTITIONS

o   DBA_PART_TABLES

o   ALL_PART_TABLES

o   USER_PART_TABLES

o   DBA_PART_KEY_COLUMNS

o   ALL_PART_KEY_COLUMNS

o   USER_PART_KEY_COLUMNS

Information Regarding Partitioned Indexes:

 

o   DBA_IND_PARTITIONS

o   ALL_IND_PARTITIONS

o   USER_IND_PARTITIONS

o   DBA_IND_SUBPARTITIONS

o   ALL_IND_SUBPARTITIONS

o   USER_IND_SUBPARTITIONS

o   DBA_PART_INDEXES

o   ALL_PART_INDEXES

o   USER_PART_INDEXES

o   DBA_SUBPART_KEY_COLUMNS

o   ALL_SUBPART_KEY_COLUMNS

o   USER_SUBPART_KEY_COLUMNS

Partitioned Index:

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes).

 

Local Partitioned Index:

All index entries in a single partition will correspond to a single table partition (equipartitioned). They are created with the LOCAL keyword and support partition independence. Equipartioning allows oracle to be more efficient whilst devising query plans.

The reason for this is equipartitioning is each partition of a local index is associated with exactly one partition of the table. This enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.

Example:

SQL>  CREATE TABLE employees (

       employee_id NUMBER(4) PRIMARY KEY,

       last_name VARCHAR2(10), 

       department_id NUMBER(2))

       PARTITION BY RANGE (department_id)

       (

  PARTITION P1 VALUES LESS THAN (11) TABLESPACE TB11, 

  PARTITION P2 VALUES LESS THAN (21) TABLESPACE TB22,

  PARTITION P3 VALUES LESS THAN (31) TABLESPACE TB33);

SQL> CREATE INDEX LOCAL_INDEX ON employees (department_id) LOCAL;

SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='EMPLOYEES';

OUTPUT:

TABLE_NAME             PARTITION_NAME                 TABLESPACE_NAME

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

EMPLOYEES                      P1                             TB11

EMPLOYEES                      P2                             TB22

EMPLOYEES                      P3                             TB33

SQL> SELECT INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='LOACL_INDEX';

OUTPUT:

INDEX_NAME             PARTITION_NAME              TABLESPACE_NAME

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

LOCAL_INDEX                  P1                          TB11

LOCAL_INDEX                  P2                          TB22

LOCAL_INDEX                  P3                          TB33

 

Global Partitioned Index:

Index in a single partition may correspond to multiple table partitions. They are created with the GLOBAL keyword and do not supports partition independences. Global indexes can only be range partitioned and may be partitioned in such a fashion that they look equipartitioned, but Oracle will not take advantage of this structure.

Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method

You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement.

Example:

SQL> SQL> CREATE INDEX GLOBAL_INDEX ON EMPLOYEES(EMPLOYEE_ID)

     GLOBAL PARTITION BY RANGE(EMPLOYEE_ID)

     (

     PARTITION IND1 VALUES LESS THAN(100) TABLESPACE TB44,

     PARTITION IND2 VALUES LESS THAN(200) TABLESPACE TB55,

     PARTITION IND3 VALUES LESS THAN(MAXVALUE) TABLESPACE TB66);

SQL> SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='EMPLOYEES';

 

 

OUTPUT:

TABLE_NAME            PARTITION_NAME             TABLESPACE_NAME

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

EMPLOYEES                   P1                         TB11

EMPLOYEES                   P2                         TB22

EMPLOYEES                   P3                         TB33

SQL> SELECT INDEX_NAME,PARTITION_NAME,TABLESPACE_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='GLOBAL_INDEX';

OUTPUT:

INDEX_NAME            PARTITION_NAME             TABLESPACE_NAME

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

GLOBAL_INDEX                IND1                        TB44

GLOBAL_INDEX                IND2                        TB55

GLOBAL_INDEX                IND3                        TB66

Partition Pruning

The Oracle server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements. In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.

For each SQL statement, depending on the selection criteria specified, unneeded partitions or subpartitions can be eliminated. For example, if a query only involves March sales data, then there is no need to retrieve data for the remaining eleven months. Such intelligent pruning can dramatically reduce the data volume, resulting in substantial improvements in query performance.

Partition Pruning Example

We have a partitioned table called orders. The partition key for orders is order_date. Let's assume that orders has six months of data, January to June, with a partition for each month of data. If the following query is run:

SQL> SELECT SUM (value) FROM orders WHERE order_date BETWEEN '28-MAR-98' AND '23-APR-98';

Partition pruning is achieved by:

• First, partition elimination of January, February, May, and June data partitions. Then either:

• An index scan of the March and April data partition due to high index selectivity

Or

• A full scan of the March and April data partition due to low index selectivity

Clustering in Oracle

A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Oracle Database stores together all the rows from all the tables that share the same cluster key.

A cluster provides an optional method of storing table data. A cluster is made up of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together.

For example, the EMPLOYEES and DEPARTMENTS table share the DEPARTMENT_ID column. When you cluster the EMPLOYEES and DEPARTMENTS tables, Oracle physically stores all rows for each department from both the EMPLOYEES and DEPARTMENTS tables in the same data blocks. You should not use clusters for tables that are frequently accessed individually.

To create a cluster in your own schema, you must have CREATE CLUSTER system privilege. To create a cluster in another user's schema, you must have CREATE ANY CLUSTER system privilege.

Oracle Database does not automatically create an index for a cluster when the cluster is initially created. Data manipulation language (DML) statements cannot be issued against cluster tables in an indexed cluster until you create a cluster index with a CREATE INDEX statement.

 

Scenario-I

Creating a Cluster

 

SQL> CREATE CLUSTER EMP_DEPT (

     DEPARTMENT_ID NUMBER (4))

     TABLESPACE TB33;

NOTE: Department_id is the cluster key.

 

Creating a Cluster Index

 

The following statement creates the cluster index on the cluster key of EMP_DEPT:

 

SQL> CREATE INDEX CLUSTER_IND ON CLUSTER EMP_DEPT;

 

        After creating the cluster index, we can add tables to the index and perform DML operations on those tables.

Adding Tables to a Cluster

SQL> CREATE TABLE EMP_10

     CLUSTER EMP_DEPT (DEPARTMENT_ID)

     AS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=10;

 

SQL> CREATE TABLE DEPT_10

     CLUSTER EMP_DEPT (DEPARTMENT_ID)

     AS SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID=10;

 

 

Scenario-II

Creating Cluster

SQL> CREATE CLUSTER EMP_DEPT (

     DEPTNO VARCHAR2 (20))

     TABLESPACE TB33;

 

Creating Index

SQL> CREATE INDEX EMP_DEPT_IND ON CLUSTER EMP_DEPT;

 

Creating Clustered Tables

 

SQL> CREATE TABLE EMP (

     EMPNO VARCHAR2 (10),

     NAME VARCHAR2 (20),

     DEPTNO VARCHAR2 (20))

     CLUSTER EMP_DEPT (DEPTNO);

 

SQL> CREATE TABLE DEPT (

     DEPTNO VARCHAR2 (20),

     DEPT_NAME VARCHAR2 (20),

     LOCATION VARCHAR2 (20))

     CLUSTER EMP_DEPT (DEPTNO);

Dropping Clustered Tables

To drop a cluster, your schema must contain the cluster or you must have the DROP ANY CLUSTER system privilege. You do not have to have additional privileges to drop a cluster that contains tables, even if the clustered tables are not owned by the owner of the cluster.

To drop a cluster that contains no tables, and its cluster index, issue the following statement.

SQL> DROP CLUSTER emp_dept;

If the cluster contains one or more clustered tables and you intend to drop the tables as well, add the INCLUDING TABLES option of the DROP CLUSTER statement, as follows:

SQL> DROP CLUSTER emp_dept INCLUDING TABLES;

If the INCLUDING TABLES option is not included and the cluster contains tables, an error is returned.

Information Regarding Clusters

·       DBA_CLUSTERS

·       ALL_CLUSTERS

·       USER_CLUSTERS

·       DBA_TABLES

·       ALL_TABLES

·       USER_TABLES

SQL> select cluster_name, table_name from dba_tables where cluster_name='EMP_DEPT';

OUTPUT:

CLUSTER_NAME                   TABLE_NAME

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

EMP_DEPT                                        DEPT

EMP_DEPT                                        EMP

SQL> select T.cluster_name, T.table_name, C.TABLESPACE_NAME from dba_tables T, DBA_CLUSTERS C where T.cluster_name='EMP_DEPT' AND T.CLUSTER_NAME = C.CLUSTER_NAME;

 

OUTPUT:

 

CLUSTER_NAME             TABLE_NAME            TABLESPACE_NAME

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

EMP_DEPT                    EMP                      TB33

EMP_DEPT                    DEPT                     TB3