파티션: 범위·목록·해시 분할, 파티션 프루닝, 파티션 단위 관리

-- Oracle · Tibero
CREATE TABLE orders (
  id INT
, branch VARCHAR2(10)
, amt INT
, ord_date DATE
)
PARTITION BY RANGE (ord_date) (
  PARTITION p2025_01 VALUES LESS THAN (DATE '2025-02-01')
, PARTITION p2025_02 VALUES LESS THAN (DATE '2025-03-01')
, PARTITION p2025_03 VALUES LESS THAN (DATE '2025-04-01')
);

-- Oracle 11g 부터: 새 달이 들어오면 파티션이 자동 생성됨
CREATE TABLE orders (
  id INT
, ord_date DATE
)
PARTITION BY RANGE (ord_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
  PARTITION p_first VALUES LESS THAN (DATE '2025-02-01')
);

-- Oracle · Tibero
PARTITION BY LIST (branch) (
  PARTITION p_seoul VALUES ('서울')
, PARTITION p_etc VALUES (DEFAULT)
)

PARTITION BY HASH (id) PARTITIONS 4

-- Oracle · Tibero
ALTER TABLE orders DROP PARTITION p2025_01 UPDATE GLOBAL INDEXES;
ALTER TABLE orders TRUNCATE PARTITION p2025_02 UPDATE GLOBAL INDEXES;
ALTER TABLE orders EXCHANGE PARTITION p2025_03 WITH TABLE orders_stage;

-- MySQL
CREATE TABLE orders (
  id INT NOT NULL
, branch VARCHAR(10)
, amt INT
, ord_date DATE NOT NULL
, PRIMARY KEY (id, ord_date)
)
PARTITION BY RANGE COLUMNS (ord_date) (
  PARTITION p2025_01 VALUES LESS THAN ('2025-02-01')
, PARTITION p2025_02 VALUES LESS THAN ('2025-03-01')
, PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

-- MySQL
EXPLAIN SELECT COUNT(*) FROM orders WHERE ord_date >= '2025-03-01' AND ord_date < '2025-04-01';
ALTER TABLE orders DROP PARTITION p2025_01;
ALTER TABLE orders TRUNCATE PARTITION p2025_02;

-- MSSQL
CREATE PARTITION FUNCTION pf_month (DATE)
AS RANGE RIGHT FOR VALUES ('2025-02-01', '2025-03-01', '2025-04-01');

CREATE PARTITION SCHEME ps_month
AS PARTITION pf_month ALL TO ([PRIMARY]);

CREATE TABLE orders (
  id INT NOT NULL
, amt INT
, ord_date DATE NOT NULL
) ON ps_month (ord_date);

-- MSSQL
ALTER TABLE orders SWITCH PARTITION 1 TO orders_archive;
TRUNCATE TABLE orders WITH (PARTITIONS (2));

-- Oracle · Tibero
SELECT COUNT(*)
  FROM orders
 WHERE ord_date >= DATE '2025-03-01'
   AND ord_date < DATE '2025-04-01';

| Id | Operation                | Name   | Pstart | Pstop |
------------------------------------------------------------
|  0 | SELECT STATEMENT         |        |        |       |
|  1 |  SORT AGGREGATE          |        |        |       |
|  2 |   PARTITION RANGE SINGLE |        |      3 |     3 |
|* 3 |    TABLE ACCESS FULL     | ORDERS |      3 |     3 |