What is Table Partitioning in Oracle Database

What is Table Partitioning?

Partitioning is a way to break down large tables into smaller, more manageable pieces, while still making them look like a single table to applications. Each partition holds a subset of the data based on specific criteria, like ranges of dates, or lists of values.

There are several types of partitioning, but the most commonly used are:

Range Partitioning
List Partitioning
Hash Partitioning
Composite Partitioning (Range-Hash, Range-List)

We'll focus on Range Partitioning, as it's the most common, but the steps for creating and managing other types are similar.

1. Range Partitioning

Range partitioning is when data is divided into partitions based on a range of values in a column. It's ideal for date-based partitioning.

Example: Create a Range Partitioned Table

In this example, let's say we have a table sales that records sales transactions. We partition it by the sale_date so that each quarter's data goes into its own partition.


CREATE TABLE sales (
  sale_id       NUMBER,
  customer_id   NUMBER,
  sale_date     DATE,
  amount        NUMBER
)
PARTITION BY RANGE (sale_date)
(
  PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
  PARTITION sales_q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
  PARTITION sales_q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
  PARTITION sales_q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

Explanation:

The table sales is partitioned based on the sale_date column.
Four partitions are created, one for each quarter in 2023.
Partition sales_q1_2023 holds all rows where sale_date is less than April 1, 2023, and so on.

2. Dropping Partitions

Dropping a partition removes all data stored in that partition. For example, if you want to remove old data for the first quarter of 2023:


ALTER TABLE sales DROP PARTITION sales_q1_2023;

Impact:

All data in the partition sales_q1_2023 will be deleted.
The partition itself will be removed from the table structure.

3. Reclaiming Space After Dropping a Partition

Simply dropping a partition doesn't always release the space in the tablespace. To fully reclaim space, you can either shrink the table or use tablespace coalescing methods.

Option 1: Coalesce the Tablespace

Coalescing attempts to consolidate fragmented free space within a tablespace, making it available for future use.


ALTER TABLESPACE users COALESCE;

Option 2: Shrink the Table

To reclaim unused space from a table, use the SHRINK SPACE command:


ALTER TABLE sales SHRINK SPACE;

This operation will reclaim the unused space from the table and its partitions.

Option 3: Move a Partition

Moving a partition can also help reclaim space by defragmenting the tablespace:


ALTER TABLE sales MOVE PARTITION sales_q2_2023 TABLESPACE users;

This moves the partition to a new location in the tablespace, compacting the data and potentially reclaiming space.

4. Dropping Partitions and Updating Indexes

If your table has indexes, dropping a partition may leave the indexes in an unusable state. To avoid this, use the UPDATE INDEXES option when dropping a partition:


ALTER TABLE sales DROP PARTITION sales_q2_2023 UPDATE INDEXES;

This will keep the indexes updated as you drop the partition.

5. Partitioning with Local Indexes

When creating a partitioned table, you can also create local indexes. Local indexes partition the index data in the same way as the table data, which improves query performance.

Example: Create a Local Index


CREATE INDEX sales_idx ON sales (sale_date) LOCAL;

Explanation:

LOCAL means that the index will be partitioned in the same way as the table, with one index for each table partition.

6. List Partitioning

List partitioning allows you to partition data based on specific values in a column.

Example: Create a List Partitioned Table


CREATE TABLE customers (
  customer_id   NUMBER,
  region        VARCHAR2(10),
  name          VARCHAR2(50)
)
PARTITION BY LIST (region)
(
  PARTITION north_america VALUES ('USA', 'CANADA', 'MEXICO'),
  PARTITION europe VALUES ('UK', 'GERMANY', 'FRANCE'),
  PARTITION asia VALUES ('INDIA', 'CHINA', 'JAPAN')
);

Here, the table is partitioned by the region column, where specific regions go into specific partitions.

7. Checking Existing Partitions

You can query the DBA_TAB_PARTITIONS view to get details about the partitions of a table.


SELECT table_name, partition_name, high_value 
FROM dba_tab_partitions 
WHERE table_name = 'SALES';

This will return the list of partitions and the high value of the partition range.

8. Composite Partitioning (Range-Hash)

Composite partitioning is when a table is partitioned first by one method (e.g., range), and then sub-partitioned by another method (e.g., hash).

Example: Create a Composite Partitioned Table (Range-Hash)


CREATE TABLE sales (
  sale_id       NUMBER,
  customer_id   NUMBER,
  sale_date     DATE,
  amount        NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 4
(
  PARTITION sales_q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
  PARTITION sales_q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
  PARTITION sales_q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
  PARTITION sales_q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

In this case, the data is first partitioned by date, and then each partition is further sub-partitioned by the customer_id using a hash function.

Benefits of Partitioning in Oracle

Improved Query Performance: Partition pruning and parallel processing optimize queries by only accessing relevant partitions.
Efficient Data Management: Easy partition maintenance for tasks like adding, dropping, or archiving partitions without affecting the entire table.
Better Space Management: Partition-level storage settings and efficient space reclamation after dropping or truncating partitions.
Simplified Data Loading and Archiving: Bulk load data into specific partitions and archive old data by dropping partitions.
High Availability and Backup Efficiency: Partition-level backup and restore reduce the time and complexity of recovery operations.
Reduced Index Maintenance: Local and partitioned indexes lower the overhead of maintaining large global indexes.
Enhanced Data Availability: Online partition maintenance allows changes without taking the table offline.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

What is Table Partitioning in Oracle Database