How Table Partitioning Works Internally in Oracle ?

In Oracle, table partitioning allows a large table to be broken down into smaller, more manageable pieces called partitions, while still being treated as a single logical table. Internally, Oracle divides the table’s data based on partitioning criteria such as ranges, lists, or hash values. The main goal is to improve performance by reducing I/O operations, enhancing manageability, and improving the speed of queries by allowing operations to be performed on specific partitions instead of the entire table.

Here’s an internal view of how table partitioning works in Oracle:

1. Internal Data Organization

When a table is partitioned, each partition is stored in its own physical segment (similar to a table), with its own blocks and extents. Despite this, Oracle manages all partitions as a single logical entity from the perspective of the end user.

Oracle uses the partitioning key to route data to specific partitions. Each time a INSERT, SELECT, UPDATE, or DELETE operation is performed, Oracle checks the partitioning key and determines which partition the operation applies to.

Example:

If a table is partitioned by date range, data inserted with a specific date value will be stored in the corresponding partition.

2. Partition Pruning

Oracle’s query optimizer uses a technique called partition pruning to improve query performance. When a query is executed, Oracle will eliminate partitions that are not relevant to the query based on the partitioning key.

This means that Oracle only accesses the specific partitions that contain the required data, significantly reducing the amount of data to scan and improving query performance.

How partition pruning works:

• For example, if a table is partitioned by date and you run a query like:

  SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-06-01';

Oracle will only scan the partitions containing data from 2023-01-01 to 2023-06-01, skipping all other partitions.

3. Partitioning Methods

Oracle supports several partitioning methods, and each method organizes data differently under the hood. Here’s a summary of the most common ones:

1. Range Partitioning

Partitions are defined based on a range of values in a column (e.g., a date or numeric column). Each partition holds data for a specific range.

• Example:
  A table orders partitioned by range on order_date:

  CREATE TABLE orders (
      order_id NUMBER,
      order_date DATE,
      customer_id NUMBER
  )
  PARTITION BY RANGE (order_date) (
      PARTITION p_2022 VALUES LESS THAN (TO_DATE('01-JAN-2023', 'DD-MON-YYYY')),
      PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY'))
  );

Internally: Rows are stored in separate physical partitions depending on the value of order_date.

2. List Partitioning

Partitions are based on discrete values (lists) of a column, such as regions or categories.

• Example:
  A table employees partitioned by department:

  CREATE TABLE employees (
      emp_id NUMBER,
      emp_name VARCHAR2(100),
      department VARCHAR2(50)
  )
  PARTITION BY LIST (department) (
      PARTITION p_sales VALUES ('Sales'),
      PARTITION p_hr VALUES ('HR'),
      PARTITION p_finance VALUES ('Finance')
  );

Internally: Rows belonging to the ‘Sales’ department are stored in the p_sales partition, and so on.

3. Hash Partitioning

Partitions are created based on a hash value of a column. This ensures that rows are distributed evenly across all partitions.

• Example:
  A table customers partitioned by hashing the customer_id column:

  CREATE TABLE customers (
      customer_id NUMBER,
      customer_name VARCHAR2(100)
  )
  PARTITION BY HASH (customer_id) PARTITIONS 4;

Internally: Oracle hashes the customer_id and routes the data to one of the 4 partitions based on the hash value.

4. Composite Partitioning

Combines two partitioning methods, such as range and hash or range and list. Each partition can have subpartitions.

• Example:
  A table sales partitioned by year and subpartitioned by region:

  CREATE TABLE sales (
      sale_id NUMBER,
      sale_date DATE,
      region VARCHAR2(50)
  )
  PARTITION BY RANGE (sale_date)
  SUBPARTITION BY LIST (region)
  SUBPARTITION TEMPLATE (
      SUBPARTITION p_north VALUES ('North'),
      SUBPARTITION p_south VALUES ('South')
  )
  (PARTITION p_2022 VALUES LESS THAN (TO_DATE('01-JAN-2023', 'DD-MON-YYYY')),
   PARTITION p_2023 VALUES LESS THAN (TO_DATE('01-JAN-2024', 'DD-MON-YYYY')));

Internally: First, the data is partitioned by year (sale_date), then each year partition is further divided into subpartitions based on the region.

4. Querying a Partitioned Table

When querying a partitioned table, Oracle automatically handles partitioning internally. As a user, you don’t need to be concerned about the underlying partitions — Oracle will decide which partitions to access based on the query.

However, you can influence how queries interact with partitions in some cases:

Simple Query Example:

For a partitioned table orders (partitioned by order_date):

SELECT * FROM orders WHERE order_date = '2023-05-01';

• Oracle will use partition pruning to access only the partition that contains data for May 2023, skipping all other partitions.

Direct Access to Partitions:

Oracle allows you to query specific partitions explicitly using the PARTITION keyword.

SELECT * FROM orders PARTITION (p_2023) WHERE customer_id = 101;

• This query only scans the p_2023 partition, which is useful if you know which partition contains the data.

Query Using Indexes on Partitioned Tables:

You can create indexes on partitioned tables to further optimize query performance. Oracle supports local indexes (one per partition) and global indexes (spanning all partitions).

Local Index Example:

CREATE INDEX idx_orders_customer ON orders (customer_id) LOCAL;

• This creates an index for the customer_id column on each partition. When querying a specific partition, only the index for that partition is used, improving performance.

5. Partition Maintenance Operations

Oracle provides several utilities to manage partitions:

Adding a Partition:

You can add new partitions dynamically as your data grows.

ALTER TABLE orders ADD PARTITION p_2024 VALUES LESS THAN (TO_DATE('01-JAN-2025', 'DD-MON-YYYY'));

Dropping a Partition:

You can drop partitions to remove old or obsolete data.

ALTER TABLE orders DROP PARTITION p_2022;

Merging Partitions:

You can merge two partitions into one.

ALTER TABLE orders MERGE PARTITIONS p_2022, p_2023 INTO PARTITION p_old_orders;

6. Benefits of Table Partitioning

• Improved Query Performance: Partition pruning ensures that only the relevant partitions are scanned, reducing query execution time.
• Parallel Query Execution: Oracle can query different partitions in parallel, increasing performance for large queries.
• Manageability: Easier to manage large tables by archiving, purging, or rolling over data in partitions without affecting the entire table.
• Partition Independence: Maintenance operations (e.g., backups, recovery, or index creation) can be performed on individual partitions without locking or affecting the entire table.

Conclusion

Oracle’s table partitioning is a powerful tool to optimize the performance and manageability of large tables. Internally, Oracle routes data into partitions based on the partitioning method, and partition pruning allows Oracle to access only the relevant partitions for queries. Partitioned tables can be queried just like normal tables, but Oracle’s query optimizer ensures that only necessary partitions are scanned. Additionally, partition maintenance operations like adding, dropping, or merging partitions provide flexibility in managing large datasets.