ALTER TABLE trade_prod.bundle_detail analyze PARTITION p20230106
-- 1. Range Partitioning
CREATE TABLE range_partitioned_table (
id INT,
created_at DATE
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021),
PARTITION p2 VALUES LESS THAN (2022),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
-- 2. List Partitioning
CREATE TABLE list_partitioned_table (
id INT,
region VARCHAR(50)
)
PARTITION BY LIST (region) (
PARTITION p_north VALUES IN ('North', 'Northeast'),
PARTITION p_south VALUES IN ('South', 'Southeast'),
PARTITION p_east VALUES IN ('East'),
PARTITION p_west VALUES IN ('West'),
PARTITION p_other VALUES IN (DEFAULT)
);
-- 3. Hash Partitioning
CREATE TABLE hash_partitioned_table (
id INT,
created_at DATETIME
)
PARTITION BY HASH(YEAR(created_at))
PARTITIONS 4;
-- 4. Key Partitioning
CREATE TABLE key_partitioned_table (
id INT PRIMARY KEY,
username VARCHAR(50)
)
PARTITION BY KEY()
PARTITIONS 5;
-- 5. Subpartitioning (Composite Partitioning)
CREATE TABLE composite_partitioned_table (
id INT,
created_at DATE,
region VARCHAR(50)
)
PARTITION BY RANGE(YEAR(created_at))
SUBPARTITION BY LIST(region) (
PARTITION p0 VALUES LESS THAN (2020) (
SUBPARTITION p0_north VALUES IN ('North'),
SUBPARTITION p0_south VALUES IN ('South')
),
PARTITION p1 VALUES LESS THAN (2021) (
SUBPARTITION p1_north VALUES IN ('North'),
SUBPARTITION p1_south VALUES IN ('South')
)
);
-- 6. Reorganize Partitions
ALTER TABLE range_partitioned_table
REORGANIZE PARTITION p2, p3 INTO (
PARTITION p2 VALUES LESS THAN (2023),
PARTITION p3 VALUES LESS THAN (2024),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
-- 7. Add New Partition
ALTER TABLE range_partitioned_table
ADD PARTITION (
PARTITION p5 VALUES LESS THAN (2025)
);
-- 8. Drop Partition
ALTER TABLE range_partitioned_table
DROP PARTITION p5;
-- 9. Truncate Specific Partition
ALTER TABLE range_partitioned_table
TRUNCATE PARTITION p2;
-- 10. Rebuild Partition
ALTER TABLE range_partitioned_table
REBUILD PARTITION p2;
-- 11. Optimize Partition
ALTER TABLE range_partitioned_table
OPTIMIZE PARTITION p2;
-- 12. Analyze Partition
ALTER TABLE range_partitioned_table
ANALYZE PARTITION p2;
-- 13. Repair Partition
ALTER TABLE range_partitioned_table
REPAIR PARTITION p2;
-- 14. Convert to Partitioned Table
CREATE TABLE convert_to_partitioned (
id INT,
created_at DATE
);
ALTER TABLE convert_to_partitioned
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021),
PARTITION p2 VALUES LESS THAN MAXVALUE
);
-- 15. Exchange Partition with Non-Partitioned Table
-- First, create a table with identical structure
CREATE TABLE exchange_table (
id INT,
created_at DATE
) LIKE range_partitioned_table;
-- Then exchange the partition
ALTER TABLE range_partitioned_table
EXCHANGE PARTITION p2 WITH TABLE exchange_table;
-- 16. Reorganize Subpartitions
ALTER TABLE composite_partitioned_table
REORGANIZE PARTITION p0, p1 INTO (
PARTITION p0 VALUES LESS THAN (2021) (
SUBPARTITION p0_east VALUES IN ('East'),
SUBPARTITION p0_west VALUES IN ('West')
),
PARTITION p1 VALUES LESS THAN (2022) (
SUBPARTITION p1_east VALUES IN ('East'),
SUBPARTITION p1_west VALUES IN ('West')
)
);
-- 17. Remove Partitioning
ALTER TABLE range_partitioned_table
REMOVE PARTITIONING;
-- 18. Alter Partition Options
CREATE TABLE partition_with_options (
id INT,
created_at DATE
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p0 VALUES LESS THAN (2020)
TABLESPACE ts1
STORAGE DISK,
PARTITION p1 VALUES LESS THAN (2021)
TABLESPACE ts2
STORAGE MEMORY
);
-- 19. Dynamic Partition Management (MySQL 8.0+)
CREATE TABLE dynamic_partitioned_table (
id INT,
created_at DATE
)
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p0 VALUES LESS THAN (2020),
PARTITION p1 VALUES LESS THAN (2021)
)
PARTITION BY RANGE (YEAR(created_at))
(
PARTITION p2 VALUES LESS THAN (2022),
PARTITION p3 VALUES LESS THAN (2023)
);
-- 20. Interval Partitioning (MySQL 8.0+)
CREATE TABLE interval_partitioned_table (
id INT,
created_at DATE
)
PARTITION BY RANGE (YEAR(created_at))
INTERVAL (1) STORED;
