📊 Module 20: Partitioning Strategies

-- Range partition by date
CREATE TABLE sales (
  sale_id NUMBER PRIMARY KEY,
  sale_date DATE,
  amount NUMBER,
  customer_id NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION p_2024 VALUES LESS THAN (TO_DATE(\"\"\"\"'2025-01-01', \"\"\"\"'YYYY-MM-DD')),
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- Range partition by number
CREATE TABLE emp_by_salary (
  emp_id NUMBER,
  emp_name VARCHAR2(50),
  salary NUMBER
)
PARTITION BY RANGE (salary) (
  PARTITION p_low VALUES LESS THAN (50000),
  PARTITION p_mid VALUES LESS THAN (100000),
  PARTITION p_high VALUES LESS THAN (MAXVALUE)
);

-- List partition by region
CREATE TABLE sales_by_region (
  sale_id NUMBER,
  region VARCHAR2(20),
  amount NUMBER
)
PARTITION BY LIST (region) (
  PARTITION p_asia VALUES ('ASIA', 'VIETNAM', 'THAI'),
  PARTITION p_europe VALUES (\"\"\"\"'EUROPE', \"\"\"\"'UK', \"\"\"\"'FRANCE'),
  PARTITION p_americas VALUES (\"\"\"\"'USA', \"\"\"\"'CANADA', \"\"\"\"'MEXICO'),
  PARTITION p_other VALUES (DEFAULT)
);

-- List partition by status
CREATE TABLE orders_by_status (
  order_id NUMBER,
  status VARCHAR2(20),
  amount NUMBER
)
PARTITION BY LIST (status) (
  PARTITION p_active VALUES (\"\"\"\"'PENDING', \"\"\"\"'PROCESSING'),
  PARTITION p_closed VALUES (\"\"\"\"'COMPLETED', \"\"\"\"'DELIVERED'),
  PARTITION p_cancelled VALUES (\"\"\"\"'CANCELLED', \"\"\"\"'REJECTED')
);

-- Hash partition for distribution
CREATE TABLE employees_hash (
  emp_id NUMBER,
  emp_name VARCHAR2(50),
  dept_id NUMBER
)
PARTITION BY HASH (emp_id) (
  PARTITION p_emp_1,
  PARTITION p_emp_2,
  PARTITION p_emp_3,
  PARTITION p_emp_4
);

-- Hash partition shorthand
CREATE TABLE transactions_hash (
  trans_id NUMBER,
  customer_id NUMBER,
  amount NUMBER
)
PARTITION BY HASH (customer_id)
PARTITIONS 8;

-- Range-Hash: Date range with hash subpartitions
CREATE TABLE sales_composite (
  sale_id NUMBER,
  sale_date DATE,
  customer_id NUMBER,
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 4 (
  PARTITION p_2024 VALUES LESS THAN (TO_DATE(\"\"\"\"'2025-01-01', \"\"\"\"'YYYY-MM-DD')),
  PARTITION p_2025 VALUES LESS THAN (TO_DATE(\"\"\"\"'2026-01-01', \"\"\"\"'YYYY-MM-DD')),
  PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

-- Range-List: Date range with list subpartitions
CREATE TABLE orders_by_date_region (
  order_id NUMBER,
  order_date DATE,
  region VARCHAR2(20),
  amount NUMBER
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY LIST (region) (
  PARTITION p_q1_2024 VALUES LESS THAN (TO_DATE(\"\"\"\"'2024-04-01', \"\"\"\"'YYYY-MM-DD'))
    (
      SUBPARTITION p_q1_asia VALUES (\"\"\"\"'ASIA'),
      SUBPARTITION p_q1_other VALUES (DEFAULT)
    )
);

-- Add range partition
ALTER TABLE sales
ADD PARTITION p_2026
  VALUES LESS THAN (TO_DATE('2027-01-01', 'YYYY-MM-DD'));

-- Add list partition
ALTER TABLE sales_by_region
ADD PARTITION p_africa
  VALUES (\"\"\"\"'EGYPT', \"\"\"\"'SOUTH_AFRICA');

-- Drop partition
ALTER TABLE sales
DROP PARTITION p_2020;

-- Truncate partition
ALTER TABLE sales
TRUNCATE PARTITION p_2023;

-- Exchange partition
ALTER TABLE sales
EXCHANGE PARTITION p_2024 WITH TABLE sales_temp;

-- View table partitions
SELECT partition_name, partition_position,
       high_value, num_rows
FROM dba_tab_partitions
WHERE table_name = 'SALES';

-- View subpartitions
SELECT partition_name, subpartition_name, num_rows
FROM dba_tab_subpartitions
WHERE table_name = \"\"\"\"'SALES_COMPOSITE';

-- Gather partition stats
ANALYZE TABLE sales PARTITION (p_2024) COMPUTE STATISTICS;

-- Query specific partition
SELECT * FROM sales
PARTITION (p_2024)
WHERE amount > 10000;