Master Oracle Partition Tables: From Basics to Advanced Partitioning Techniques

1. Oracle Storage Architecture

Oracle stores data logically in tablespaces and physically in data files. A tablespace consists of many logical data blocks (default size 8 KB, range 2‑32 KB). Logical blocks are grouped into extents, and a contiguous set of blocks forms a segment. Each block contains a header, row data, and free space. The block header records the segment type, block address, table and row directories, and transaction slots. Free space in the middle of a block can grow as rows are inserted or updated. When a row is too large for a single block, it is stored as row fragments across multiple blocks.

2. Partitioning Overview

Partitioning splits a table or index into multiple independent pieces that share the same logical definition but can have separate physical attributes such as tablespace, storage parameters, or parallelism. It improves query performance through partition pruning, increases fault tolerance (a failure in one partition does not affect others), and reduces management and backup overhead.

2.1 Advantages and Disadvantages

Improves query performance by pruning partitions.

Increases fault tolerance; a failure in one partition does not affect others.

Reduces management and backup overhead.

Drawbacks: an ordinary (non‑partitioned) table cannot be converted directly; data must be migrated, and constraints and indexes need to be rebuilt. Adding the parallel compress keyword can speed up the migration.

2.2 When to Partition

Partition a table when it exceeds 5 million rows or occupies more than 2 GB. For tables with 1‑5 million rows, partitioning is recommended but not mandatory. A single partition may hold more than 5 million rows, but its size should stay below 2 GB to avoid performance degradation.

3. Partition‑Related Data Dictionary Views

Dictionary view prefixes determine the required privileges: DBA_ – requires DBA role; shows all objects. ALL_ – shows objects accessible to the current user. USER_ – shows objects owned by the current user.

Key views for partitions: *_PART_TABLES – information about partitioned tables. *_TAB_PARTITIONS – partition definitions. *_TAB_SUBPARTITIONS – sub‑partition definitions. *_PART_KEY_COLUMNS – partition key column details.

4. Partition Types and DDL Examples

4.1 Range Partitioning

Rows are assigned to partitions based on a range of values, typically a date or numeric column.

create table WORKER_202301 (
  id VARCHAR2(100) not null,
  name VARCHAR2(200),
  technology VARCHAR2(100),
  save_date DATE
) partition by range (SAVE_DATE) (
  partition WORKER20230129 values less than (TO_DATE('2023-01-30 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace MYSPACE pctfree 10 initrans 1 maxtrans 255 storage (initial 80K minextents 1 maxextents unlimited),
  partition WORKER20230130 values less than (TO_DATE('2023-01-31 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) tablespace MYSPACE pctfree 10 initrans 1 maxtrans 255 storage (initial 80K minextents 1 maxextents unlimited)
);
create index IDX_WORKER_ID202301 on WORKER_202301 (ID) local;
create index IDX_WORKER_ID_NAME202301 on WORKER_202301 (ID, NAME) local;

4.1.1 DML Example

insert into worker_202301 (id, name, technology, save_date) values ('1','哪吒','java',to_date('2023/1/29 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202301 (id, name, technology, save_date) values ('2','云韵','java',to_date('2023/1/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202301 (id, name, technology, save_date) values ('3','美杜莎','Python',to_date('2023/1/30 00:45:19','yyyy-MM-dd hh24:mi:ss'));

4.1.2 Query a Specific Partition

select * from worker_202301 partition (WORKER20230129);

4.2 List Partitioning

Used when a column has a fixed set of values (e.g., technology).

create table WORKER_202302 (
  id VARCHAR2(100) not null,
  name VARCHAR2(200),
  technology VARCHAR2(100),
  save_date DATE
) partition by list (technology) (
  partition technology_java values ('java'),
  partition technology_python values ('python'),
  partition technology_c values ('c')
);

4.3 Hash Partitioning

Distributes rows evenly based on a hash of the partition key, useful when range or list partitions become skewed.

create table WORKER_202304 (
  id VARCHAR2(100) not null,
  name VARCHAR2(200),
  technology VARCHAR2(100),
  save_date DATE
) partition by hash (id) (
  partition worker_id_1,
  partition worker_id_2,
  partition worker_id_3,
  partition worker_id_4
);

When a new partition is added, Oracle recomputes hash values for all rows and moves data accordingly.

4.4 Composite Partitioning (Range‑List, Range‑Hash, List‑Hash)

Combines two methods, e.g., range on date and list on technology.

create table WORKER_202305 (
  id VARCHAR2(100) not null,
  name VARCHAR2(200),
  technology VARCHAR2(100),
  save_date DATE
) partition by range (SAVE_DATE) subpartition by list (technology) (
  partition WORKER20230529 values less than (TO_DATE('2023-05-30 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) (
    subpartition technology_java_29 values ('java'),
    subpartition technology_python_29 values ('python'),
    subpartition technology_c_29 values ('c')
  ),
  partition WORKER20230530 values less than (TO_DATE('2023-05-31 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) (
    subpartition technology_java_30 values ('java'),
    subpartition technology_python_30 values ('python'),
    subpartition technology_c_30 values ('c')
  )
);

4.5 Managing Partitions

Add a partition:

ALTER TABLE worker_202301 ADD PARTITION WORKER20230131 VALUES LESS THAN (TO_DATE('2023-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'));

Drop a partition: ALTER TABLE worker_202301 DROP PARTITION WORKER20230131; Split a partition:

ALTER TABLE WORKER_202308 SPLIT PARTITION WORKER1 AT (180) INTO (PARTITION WORKER2, PARTITION WORKER3);

Merge partitions:

ALTER TABLE WORKER_202309 MERGE PARTITIONS WORKER2,WORKER3 INTO PARTITION WORKER3;

Truncate a partition (delete data only):

ALTER TABLE WORKER_202309 TRUNCATE PARTITION WORKER3;

5. Converting an Existing Table to a Partitioned Table

Create an empty partitioned table with the desired partitioning scheme, then exchange the data without validation. After the exchange, drop the original table and rename the new one.

create table WORKER_202308_tab (
  id number not null,
  name VARCHAR2(200),
  technology VARCHAR2(100),
  save_date DATE
) partition by range (id) (
  partition WORKER1 values less than (201)
);
ALTER TABLE WORKER_202308_tab EXCHANGE PARTITION WORKER1 WITH TABLE WORKER_202308 WITHOUT VALIDATION;

6. Summary

Oracle’s storage hierarchy follows

Oracle → Tablespace → Segment → Extent → Logical Data Block

. Partitioned tables provide a powerful mechanism to manage large datasets, improve query performance through partition pruning, simplify maintenance, and enhance fault tolerance. The article covered storage fundamentals, partition concepts, when to use them, all major partition types, and hands‑on DDL/DML examples for creating, altering, splitting, merging, and truncating partitions.