Oracle Database that allows you to manage large tables and indexes by dividing them into smaller, more manageable pieces called partitions. Each partition can be stored and managed separately, improving performance, maintenance, and scalability of database operations.

partitioning features:

Partition Pruning: Access only relevant partitions for faster query performance.
Parallel Query Execution: Run queries across partitions simultaneously for better speed.
Partition-Level Operations: Manage partitions independently (e.g., data purging, index rebuilding).
Scalable Data Management: Handle very large tables by breaking them into smaller parts.
Flexible Storage: Store partitions in different tablespaces for optimized use.
Efficient Data Loading: Load data directly into partitions, reducing resource use.
Partition-Wise Joins: Optimize joins by processing only relevant partitions.
Partition Rotation: Easily add/drop partitions to manage data retention.
Backup and Recovery: Partition-level backups for faster, targeted recovery.
Reduced Lock Contention: Operations on one partition don’t affect others.

Types of Partition:

Range Partitioning: Divides data based on a continuous range of values (e.g., dates or numbers). Ideal for time-series data.
List Partitioning: Partitions data based on a predefined list of discrete values (e.g., region or department).
Hash Partitioning: Uses a hash function to evenly distribute data across partitions, useful when data does not fit into a range or list pattern.
Composite Partitioning: Combines two partitioning methods, such as range-hash or range-list, for more complex data organization.
Interval Partitioning: An extension of range partitioning where new partitions are automatically created as new data arrives beyond existing ranges.
Reference Partitioning: Aligns partitions between parent and child tables in a foreign key relationship, maintaining data integrity and simplifying joins.
System Partitioning: Gives full control to users for assigning data to partitions based on application logic, without Oracle automating the distribution.
Virtual Column-Based Partitioning: Partitions data based on a virtual column derived from existing columns, providing flexible partition keys.
Sub-Partitioning: Adds another layer of partitioning within an existing partition (e.g., range partitioned table with hash sub-partitions) for detailed data management.

history of partition:

Oracle 8 (1997): Introduced range partitioning for splitting tables based on ranges of values.
Oracle 8i: Added hash partitioning for even data distribution.
Oracle 9i: Introduced composite partitioning, combining multiple partition types.
Oracle 10g: Added list partitioning for grouping data by specific values.
Oracle 11g: Brought interval partitioning (automatic range creation) and reference partitioning for parent-child table relationships.
Oracle 12c: Enhanced with multi-column and sub-partitioning options.
Oracle 19c & 21c: Focused on automation, with hybrid partitioned tables allowing external storage integration.

Range Partition:

Range partitioning is a type of table partitioning in Oracle where the data is divided into partitions based on a specified range of values from a column. It is one of the most commonly used partitioning strategies, especially for date-based data. Each partition holds rows that fall within a specific range, making it ideal for scenarios. where data is naturally segmented by a range, such as dates, numbers, or other sequential data.

CREATE TABLE time_range_sales ( prod_id NUMBER(6) primary key,cust_id NUMBER,time_id DATE,quantity_sold NUMBER(3),amount_sold NUMBER(10,2))
PARTITION BY RANGE (time_id)
(PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')) tablespace users,
PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) tablespace examples,
PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')) tablespace xyz,
PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE) tablespace abc );

COLUMN high_value FORMAT A20
SELECT table_name,partition_name,high_value, num_rows FROM user_tab_partitions ORDER BY table_name, partition_name;

select * from TIME_RANGE_SALES;

select * from TIME_RANGE_SALES partition (SALES_1998);
select * from TIME_RANGE_SALES partition (SALES_1999);
select * from TIME_RANGE_SALES partition (SALES_2000);
select * from TIME_RANGE_SALES partition (SALES_2001);

CREATE TABLE sales (sale_id NUMBER, sale_date DATE,amount NUMBER)
PARTITION BY RANGE (sale_date) (
PARTITION sales_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
PARTITION sales_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
PARTITION sales_q3 VALUES LESS THAN (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
PARTITION sales_q4 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) );

List:

List partitioning is a type of table partitioning in Oracle where data is divided into partitions based on a specific list of discrete values from a column. Each partition is defined by a set of values, and rows are placed into a partition based on whether the value in the partitioning column matches one of those specified in the list. This method is ideal when the data has a natural grouping that isn’t sequential or continuous, such as categorizing data by region, department, or product type.

CREATE TABLE customer_orders ( order_id NUMBER,region VARCHAR2(50),order_date DATE)
PARTITION BY LIST (region) (
PARTITION east VALUES ('East') TABLESPACE ts_east,
PARTITION west VALUES ('West') TABLESPACE ts_west,
PARTITION north VALUES ('North') TABLESPACE ts_north,
PARTITION south VALUES ('South') TABLESPACE ts_south);

Hash:

