How to Create a Partitioned Table in Oracle

How to Create a Partitioned Table in Oracle

Partitioning divides a large table into smaller, manageable pieces called partitions, each stored independently. Queries that filter on the partition key access only relevant partitions (partition pruning), dramatically improving performance. Partition management operations (ADD, DROP, TRUNCATE) operate on individual partitions without touching others. This guide covers every major partitioning strategy.

Prerequisites

Partitioning requires the Oracle Partitioning option, which is included in Enterprise Edition. Verify it is enabled:

SELECT value FROM v$option WHERE parameter = 'Partitioning';
-- Must return: TRUE

Step 1: Range Partitioning

Range partitioning maps rows to partitions based on a column value range. The most common use case is partitioning by date.

-- Range partition by year
CREATE TABLE orders_range (
  order_id     NUMBER        NOT NULL,
  customer_id  NUMBER        NOT NULL,
  order_date   DATE          NOT NULL,
  status       VARCHAR2(20),
  total_amount NUMBER(12, 2)
)
PARTITION BY RANGE (order_date) (
  PARTITION p_2022 VALUES LESS THAN (DATE '2023-01-01')
    TABLESPACE app_data,
  PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01')
    TABLESPACE app_data,
  PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01')
    TABLESPACE app_data,
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
    TABLESPACE app_data
);

-- Range partition by quarter
CREATE TABLE sales_quarterly (
  sale_id   NUMBER,
  sale_date DATE,
  amount    NUMBER(12, 2),
  region    VARCHAR2(30)
)
PARTITION BY RANGE (sale_date) (
  PARTITION q1_2024 VALUES LESS THAN (DATE '2024-04-01'),
  PARTITION q2_2024 VALUES LESS THAN (DATE '2024-07-01'),
  PARTITION q3_2024 VALUES LESS THAN (DATE '2024-10-01'),
  PARTITION q4_2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION q_future VALUES LESS THAN (MAXVALUE)
);

Step 2: Interval Partitioning (Automatic Range Partitions)

Interval partitioning automatically creates new partitions when data is inserted beyond the last defined partition. No manual ADD PARTITION required.

-- Interval partitioning by month (auto-creates monthly partitions)
CREATE TABLE orders_interval (
  order_id     NUMBER        NOT NULL,
  customer_id  NUMBER        NOT NULL,
  order_date   DATE          NOT NULL,
  status       VARCHAR2(20),
  total_amount NUMBER(12, 2)
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  -- At least one partition must be defined as the anchor
  PARTITION p_start VALUES LESS THAN (DATE '2024-01-01')
    TABLESPACE app_data
)
ENABLE ROW MOVEMENT;

-- Oracle automatically creates partitions like SYS_P123, SYS_P124...
-- when rows with order_date >= 2024-01-01 are inserted

-- Rename auto-created partitions for clarity
ALTER TABLE orders_interval
  RENAME PARTITION SYS_P123 TO p_2024_01;

Step 3: List Partitioning

List partitioning maps rows to partitions based on discrete column values — ideal for geographic regions, categories, or status codes.

-- List partition by region
CREATE TABLE sales_list (
  sale_id   NUMBER,
  region    VARCHAR2(30) NOT NULL,
  sale_date DATE,
  amount    NUMBER(12, 2),
  rep_id    NUMBER
)
PARTITION BY LIST (region) (
  PARTITION p_north VALUES ('NORTH', 'NORTHEAST', 'NORTHWEST')
    TABLESPACE app_data,
  PARTITION p_south VALUES ('SOUTH', 'SOUTHEAST', 'SOUTHWEST')
    TABLESPACE app_data,
  PARTITION p_east  VALUES ('EAST')
    TABLESPACE app_data,
  PARTITION p_west  VALUES ('WEST')
    TABLESPACE app_data,
  PARTITION p_other VALUES (DEFAULT)   -- Catch-all for unknown values
    TABLESPACE app_data
);

-- List partition by status
CREATE TABLE orders_by_status (
  order_id NUMBER,
  status   VARCHAR2(20),
  amount   NUMBER(12, 2)
)
PARTITION BY LIST (status) (
  PARTITION p_pending   VALUES ('PENDING', 'NEW'),
  PARTITION p_active    VALUES ('PROCESSING', 'SHIPPED'),
  PARTITION p_complete  VALUES ('DELIVERED', 'COMPLETED'),
  PARTITION p_cancelled VALUES ('CANCELLED', 'REJECTED')
);

Step 4: Hash Partitioning

Hash partitioning distributes rows across N partitions using a hash function — provides even data distribution when there is no natural range or list key.

-- Hash partition into 8 partitions (must be a power of 2 for even distribution)
CREATE TABLE customers_hash (
  customer_id   NUMBER        NOT NULL,
  customer_name VARCHAR2(100),
  email         VARCHAR2(200),
  created_date  DATE
)
PARTITION BY HASH (customer_id)
PARTITIONS 8
STORE IN (app_data1, app_data2, app_data3, app_data4,
          app_data5, app_data6, app_data7, app_data8);

Step 5: Composite Partitioning

