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[edit]
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[edit]
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[edit]
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[edit]
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[edit]
- Partitioning FAQ, some frequently asked questions about partitioning.