ORACLE TABLE PARTITIONING

What is the use of PARTITIONING in ORACLE? How it helps in Organizing Table Data?

Availability, Manageability, Performance

PARTITION: Decompose a table or index into smaller, more manageable pieces, called partitions. 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.

When we store our data in a non-partitioned table, without indexes, we will always have a full table scan.

Suppose, your Table SALES_ORDER containing millions of records and then you perform the following:

SELECT SUM(TOTAL_SALE) From SALES_ORDER Where Year=2016;

SELECT PRODUCT, SUM(TOTAL_SALE) From SALES_ORDER

Where Year=2015

Group By PRODUCT;

Now, in above two Queries ORACLE will always do Full Table scan.  If you partition this table according to year, then the performance will be improved since oracle will scan only a single partition instead of whole table.

With table partitioning, a table can be physically divided into multiple smaller tables, called partitions, while logically it stays one table. This means your code stays the same, but full partition scans will be executed instead of a full table scan.

PARTITION KEY: Each row in a partitioned table is unambiguously assigned to a single partition. These partitions are created based on a key. Depending on which value a certain column has, the record will be stored in a certain partition. It’s important to choose a column that is often used in queries as our key.

SUBPARTITION: Partitions created within partitions. They are just partitions themselves and there is nothing special about them.

COMPOSITE PARTITIONING: Composite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash and range-list composite partitioning.

INTERVAL PARTITIONING: Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.

GLOBAL INDEX: A single index covering all partitions.

LOCAL INDEX: Separate indexes for each partition. A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.

PARTITION PRUNING: Oracle optimizes SQL statements to mark the partitions or Subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or Subpartitions from access. Partition pruning is the skipping of unnecessary index and data partitions or Subpartitions by a query.

When to Partition a Table?

·       Tables size greater than 2 GB should always be considered as candidates for partitioning.

·       Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

·       When the contents of a table need to be distributed across different types of storage devices.

Advantages of PARTITIONING

• Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
• Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.
• Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
• Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
• Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.

PARTITIONING Types:

•         RANGE PARTITIONING AND INTERVAL PARTITIONING
•         LIST PARTITIONING
•         HASH PARTITIONING
•         COMPISITE PARTITIONING

RANGE PARTITIONING AND INTERVAL PARTITIONING

RANGE PARTITIONING: The table is divided in ranges, typically used for Date Ranges. This is beneficial when the filters using in between, greater than or less than. Table is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.

CREATE TABLE ORDERS
(
ORDER_ID NUMBER,
CUST_ID NUMBER,
ORDER_DATE DATE,
ORDER_STATUS VARCHAR2(10)
)
PARTITION BY RANGE(ORDER_DATE)
(PARTITION ORDERS2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','DD_MON-YYYY'))
,PARTITION ORDERS2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD_MON-YYYY'))
,PARTITION orders2016 VALUES LESS THAN (MAXVALUE));

Let's Insert Data into above Table

SQL> INSERT INTO ORDERS VALUES(1001,20001,'14-OCT-2015','OPEN');
SQL> INSERT INTO ORDERS VALUES(1002,20002,'24-NOV-2016','PENDING');
SQL> INSERT INTO ORDERS VALUES(1003,20003,'17-FEB-2017','CLOSED');
COMMIT;
SQL> SELECT * From ORDERS;
  ORDER_ID    CUST_ID ORDER_DAT ORDER_STAT
---------- ---------- --------- ----------
      1001      20001 14-OCT-15 OPEN
      1002      20002 24-NOV-16 PENDING
      1003      20003 17-FEB-17 CLOSED

Let’s check the Partitions:

SQL> Exec Dbms_Stats.Gather_Table_Stats('MYUSER','ORDERS');
SQL> SELECT Table_Name,Partition_Name,Num_Rows
  2  From User_Tab_Partitions
  3  Where Table_Name='ORDERS';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
ORDERS                         ORDERS2014                              0
ORDERS                         ORDERS2015                              1
ORDERS                         ORDERS2016                              2
To SELECT Data from any Partition you need to do the following:
SQL> SELECT * FROM ORDERS PARTITION(ORDERS2016);

  ORDER_ID    CUST_ID ORDER_DAT ORDER_STAT
---------- ---------- --------- ----------
      1002      20002 24-NOV-16 PENDING
      1003      20003 17-FEB-17 CLOSED

ORA-14400: Inserted partition key does not map to any partition: When inserting a record that does not belong to any of the partitions, an ORA-14400 error will be raised. If you create a partition with “MAXVALUE”, it will be used as default partition. New partitions can be added or dropped manually when needed, or they can be created automatically with interval partitioning

