【PL/SQL】パーティションテーブル運用を自動化する方法｜月次追加・EXCHANGE・DBMS_STATSまで解説

CREATE TABLE sales_log (
  sale_id    NUMBER,
  sale_date  DATE NOT NULL,
  amount     NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p202601 VALUES LESS THAN (DATE '2026-02-01')
);

CREATE TABLE partition_maintenance_log (
  log_id       NUMBER GENERATED BY DEFAULT AS IDENTITY,
  operation    VARCHAR2(50) NOT NULL,
  table_name   VARCHAR2(128) NOT NULL,
  partition_name VARCHAR2(128),
  status       VARCHAR2(30) NOT NULL,
  message      VARCHAR2(4000),
  started_at   TIMESTAMP DEFAULT SYSTIMESTAMP,
  finished_at  TIMESTAMP,
  CONSTRAINT pk_partition_maintenance_log PRIMARY KEY (log_id)
);

CREATE OR REPLACE PROCEDURE add_sales_partition(
  p_month DATE
) IS
  l_month_start DATE := TRUNC(p_month, 'MM');
  l_next_month   DATE := ADD_MONTHS(TRUNC(p_month, 'MM'), 1);
  l_part_name    VARCHAR2(30);
  l_sql          VARCHAR2(4000);
  l_exists       NUMBER;
BEGIN
  l_part_name := 'P' || TO_CHAR(l_month_start, 'YYYYMM');

  SELECT COUNT(*)
  INTO l_exists
  FROM user_tab_partitions
  WHERE table_name = 'SALES_LOG'
    AND partition_name = l_part_name;

  IF l_exists > 0 THEN
    RETURN;
  END IF;

  l_sql :=
    'ALTER TABLE sales_log ADD PARTITION ' || l_part_name ||
    ' VALUES LESS THAN (DATE ''' || TO_CHAR(l_next_month, 'YYYY-MM-DD') || ''')';

  INSERT INTO partition_maintenance_log(operation, table_name, partition_name, status, message)
  VALUES ('ADD_PARTITION', 'SALES_LOG', l_part_name, 'STARTED', l_sql);

  EXECUTE IMMEDIATE l_sql;

  UPDATE partition_maintenance_log
  SET status = 'DONE',
      finished_at = SYSTIMESTAMP
  WHERE operation = 'ADD_PARTITION'
    AND table_name = 'SALES_LOG'
    AND partition_name = l_part_name
    AND status = 'STARTED';
END;
/

SELECT
  table_name,
  partition_name,
  high_value,
  num_rows,
  last_analyzed
FROM user_tab_partitions
WHERE table_name = 'SALES_LOG'
ORDER BY partition_position;

-- パーティションプルーニングしやすい条件
SELECT SUM(amount)
FROM sales_log
WHERE sale_date >= DATE '2026-05-01'
  AND sale_date <  DATE '2026-06-01';

-- 避けたい例: パーティションキーに関数をかける
SELECT SUM(amount)
FROM sales_log
WHERE TO_CHAR(sale_date, 'YYYYMM') = '202605';

INSERT INTO sales_log PARTITION (P202605) (
  sale_id,
  sale_date,
  amount
)
SELECT
  sale_id,
  sale_date,
  amount
FROM sales_stage
WHERE sale_date >= DATE '2026-05-01'
  AND sale_date <  DATE '2026-06-01';

-- ステージング表に対象月のデータをロード済みとする
ALTER TABLE sales_log
EXCHANGE PARTITION P202605
WITH TABLE sales_stage_202605
INCLUDING INDEXES
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;

SELECT
  COUNT(*) AS row_count,
  MIN(sale_date) AS min_sale_date,
  MAX(sale_date) AS max_sale_date
FROM sales_stage_202605;

-- 対象月外のデータが混ざっていないか
SELECT COUNT(*) AS out_of_range_count
FROM sales_stage_202605
WHERE sale_date < DATE '2026-05-01'
   OR sale_date >= DATE '2026-06-01';

-- 業務キー重複の確認例
SELECT sale_id, COUNT(*)
FROM sales_stage_202605
GROUP BY sale_id
HAVING COUNT(*) > 1;

-- 例: 古い月次パーティションを削除する
ALTER TABLE sales_log
DROP PARTITION P202401
UPDATE GLOBAL INDEXES;

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'SALES_LOG',
    partname         => 'P202605',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    granularity      => 'AUTO',
    cascade          => TRUE
  );
END;
/

CREATE OR REPLACE PROCEDURE run_monthly_partition_load(
  p_target_month DATE
) IS
  l_part_name VARCHAR2(30) := 'P' || TO_CHAR(TRUNC(p_target_month, 'MM'), 'YYYYMM');
BEGIN
  -- 1. 次月パーティションの存在確認・必要なら追加
  add_sales_partition(p_target_month);

  -- 2. ステージング表を検証
  validate_sales_stage(p_target_month);

  -- 3. EXCHANGE PARTITION
  exchange_sales_partition(p_target_month);

  -- 4. 対象パーティション統計を収集
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname     => USER,
    tabname     => 'SALES_LOG',
    partname    => l_part_name,
    granularity => 'AUTO',
    cascade     => TRUE
  );

  -- 5. 運用ログ更新
  INSERT INTO partition_maintenance_log(operation, table_name, partition_name, status, message)
  VALUES ('MONTHLY_LOAD', 'SALES_LOG', l_part_name, 'DONE', 'monthly load completed');
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_ADD_NEXT_MONTH_PARTITION',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      BEGIN
        add_sales_partition(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1));
      END;
    ]',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=20;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
    enabled         => TRUE,
    comments        => 'Create next month partition for SALES_LOG'
  );
END;
/

INSERT INTO sales_stage_202605(sale_id, sale_date, amount)
SELECT sale_id, sale_date, amount
FROM sales_source
WHERE sale_date >= DATE '2026-05-01'
  AND sale_date <  DATE '2026-06-01'
LOG ERRORS INTO err$_sales_stage_202605('LOAD_202605')
REJECT LIMIT UNLIMITED;

SELECT
  index_name,
  partitioned,
  status
FROM user_indexes
WHERE table_name = 'SALES_LOG'
ORDER BY index_name;

SELECT
  index_name,
  partition_name,
  status
FROM user_ind_partitions
WHERE index_name IN (
  SELECT index_name
  FROM user_indexes
  WHERE table_name = 'SALES_LOG'
)
ORDER BY index_name, partition_position;