Hash partitioning is a type of partitioning method in Oracle where data is distributed across partitions using a hashing algorithm. This type of partitioning is particularly useful for evenly distributing data when there is no natural or obvious range or list to partition by. The goal is to spread data evenly across all partitions to prevent data skew and ensure uniform distribution, which can improve query performance and parallel processing.

CREATE TABLE user_data (user_id NUMBER,user_name VARCHAR2(50),signup_date DATE)
PARTITION BY HASH (user_id)
PARTITIONS 4
STORE IN (ts_hash1, ts_hash2, ts_hash3, ts_hash4);

Composite Partitioning (Range-Hash) with Tablespaces:

Composite partitioning is an advanced partitioning strategy in Oracle that involves using two levels of partitioning on a single table. It combines two different partitioning methods to create a multi-tiered data organization scheme, offering more control and flexibility in managing data. This approach is useful when the data structure requires more granular division for efficient query performance and better management.

CREATE TABLE transactions (transaction_id NUMBER,transaction_date DATE,amount NUMBER)
PARTITION BY RANGE (transaction_date)
SUBPARTITION BY HASH (transaction_id)
SUBPARTITIONS 4 STORE IN (ts_sub1, ts_sub2, ts_sub3, ts_sub4) (
PARTITION trans_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')) TABLESPACE ts_q1,
PARTITION trans_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')) TABLESPACE ts_q2);

Composite Partitioning (Range-List) with Tablespaces:

CREATE TABLE sales_data ( sale_id NUMBER, sale_date DATE, region VARCHAR2(50), amount NUMBER)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY LIST (region) (
PARTITION sales_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')) TABLESPACE ts_q1 (
SUBPARTITION east_region VALUES ('East') TABLESPACE ts_east,
SUBPARTITION west_region VALUES ('West') TABLESPACE ts_west,
SUBPARTITION north_region VALUES ('North') TABLESPACE ts_north,
SUBPARTITION south_region VALUES ('South') TABLESPACE ts_south),
PARTITION sales_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')) TABLESPACE ts_q2 (
SUBPARTITION east_region VALUES ('East') TABLESPACE ts_east,
SUBPARTITION west_region VALUES ('West') TABLESPACE ts_west,
SUBPARTITION north_region VALUES ('North') TABLESPACE ts_north,
SUBPARTITION south_region VALUES ('South') TABLESPACE ts_south));

Interval Partitioning with Tablespaces:

Interval partitioning is an extension of range partitioning in Oracle that automates the creation of partitions based on specified intervals. This method simplifies the management of partitioned tables by automatically creating new partitions when data exceeds the boundary of the existing partitions. It is particularly useful for time-based data, such as logs or transaction records, where new data frequently falls outside current partition ranges.

Restrictions in Interval partitioning :

Interval partitioning is restricted to a single partition key that must be a numerical or date range.
At least one partition must be defined when the table is created.
Interval partitioning is not supported for index-organized tables.
You cannot create a domain index on an interval partitioned table.
Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can’t be used at the subpartition level.
A MAXVALUE partition cannot be defined for an interval partitioned table.
NULL values are not allowed in the partition column.

CREATE TABLE monthly_sales (sale_id NUMBER,sale_date DATE,amount NUMBER)
PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
PARTITION sales_start VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
TABLESPACE ts_start);

Reference Partitioning with Tablespaces:

Reference partitioning is for a child table is inherited from the parent table through
a primary key – foreign key relationship. The partitioning keys are not stored in actual columns in the child table.
The child table is partitioned using the same partitioning key as the parent table without having to
duplicate the key columns.Partition maintenance operations performed on the parent table are reflected
on the child table, but no partition maintenance operations are allowed on the child table.

