Partitioning

Oracle Partitioning is a separately licensed option of the Enterprise Edition of the Oracle database. It is also available (and covered by the free license) in Oracle XE 18c. Partitioning allows DBAs to split large tables into more manageable "sub-tables", called partitions, to improve database performance, manageability and availability.

Partitioning is basically a divide-and-conquer approach to scale to very large database sizes. It improves manageability by allowing partitions to be added, loaded, indexed and removed while the database is on-line and users are working against these tables. Partitioning can potentially make the database faster the bigger it gets.

History

Oracle Partitioning was first introduced in Oracle 8 in 1997.

Oracle 10g R2 allowed more than 1 million partitions per table. It also introduced multidimensional pruning and fast table drops.

Starting with Oracle 11g, Oracle provides a partition advisor to assist customers to choose the best partitioning method for their data.

Advantages

Some of the advantages offered:

• Partitions can be stored in different tablespaces.

• Partitions can be added/ removed while users are working. DBAs can perform maintenance without having to bring down an entire table (import/ export/ load/ etc).

• Data can be selected from targeted partitions without having to scan all partitions for rows (partition pruning). The SQL optimizer will bypass partitions that don't contain data pertinent to the query being solved.

Partitioning types

Available partitioning types:

• Range partitioning (introduced in Oracle 8)
• Hash partitioning (introduced in Oracle 8i)
• Composite partitioning (introduced in Oracle 8i)
• List partitioning (introduced in Oracle 9i)
• Interval partitioning (introduced in Oracle 11g)
• System partitioning (introduced in Oracle 11g)
• Reference partitioning (introduced in Oracle 11g)

Partition compression

Starting from Oracle 11g individual partitions can be compressed:

• Compression as high as 3.5 to 1 is possible
• Compressed tables now support: DML Statements, Add and Drop Column, Partition level COMPRESS or NOCOMPRESS.
• ALTER TABLE... COMPRESS;
• ALTER TABLE... NOCOMPRESS;
• Table compression now supported for OLTP

As we know that in 10g we were able to compress the whole table neither it was apply to individual partition but in 11g we can use compress on partition level. Let's check the compression feature for partitions.

SQL>CREATE TABLE test
           (sno       NUMBER(6),
            last_name VARCHAR2(30),
            salary    NUMBER(6))
          PARTITION BY RANGE (salary)
           INTERVAL  (5000)
          (
     PARTITION p1 VALUES LESS THAN (5000)   COMPRESS,
     PARTITION p2 VALUES LESS THAN (10000)  NOCOMPRESS,
     PARTITION p3 VALUES LESS THAN (15000)  COMPRESS,
     PARTITION p4 VALUES LESS THAN (20000)) NOCOMPRESS;

SQL> SELECT table_name, partition_name, compression
 2  FROM user_tab_partitions
 3  WHERE table_name='TEST';

TABLE_NAME      PARTITION_NAME              COMPRESS
--------------- --------------------------- --------------
TEST            P1                          ENABLED
TEST            P2                          DISABLED
TEST            P3                          ENABLED
TEST            P4                          DISABLED

We can define compression for whole partition by using single keyword like this.

SQL> CREATE TABLE test
 2              (sno       NUMBER(6),
 3               last_name VARCHAR2(30),
 4               salary    NUMBER(6))
 5          COMPRESS
 6             PARTITION BY RANGE (salary)
 7              INTERVAL (5000)
 8             (
 9        PARTITION p1 VALUES LESS THAN (5000) ,
10        PARTITION p2 VALUES LESS THAN (10000),
11        PARTITION p3 VALUES LESS THAN (15000),
12        PARTITION p4 VALUES LESS THAN (20000));
Table created.

SQL> SELECT table_name, partition_name, compression
 2  FROM user_tab_partitions
 3  WHERE table_name='TEST';

TABLE_NAME                     PARTITION_NAME                COMPRESS
------------------------------ ----------------------------- -------------
TEST                           P1                            ENABLED
TEST                           P2                            ENABLED
TEST                           P3                            ENABLED
TEST                           P4                            ENABLED

Also see

• Partitioning FAQ, some frequently asked questions about partitioning.