INTERVAL PARTITIONING: Interval partitioning can simplify the manageability by automatically creating the new partitions as needed by the data.  Interval partitioning is enabled in the table's definition by defining one or more range partitions and including a specified interval. 

IF we take an example of Above Table and let’s say we need to add Interval PARTITION on it. Then one way you need to recreate that Table or Add new partition into existing Table.

New Table with RANGE INTERVAL PARTITIONING:

CREATE TABLE ORDERS
(
ORDER_ID NUMBER,
CUST_ID NUMBER,
ORDER_DATE DATE,
ORDER_STATUS VARCHAR2(10)
)
PARTITION BY RANGE(ORDER_DATE)
INTERVAL (NUMTOYMINTERVAL(1,’YEAR’))
(PARTITION ORDERS2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','DD_MON-YYYY'))
,PARTITION ORDERS2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD_MON-YYYY')));

Now, Oracle will automatically create a new partition for the new YEAR.

ADD INTERVAL PARTITION to the EXISTING Table:

SQL> ALTER TABLE ORDERS DROP PARTITION ORDERS2016;
Table altered.
SQL> SELECT * From ORDERS;
  ORDER_ID    CUST_ID ORDER_DAT ORDER_STAT
---------- ---------- --------- ----------
      1001      20001 14-OCT-15 OPEN
SQL> ALTER TABLE ORDERS SET INTERVAL (NUMTOYMINTERVAL(1,'YEAR'));
Table altered.
SQL> INSERT INTO ORDERS VALUES(1002,20002,'24-NOV-2016','PENDING');
1 row created.
SQL> INSERT INTO ORDERS VALUES(1003,20003,'17-FEB-2017','CLOSED');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * From ORDERS;
  ORDER_ID    CUST_ID ORDER_DAT ORDER_STAT
---------- ---------- --------- ----------
      1001      20001 14-OCT-15 OPEN
      1002      20002 24-NOV-16 PENDING
      1003      20003 17-FEB-17 CLOSED

Let's check the Partitions:

SQL> Exec Dbms_Stats.Gather_Table_Stats('MYUSER','ORDERS');
PL/SQL procedure successfully completed.
SQL> SELECT Table_Name,Partition_Name,Num_Rows
  2  From User_Tab_Partitions
  3  Where Table_Name='ORDERS';

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
ORDERS                         ORDERS2014                          0
ORDERS                         ORDERS2015                          1
ORDERS                         SYS_P21                             1
ORDERS                         SYS_P22                             1

LIST PARTITIONING:

The data distribution is defined by a list of values of the partitioning key. This is useful for discrete lists. Example Regions, States.

Suppose you want to partition your stores into specific regions based on their location within each state, as seen in the following table.

REGION	MARKET
North America	Bermuda, Canada, United States
Europe	Belgium, Bulgaria, Switzerland, Finland, United Kingdom
CIS	Georgia, Kazakhstan, Tajikistan, Ukraine
Asia & Pacific	Australia, Bangladesh, China, Hong Kong, India

CREATE TABLE ORDER_DETAILS
(
ORDER_NUM NUMBER,
CUST_ID NUMBER,
STORE_ID NUMBER,
STORE_ADDR VARCHAR2(40),
REGION VARCHAR2(30),
MARKET VARCHAR2(200 CHAR)
)
PARTITION BY LIST(MARKET)
(
PARTITION NORTH_AMERICA VALUES('Bermuda','Canada','United States'),
PARTITION EUROPE VALUES('Belgium','Bulgaria','Switzerland','Finland','United Kingdom'),
PARTITION CIS VALUES('Georgia','Kazakhstan','Tajikistan','Ukraine'),
PARTITION APAC VALUES ('Australia', 'Bangladesh', 'China', 'Hong Kong', 'India'),
PARTITION REGION_NULL VALUES (NULL),
PARTITION REGION_UNKNOWN VALUES (DEFAULT)
);

In this example, we have created a partitioned table with a partition for each of the four regions. We have defined each Market in which we have a store as part of one of the partition value lists.

HASH PARTITIONING:

Sometimes it may not be possible to define the ranges of—or a set of known values for—each partition. In such a case, the hash partition is useful. It makes sure the records are evenly spread over all the partitions. Hash partitioning is a Partitioning technique where a hash key is used to distribute rows evenly across the different partitions (Sub-Tables). This is basically used where there are no searches on ranges and Value is hashed among the different distributions.

EMPLOYEE LIST Table

A typical example is a table, EMPLOYEE_LIST, containing Employees, with the EMP_ID column as the primary key. The EMP_ID value is an incrementally increasing but rather meaningless number, so a RANGE Partition based on these values may also be rather meaningless.

You can partition this table by using HASH, where a hash function is applied to the partition key of each row and, based on the output, the row is placed in an appropriate partition. All the hash partitions hold an equal number of rows.

CREATE TABLE EMPLOYEE_LIST(EMP_ID NUMBER

                          ,Name VARCHAR2(50)

                          ,Location VARCHAR2(2))

  PARTITION BY HASH(Name)

  PARTITIONS 5;

SQL> SELECT Table_Name,Partition_Name,Num_Rows

  2  From User_Tab_Partitions

  3  Where Table_Name='EMPLOYEE_LIST';

TABLE_NAME                     PARTITION_NAME              NUM_ROWS

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

EMPLOYEE_LIST                  SYS_P41                                 0

EMPLOYEE_LIST                  SYS_P42                                 0

EMPLOYEE_LIST                  SYS_P43                                 0

EMPLOYEE_LIST                  SYS_P44                                 0

EMPLOYEE_LIST                  SYS_P45                                 0

SQL> INSERT INTO EMPLOYEE_LIST VALUES(1001,'Ravi','UK');

1 row created.

SQL> INSERT INTO EMPLOYEE_LIST VALUES(1002,'Rajan','US');

1 row created.

SQL> INSERT INTO EMPLOYEE_LIST VALUES(1003,'Karan','US');

1 row created.

SQL> INSERT INTO EMPLOYEE_LIST VALUES(1004,'Kiran','UK');

1 row created.

SQL> INSERT INTO EMPLOYEE_LIST VALUES(1005,'Shekhar','AS');

1 row created.

SQL> INSERT INTO EMPLOYEE_LIST VALUES(1006,'Ram','UK');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> Exec Dbms_Stats.Gather_Table_Stats('MYUSER','EMPLOYEE_LIST');

PL/SQL procedure successfully completed.

SQL> SELECT Table_Name,Partition_Name,Num_Rows

  2  From User_Tab_Partitions

  3  Where Table_Name='EMPLOYEE_LIST';

TABLE_NAME                     PARTITION_NAME             NUM_ROWS

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

EMPLOYEE_LIST                  SYS_P41                                 1

EMPLOYEE_LIST                  SYS_P42                                 3

EMPLOYEE_LIST                  SYS_P43                                 1

EMPLOYEE_LIST                  SYS_P44                                 1

EMPLOYEE_LIST                  SYS_P45                                 0

You can also specify each of the Partition with separate Tablespace as in below Example Hash Partitioned on Employee ID. IF we don’t specify Tablespace it gets assigned to default Tablespace. In all above examples, all the Partitions were created in the same Tablespace.

CREATE TABLE EMPLOYEE_LIST(EMP_ID NUMBER

                          ,Name VARCHAR2(50)

                          ,Location VARCHAR2(2))

  Partition By HASH(EMP_ID) (

  Partition E1 Tablespace Emp1,

  Partition E2 Tablespace Emp2,

  Partition E3 Tablespace Emp3,

  Partition E4 Tablespace Emp4,

  Partition E5 Tablespace Emp5

);

Also, can be done as below:

CREATE TABLE EMPLOYEE_LIST(EMP_ID NUMBER

                          ,Name VARCHAR2(50)

                          ,Location VARCHAR2(2))

Partition By HASH(EMP_ID) (

PARTITIONS 3

STORE IN (TBS1, TBS2, TBS33);

Let’s take one more example of HASH Partitioning:

CREATE TABLE MyObjects(ID, NAME)
PARTITION BY HASH(ID)
PARTITIONS 6
AS 
SELECT ROWNUM, OBJECT_NAME
From ALL_OBJECTS Where ROWNUM<=32000;

Check the Partitions:

SQL> Exec Dbms_Stats.Gather_Table_Stats('MYUSER','MYOBJECTS');
SQL> SELECT Table_Name,Partition_Position,Partition_Name,Num_Rows, Tablespace_Name
  2  From User_Tab_Partitions
  3  Where Table_Name='MYOBJECTS';
TABLE_NAME                     PARTITION_POSITION PARTITION_NAME                   NUM_ROWS TABLESPACE_NAME
------------------------------ ------------------ ------------------------------ ---------- ------------------------------
MYOBJECTS                                       1 SYS_P52                              3981 USERS
MYOBJECTS                                       2 SYS_P53                              3988 USERS
MYOBJECTS                                       3 SYS_P54                              8246 USERS
MYOBJECTS                                       4 SYS_P55                              7945 USERS
MYOBJECTS                                       5 SYS_P56                              3912 USERS
MYOBJECTS                                       6 SYS_P57                              3928 USERS

 If you see ORACLE has divided 4 almost equal partitions and two big partitions (3 and 4). When you add more partition to this table, Oracle will distribute data from big partitions by decomposing them into smaller Partitions.

SQL> ALTER TABLE MYOBJECTS ADD PARTITION;

Let’s check the Partitions now:

SQL> Exec Dbms_Stats.Gather_Table_Stats('MYUSER','MYOBJECTS');
SQL> SELECT Table_Name,Partition_Position,Partition_Name,Num_Rows, Tablespace_Name
  2  From User_Tab_Partitions
  3  Where Table_Name='MYOBJECTS';

TABLE_NAME                     PARTITION_POSITION PARTITION_NAME                   NUM_ROWS TABLESPACE_NAME
------------------------------ ------------------ ------------------------------ ---------- ------------------------------
MYOBJECTS                                       1 SYS_P52                              3981 USERS
MYOBJECTS                                       2 SYS_P53                              3988 USERS
MYOBJECTS                                       3 SYS_P54                              4112 USERS
MYOBJECTS                                       4 SYS_P55                              7945 USERS
MYOBJECTS                                       5 SYS_P56                              3912 USERS
MYOBJECTS                                       6 SYS_P57                              3928 USERS
MYOBJECTS                                       7 SYS_P58                              4134 USERS

As you can see, Partition 3 divided into two and half its data copied into newly created Partition (7).

Add one more partition and check the stats which will split Partition 4 into two by decomposing data evenly across all the Partitions.

SQL> ALTER TABLE MYOBJECTS ADD PARTITION;

Table altered.

SQL> SELECT Table_Name,Partition_Position,Partition_Name,Num_Rows, Tablespace_Name
  2  From User_Tab_Partitions
  3  Where Table_Name='MYOBJECTS';

TABLE_NAME                     PARTITION_POSITION PARTITION_NAME                   NUM_ROWS TABLESPACE_NAME
------------------------------ ------------------ ------------------------------ ---------- ------------------------------
MYOBJECTS                                       1 SYS_P52                              3981 USERS
MYOBJECTS                                       2 SYS_P53                              3988 USERS
MYOBJECTS                                       3 SYS_P54                              4112 USERS
MYOBJECTS                                       4 SYS_P55                              3954 USERS
MYOBJECTS                                       5 SYS_P56                              3912 USERS
MYOBJECTS                                       6 SYS_P57                              3928 USERS
MYOBJECTS                                       7 SYS_P58                              4134 USERS
MYOBJECTS                                       8 SYS_P59                              3991 USERS

COMPOSITE PARTITIONING:

To create a composite partitioned table, you start by using the PARTITION BY [RANGE | LIST] clause of a CREATE TABLE statement. Next, you specify a SUBPARTITION BY [RANGE | LIST | HASH] clause that follows similar syntax and rules as the PARTITION BY [RANGE | LIST | HASH] clause. The individual PARTITION and SUBPARTITION or SUBPARTITIONS clauses, and optionally a SUBPARTITION TEMPLATE clause, follow.

Composite partitioning is a partitioning technique that combines some of the other partitioning methods. The table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method.

The following combinations of basic partitioning result into valid composite partitioning strategies, namely:

·                Range-Range

·                Range-List

·                Range-Hash

·                List-List

·                List-Range

·                List-Hash

·                Interval Partitioning

The Interval Partitioning strategy is fundamentally a special implementation of Range partitioning which we have already discussed, which maps primarily a DATE, TIMESTAMP data type to a numeric interval, using the INTERVAL keyword, use as a partition range marker. The functions NUMTOYMINTERVAL and NUMTODSINTERVAL are commonly used. Interval partitioning can occur as a single-level strategy or composite option in combination with all other options, namely, Range, Hash and List.

CREATE TABLE ORDER_HISTORY(

ORDER_ID       NUMBER,

ORDER_DATE     DATE,

PROD_ID        NUMBER,

QUANTITY       NUMBER)

PARTITION BY RANGE(ORDER_DATE)

SUBPARTITION BY HASH(PROD_ID) 

SUBPARTITIONS 4 -- STORE IN(TS1, TS2, TS3, TS4)

( 

PARTITION P1 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')),

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

PARTITION P3 VALUES LESS THAN (TO_DATE('01-MAR-2017', 'DD-MON-YYYY')),

PARTITION P4 VALUES LESS THAN (MAXVALUE)

);

Same can be done with other combination based on our requirement.

LISTING INFORMATION ABOUT PARTITION TABLES

To see how many partitioned tables are there in your schema give the following statement

Select * From USER_PART_TABLES;

To see on partition level partitioning information which we have already used above

Select * From USER_TAB_PARTITIONS;