CREATE TABLE orders (order_id NUMBER PRIMARY KEY,order_date DATE,customer_id NUMBER)
PARTITION BY RANGE (order_date) (PARTITION q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')) TABLESPACE ts_q1,
PARTITION q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')) TABLESPACE ts_q2 );

CREATE TABLE order_items (item_id NUMBER,order_id NUMBER REFERENCES orders(order_id),product_id NUMBER,quantity NUMBER)
PARTITION BY REFERENCE (order_items.order_id) STORE IN (ts_items1, ts_items2);
(OR)
CREATE TABLE order_items (item_id NUMBER,order_id NUMBER REFERENCES orders(order_id),product_id NUMBER,quantity NUMBER)
PARTITION BY REFERENCE (order_items.order_id);

SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions ORDER BY table_name, partition_name;

System Partitioning with Tablespaces:

Unlike other partitioning methods (such as range, list, or hash), which determine the appropriate partition based on the value of the partitioning key, system partitioning gives you full control over the distribution of rows.

CREATE TABLE logs (log_id NUMBER,log_date DATE,message VARCHAR2(255))
PARTITION BY SYSTEM (
PARTITION p1 TABLESPACE ts1,
PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts3);

INSERT INTO logs PARTITION (p1) VALUES (1, TO_DATE('2024-03-01', 'YYYY-MM-DD'), 'System startup log');
INSERT INTO logs PARTITION (p2) VALUES (2, TO_DATE('2024-03-02', 'YYYY-MM-DD'), 'User login event');

Virtual column-based partition:

Virtual column-based partitioning is a type of partitioning in Oracle where partitions are created based on the value of a virtual column. A virtual column is a derived column that doesn't store data but computes its value based on an expression using other columns. This allows you to create partitions based on dynamically generated values without needing to store those values explicitly in the table.

CREATE TABLE employee_data (emp_id NUMBER,hire_date DATE,department VARCHAR2(50),year_hired AS (EXTRACT(YEAR FROM hire_date)))
PARTITION BY RANGE (year_hired) (
PARTITION hire_2022 VALUES LESS THAN (2023) TABLESPACE ts_2022,
PARTITION hire_2023 VALUES LESS THAN (2024) TABLESPACE ts_2023,
PARTITION hire_2024 VALUES LESS THAN (2025) TABLESPACE ts_2024);

sub-partition :

A subpartition is a method in Oracle that allows you to further divide the data within each partition of a table. This means that after partitioning the data into main partitions, each main partition can be split into subpartitions, allowing for more granular data organization. This technique is known as composite partitioning.

CREATE TABLE sales_report (report_id NUMBER,report_date DATE,region VARCHAR2(50))
PARTITION BY RANGE (report_date) SUBPARTITION BY LIST (region) (
PARTITION report_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')) TABLESPACE ts_q1 (
SUBPARTITION east_region VALUES ('East') TABLESPACE ts_east,
SUBPARTITION west_region VALUES ('West') TABLESPACE ts_west),
PARTITION report_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')) TABLESPACE ts_q2 (
SUBPARTITION north_region VALUES ('North') TABLESPACE ts_north,
SUBPARTITION south_region VALUES ('South') TABLESPACE ts_south));

Altering and Managing Partition Tables:

Add partition for Range:

alter table sales add partition p6 values less than (1996);

Split the Range partition:

alter table sales split partition p5 into (Partition p6 values less than (1996), Partition p7 values less then (MAXVALUE));

Add partition for list:

alter table customers add partition central_India values ('BHOPAL','NAGPUR');

List partition modified the Value:

ALTER TABLE customers MODIFY PARTITION south_india ADD VALUES ('KOCHI', 'MANGALORE');

List partition Drop the values:

ALTER TABLE customers MODIFY PARTITION south_india DROP VALUES ('KOCHI','MANGALORE');

Add Partition for Hash:

alter table products add partition;

coalesce the hash partition:

alter table products coalesce partition;

Drop the partition from Range, List or Composite Partition table:

alter table sales drop partition p5;

If you have index on dropped table, Then you have to rebuild the index:

alter index sales_ind rebuild;

To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then drop the partition table:

delete from sales where year=1994;
alter table sales drop partition p4;

Exchange Partition:

Exchange Partitioning in Oracle is a feature that allows you to swap data between a partition of a partitioned table and an external table or another partitioned table. This operation is highly efficient because it avoids the need for data movement or DML operations like inserts or deletes. Instead, Oracle swaps the partition metadata, which is a much faster operation.

Exchange partitioning is useful for managing large volumes of data, archiving, and performing efficient batch loads or data purges. It is often used when you want to quickly move data in or out of a partition without the overhead of traditional insert or delete operations.

-- Source table