Composite partitioning combines two strategies — for example, range by date with hash sub-partitions for parallelism.

-- Range-Hash composite (partition by year, subpartition by hash)
CREATE TABLE orders_composite (
  order_id     NUMBER,
  order_date   DATE,
  customer_id  NUMBER,
  region       VARCHAR2(30),
  amount       NUMBER(12, 2)
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 8
(
  PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
  PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01')
);

-- Range-List composite (partition by year, subpartition by region)
CREATE TABLE sales_range_list (
  sale_id   NUMBER,
  sale_date DATE,
  region    VARCHAR2(30),
  amount    NUMBER(12, 2)
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
  SUBPARTITION sp_north VALUES ('NORTH', 'NORTHEAST'),
  SUBPARTITION sp_south VALUES ('SOUTH', 'SOUTHEAST'),
  SUBPARTITION sp_other VALUES (DEFAULT)
)
(
  PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
  PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01')
);

Step 6: Reference Partitioning

Reference partitioning propagates the parent table’s partitioning strategy to child tables — the foreign key relationship defines the partition alignment.

-- Parent table: partitioned by order_date
CREATE TABLE orders_ref_parent (
  order_id    NUMBER PRIMARY KEY,
  order_date  DATE,
  customer_id NUMBER,
  status      VARCHAR2(20)
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_start VALUES LESS THAN (DATE '2024-01-01')
);

-- Child table: inherits parent's partition structure via FK
CREATE TABLE order_items_ref (
  item_id     NUMBER PRIMARY KEY,
  order_id    NUMBER REFERENCES orders_ref_parent(order_id),
  product_id  NUMBER,
  quantity    NUMBER,
  unit_price  NUMBER(12, 2)
)
PARTITION BY REFERENCE (order_id);
-- No partition definition needed — inherited from orders_ref_parent

Partition Management

-- Add a new partition (for range partitioned tables without MAXVALUE)
ALTER TABLE orders_range
  ADD PARTITION p_2025 VALUES LESS THAN (DATE '2026-01-01')
  TABLESPACE app_data;

-- Drop a partition (deletes data in the partition)
ALTER TABLE orders_range DROP PARTITION p_2022;

-- Truncate a partition (faster than DELETE — minimal redo)
ALTER TABLE orders_range TRUNCATE PARTITION p_2022;

-- Move a partition to a different tablespace
ALTER TABLE orders_range MOVE PARTITION p_2023
  TABLESPACE archive_ts;

-- Merge two partitions
ALTER TABLE orders_range
  MERGE PARTITIONS p_2022, p_2023 INTO PARTITION p_2022_2023;

-- Split a partition
ALTER TABLE orders_range
  SPLIT PARTITION p_future AT (DATE '2025-01-01')
  INTO (PARTITION p_2024_extra, PARTITION p_future_new);

Common Mistakes and Pitfalls

Choosing the wrong partition key — A partition key on a column that is never in WHERE clauses provides no partition pruning benefit. The key must appear in the most frequent queries.

Using MAXVALUE with interval partitioning — MAXVALUE is incompatible with interval partitioning. Use interval partitioning instead of maintaining a MAXVALUE catch-all partition.

No local indexes — A global index on a partitioned table is maintained across all partition operations (ADD, DROP, SPLIT). This can cause massive redo and index invalidation. Prefer LOCAL indexes wherever possible.

Skewed hash partitions — Use a power-of-2 for hash partition count (4, 8, 16, 32). Non-power-of-2 counts produce uneven distribution.

Not using ENABLE ROW MOVEMENT — Without this, updates that change the partition key value will fail with ORA-14402. Enable row movement if your application updates the partition key column.

Confusing TRUNCATE PARTITION with DROP PARTITION — TRUNCATE removes rows but keeps the partition structure. DROP removes both the data and the partition definition.

Verification Queries

-- List all partitions for a table
SELECT partition_name, partition_position,
       high_value,
       num_rows, last_analyzed,
       ROUND(blocks * 8192 / 1024 / 1024, 0) AS size_mb
FROM dba_tab_partitions
WHERE table_name = 'ORDERS_RANGE'
  AND table_owner = 'APP_SCHEMA'
ORDER BY partition_position;

-- Confirm partition pruning is working (check for PARTITION RANGE SINGLE)
EXPLAIN PLAN FOR
  SELECT * FROM app_schema.orders_range
  WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Check partition sizes
SELECT partition_name,
       ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_segments
WHERE segment_name = 'ORDERS_RANGE'
  AND owner = 'APP_SCHEMA'
GROUP BY partition_name
ORDER BY partition_name;

-- Check row distribution across partitions
SELECT partition_name, num_rows
FROM dba_tab_partitions
WHERE table_name = 'ORDERS_RANGE'
  AND table_owner = 'APP_SCHEMA'
ORDER BY partition_position;

Related Topics

• How to Create an Index - Local vs global partitioned index strategy
• How to Gather Statistics - Partition-level statistics
• Schema Analysis Scripts - Partition analysis scripts
• Oracle Performance Tuning Cheat Sheet - Partitioning best practices