Partitioning in ORACLE SQL | Horizontal and Vertical Partitioning

Horizontal and Vertical Partitioning in ORACLE SQL

Horizontal partitioning is a database design technique where a table is divided into smaller, more manageable pieces called partitions, based on rows. Each partition holds a subset of the table's data. This can improve query performance and manageability by allowing the database to focus on a smaller set of data rather than scanning the entire table.

In vertical partitioning, the process involves splitting a single table into multiple smaller tables by dividing its columns. Each of these smaller tables will contain a subset of the original table's columns, but they will share the same primary key. This ensures that the data can still be joined together when needed.

Here's a concise summary of the two concepts:

Vertical Partitioning

Definition: Involves dividing a larger table into smaller tables based on columns. Each smaller table retains a subset of the original table's columns while maintaining the same primary key to ensure data integrity.

Purpose:

• Improve performance by reducing the number of columns accessed in queries.
• Enhance data management by isolating less frequently used or large columns (e.g., BLOBs).
• Facilitate normalization to eliminate redundancy and maintain data integrity.

Horizontal Partitioning (Partitioning)

Definition: Involves dividing a table into smaller pieces (partitions) based on rows using criteria such as list, range, or hash. Each partition holds a subset of the rows from the original table.

Purpose:

• Improve query performance by allowing queries to target specific partitions, reducing the amount of data scanned.
• Enhance manageability, allowing for easier data maintenance, archiving, and backup.
• Scale databases horizontally by distributing data across multiple partitions, potentially on different servers.

Key Distinction

• Vertical Partitioning focuses on dividing columns to manage and optimize data access and integrity.
• Horizontal Partitioning focuses on dividing rows to optimize query performance and manageability.

Here are examples illustrating both horizontal partitioning and vertical partitioning in a database context, particularly in Oracle SQL:

Horizontal Partitioning Examples

1. Range Partitioning

Example: A sales table is partitioned by date range, where each partition holds data for a specific year.

CREATE TABLE Sales (
    sale_id NUMBER PRIMARY KEY,
    sale_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

2. List Partitioning

Example: A customer table is partitioned based on customer regions, where each partition holds data for a specific region.

CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    region VARCHAR2(50)
)
PARTITION BY LIST (region) (
    PARTITION pNorth VALUES ('North'),
    PARTITION pSouth VALUES ('South'),
    PARTITION pEast VALUES ('East'),
    PARTITION pWest VALUES ('West')
);

3. Hash Partitioning

Example: A user activity log is partitioned using a hash function on the user ID, distributing the data evenly across several partitions.

CREATE TABLE UserActivity (
    activity_id NUMBER PRIMARY KEY,
    user_id NUMBER,
    activity_timestamp TIMESTAMP
)
PARTITION BY HASH (user_id) PARTITIONS 4;  -- Creates 4 partitions

Vertical Partitioning Examples

1. Normalization Example

Example: A customer table is split into two tables to reduce redundancy and improve data integrity, separating contact information from basic user information.

-- Original denormalized table
CREATE TABLE Customer (
    customer_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    email VARCHAR2(100),
    address VARCHAR2(200),
    phone_number VARCHAR2(15)
);

-- Normalized version

CREATE TABLE CustomerInfo (
    customer_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    email VARCHAR2(100)
);


CREATE TABLE CustomerContact (
    customer_id NUMBER,
    address VARCHAR2(200),
    phone_number VARCHAR2(15),
    FOREIGN KEY (customer_id) REFERENCES CustomerInfo(customer_id)
);

2. BLOB Separation Example

Example: Storing large binary objects (BLOBs), such as images, in a separate table to improve the performance of the main user table.

CREATE TABLE Users (
    user_id NUMBER PRIMARY KEY,
    username VARCHAR2(50),
    password VARCHAR2(50)
);

CREATE TABLE UserImages (
    user_id NUMBER,
    profile_image BLOB,
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

3. Columnar Storage Example

Example: Using Oracle’s In-Memory feature to store specific columns of a large table in a columnar format for analytical processing.

ALTER TABLE Sales INMEMORY;  -- Enables columnar storage for the Sales table

Summary

Horizontal Partitioning involves splitting tables based on rows (e.g., partitioning a sales table by date),
while vertical partitioning involves splitting tables based on columns (e.g., normalizing customer data into separate tables).

These strategies are used to optimize performance, manageability, and data integrity based on the specific needs of your database applications.