CREATE TABLE source_table ( record_id NUMBER, record_date DATE, data_value VARCHAR2(50))
PARTITION BY RANGE (record_date) (
PARTITION p_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
PARTITION p_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')));

-- Target table

CREATE TABLE target_table (record_id NUMBER,record_date DATE,data_value VARCHAR2(50))
PARTITION BY RANGE (record_date) (
PARTITION p_q1 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
PARTITION p_q2 VALUES LESS THAN (TO_DATE('2024-07-01', 'YYYY-MM-DD')));

ALTER TABLE source_table EXCHANGE PARTITION p_q1 WITH TABLE target_table;

merge the partition:

merge the contents of two partitions into one partition and the two original partitions are dropped.
You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.
You can only merged two adjacent partitions, you cannot merge non adjacent partitions.

alter table sales merge partition p2 and p3 into partition p23;

TRUNCATE PARTITON:

Truncating a partition will delete all rows from the partition.

alter table sales truncate partition p5;

Index Partition:

index partitioning is a feature that allows you to partition indexes in the same way that tables are partitioned. By partitioning indexes, you can improve the performance of queries and DML operations on partitioned tables, particularly when dealing with large datasets. When an index is partitioned, each partition corresponds to a partition of the underlying table, enabling efficient data retrieval and reducing maintenance overhead for large tables.

Types of Index Partitioning:

Local Index Partitioning:

A local index is partitioned in the same way as the underlying table. Each partition of the index corresponds to a partition of the table. This provides the advantage of partition pruning, where Oracle will only use the relevant partition of the index based on the query filter.

CREATE INDEX sales_idx ON sales (sale_date) LOCAL PARTITION BY RANGE (sale_date)
(PARTITION p_2024 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION p_2025 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')));

The sales table is partitioned by sale_date, and the sales_idx index is partitioned locally in the same way.
Each partition of the sales_idx index corresponds to the partitions p_2024 and p_2025 of the sales table.

Global Index Partitioning:

A global index is not partitioned like the underlying table. Instead, it spans the entire table and is partitioned independently. The index partitions are not tied directly to the table's partitions. However, global indexes can still be useful in scenarios where partitioning on the table doesn't match the query pattern.

CREATE INDEX customer_idx ON sales (customer_id) GLOBAL PARTITION BY RANGE (customer_id)
(PARTITION p_a_to_m VALUES LESS THAN (100000),
PARTITION p_n_to_z VALUES LESS THAN (200000));

The sales table is partitioned by sale_date, but the customer_idx global index is partitioned by customer_id into two partitions (p_a_to_m and p_n_to_z).
The global index will span the entire table, and partition pruning is applied when queries filter by customer_id.

Check Partition Indexes:

select INDEX_NAME, PARTITION_NAME from user_ind_partitions;

Partitioning Existing Non-Partition Table(12cr2):

Partitioning an existing non-partitioned table in Oracle involves converting the table into a partitioned structure without disrupting the data or application functionality. This process can be done in several ways,

Online Partition Existing Table (12cR2 onwards)
Export & Import Partition Table
Split and Exchange Partition
using DBMS_REDEFINITION for minimal downtime.

Online Partition Existing Table:

ALTER TABLE tab1 MODIFY
PARTITION BY RANGE (transaction_date) (
PARTITION TRX_2018 VALUES LESS THAN (TIMESTAMP' 2018-12-31 23:59:59.000000000+00:00') tablespace TBS_01,
PARTITION TRX_2019 VALUES LESS THAN (TIMESTAMP' 2019-12-31 23:59:59.000000000+00:00') tablespace TBS_01,
PARTITION TRX_2020 VALUES LESS THAN (TIMESTAMP' 2020-12-31 23:59:59.000000000+00:00') tablespace TBS_01,
PARTITION TRX_2021 VALUES LESS THAN (TIMESTAMP' 2021-12-31 23:59:59.000000000+00:00') tablespace TBS_01
) ONLINE;

SELECT table_name, partition_name, num_rows FROM dba_tab_partitions WHERE table_name='TAB1' ORDER BY 1,2;

The index on transaction_date column will be automatically converted to locally partitioned index

SELECT index_name, table_name, partitioned, status FROM dba_indexes where table_name='TAB1' ORDER BY 1;

SELECT index_name, partition_name, status, NUM_ROWS FROM dba_ind_partitions WHERE idex_name='&index_name' ORDER BY 1,2;

Export & Import Partition Table:

exp file=sales_new.exp tables=sales_new

rename sales_new to sales_new_bkp;

create the New Partition Table and Import Data Into New Partition Table:

imp file=sales_new.exp tables=sales_new ignore=y

Drop the SALES_NEW_BKP table:

DROP TABLE SALES_NEW_BKP;

Split & Exchange Partition:

CREATE TABLE my_table ( id NUMBER, description VARCHAR2(50) );
INSERT INTO my_table (id, description) VALUES (1, 'One');
INSERT INTO my_table (id, description) VALUES (2, 'Two');
INSERT INTO my_table (id, description) VALUES (3, 'Three');
INSERT INTO my_table (id, description) VALUES (4, 'Four');
COMMIT;

Now create another single full partition table with only one partition to contain whole table

CREATE TABLE my_table_2 (id NUMBER,description VARCHAR2(50))
PARTITION BY RANGE (id) (PARTITION my_table_part VALUES LESS THAN (MAXVALUE));

Switch original table segment with partition table segment

ALTER TABLE my_table_2 EXCHANGE PARTITION my_table_part WITH TABLE my_table WITHOUT VALIDATION;

Drop original table and rename partition table:

DROP TABLE my_table;
RENAME my_table_2 TO my_table;

we can split the new partition table into Multiple partitions:

ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3) INTO (PARTITION my_table_part_1, PARTITION my_table_part_2);

Check partition details via below query

SELECT table_name,partition_name, high_value,num_rows FROM dba_tab_partitions ORDER BY table_name, partition_name;

using DBMS_REDEFINITION for minimal downtime.(check Defragmentation concept)

Oracle Partitions