この記事は約16分30秒で読むことができます。

非パーティションからパーティションへの移行 オンライン再定義の手順

参考文献

https://www.oracle.com/technetwork/jp/ondemand/database/db-technique/d-12-disk-1484778-ja.pdf

初期データ投入


PURGE RECYCLEBIN;
DROP TABLE CALENDAR PURGE;
CREATE TABLE CALENDAR AS
WITH sub AS (
SELECT
    to_char(ind,'yyyymmdd') AS yyyymmdd
    ,to_char(ind,'cc') AS century
    ,to_char(ind,'yyyy') AS yyyy
    ,to_char(ind,'mm') AS mm
    ,to_char(ind,'dd') AS dd
    ,to_char(ind,'q') AS quater
    ,to_char(ind,'mon') AS japan_mon
    ,to_char(ind,'rm') AS roman_mon
    ,to_char(ind,'ddd') AS day_per_year
    ,to_char(ind,'ww') AS week_per_year
    ,to_char(ind,'w') AS week_per_month
    ,to_char(ind,'d') AS day_of_week
    ,to_char(ind,'dy') AS jan_day_of_week
    ,to_char(ind,'day') AS jan_day_of_week_rich
FROM
    dual
MODEL
RETURN UPDATED ROWS
DIMENSION BY(sysdate AS ind)
MEASURES(
        0 AS val
        )
RULES(
        val[FOR ind FROM TO_DATE('2019-01-01') TO TO_DATE('2019-01-01') INCREMENT INTERVAL '1' DAY] = 0
        )
)
SELECT * FROM sub;

ALTER TABLE CALENDAR ADD CONSTRAINT CALENDAR_PK PRIMARY KEY(yyyymmdd) USING INDEX GLOBAL;
CREATE UNIQUE INDEX CALENDAR_ind ON CALENDAR (yyyy,mm,dd) GLOBAL;

仮表の作成


DROP MATERIALIZED VIEW TMP_CALENDAR;
DROP TABLE aine.TMP_CALENDAR PURGE;
CREATE TABLE aine.TMP_CALENDAR (
	yyyymmdd               VARCHAR2(8 BYTE)
	, century                VARCHAR2(2 BYTE)
	, yyyy                   VARCHAR2(4 BYTE)
	, mm                     VARCHAR2(2 BYTE)
	, dd                     VARCHAR2(2 BYTE)
	, quater                 VARCHAR2(1 BYTE)
	, japan_mon              VARCHAR2(8 BYTE)
	, roman_mon              VARCHAR2(4 BYTE)
	, day_per_year           VARCHAR2(3 BYTE)
	, week_per_year          VARCHAR2(2 BYTE)
	, week_per_month         VARCHAR2(1 BYTE)
	, day_of_week            VARCHAR2(1 BYTE)
	, jan_day_of_week        VARCHAR2(4 BYTE)
	, jan_day_of_week_rich   VARCHAR2(12 BYTE)
	)
	PARTITION BY RANGE(yyyymmdd)
       (
	   PARTITION part_q1 VALUES LESS THAN ('20190401')
		,PARTITION part_q2 VALUES LESS THAN ('20190701')
		,PARTITION part_q3 VALUES LESS THAN ('20191001')
		,PARTITION part_q4 VALUES LESS THAN (MAXVALUE)
);

オンライン処理を表現


BEGIN
	FOR I IN 1..364
	LOOP
		INSERT INTO CALENDAR
		WITH sub AS(
		SELECT
			to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'yyyymmdd') AS yyyymmdd
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'cc') AS century
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'yyyy') AS yyyy
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'mm') AS mm
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'dd') AS dd
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'q') AS quater
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'mon') AS japan_mon
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'rm') AS roman_mon
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'ddd') AS day_per_year
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'ww') AS week_per_year
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'w') AS week_per_month
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'd') AS day_of_week
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'dy') AS jan_day_of_week
			, to_char(TO_DATE(MAX(yyyymmdd) OVER ()) + I, 'day') AS jan_day_of_week_rich
		FROM
			CALENDAR
		)
		SELECT * FROM sub WHERE ROWNUM = 1
		;
		COMMIT;
	dbms_lock.sleep(1);
  END
LOOP;
END;
/

以降の処理は別セッションで実行。sqldeveloperをもう一個開くなり、teratermでもうひとつセッション複製するなり、コンソールもう一個開くなりする。

表がオンライン再定義の候補であることの確認


EXEC dbms_redefinition.can_redef_table(user,'CALENDAR',dbms_redefinition.cons_use_pk);

ディクショナリ情報の確認


SELECT table_name, partitioning_type, status FROM user_part_tables WHERE table_name LIKE '%CALENDAR%';
SELECT table_owner, table_name, index_name, uniqueness, status, global_stats FROM user_indexes WHERE table_name LIKE '%CALENDAR%';
SELECT OWNER, table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name LIKE '%CALENDAR%';

件数チェック


select count(*) from CALENDAR;
select count(*) from TMP_CALENDAR;

再定義プロセスの開始


EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'CALENDAR', 'TMP_CALENDAR');

仮表にローカル主キー索引を作成


ALTER TABLE TMP_CALENDAR ADD CONSTRAINT TMP_CALENDAR_PK PRIMARY KEY (yyyymmdd) USING INDEX LOCAL;

仮表にローカル索引を作成


CREATE INDEX TMP_CALENDAR_ind ON TMP_CALENDAR (yyyy,mm,dd) LOCAL;

ディクショナリ情報の確認


SELECT table_name, partitioning_type, status FROM user_part_tables WHERE table_name LIKE '%CALENDAR%';
SELECT table_owner, table_name, index_name, uniqueness, status, global_stats FROM user_indexes WHERE table_name LIKE '%CALENDAR%';
SELECT OWNER, table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name LIKE '%CALENDAR%';

DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECTプロシージャによる索引&主キー制約の関連付け


BEGIN
  dbms_redefinition.register_dependent_object(
      USER                         -- uname
    , 'CALENDAR'                      -- orig_table
    , 'TMP_CALENDAR'                  -- int_table
    , dbms_redefinition.cons_index -- dep_type
    , USER                         -- dep_owner
    , 'CALENDAR_PK'                   -- dep_orig_name
    , 'TMP_CALENDAR_PK'               -- dep_int_name
  );
END;
/


BEGIN
  dbms_redefinition.register_dependent_object(
      USER                         -- uname
    , 'CALENDAR'                      -- orig_table
    , 'TMP_CALENDAR'                  -- int_table
    , dbms_redefinition.cons_constraint -- dep_type
    , USER                         -- dep_owner
    , 'CALENDAR_PK'                   -- dep_orig_name
    , 'TMP_CALENDAR_PK'               -- dep_int_name
  );
END;
/


BEGIN
  dbms_redefinition.register_dependent_object(
      USER                         -- uname
    , 'CALENDAR'                      -- orig_table
    , 'TMP_CALENDAR'                  -- int_table
    , dbms_redefinition.cons_index -- dep_type
    , USER                         -- dep_owner
    , 'CALENDAR_IND'                   -- dep_orig_name
    , 'TMP_CALENDAR_IND'               -- dep_int_name
  );
END;
/

再定義プロセスの終了


BEGIN
	dbms_redefinition.finish_redef_table(
		'aine'
		, 'CALENDAR'
		, 'TMP_CALENDAR'
	);
END;
/

ディクショナリ情報の確認


SELECT table_name, partitioning_type, status FROM user_part_tables WHERE table_name LIKE '%CALENDAR%';
SELECT table_owner, table_name, index_name, uniqueness, status, global_stats FROM user_indexes WHERE table_name LIKE '%CALENDAR%';
SELECT OWNER, table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name LIKE '%CALENDAR%';

パーティション単位の検索


SELECT COUNT(*) FROM CALENDAR PARTITION (part_q1);
SELECT COUNT(*) FROM CALENDAR PARTITION (part_q2);
SELECT COUNT(*) FROM CALENDAR PARTITION (part_q3);
SELECT COUNT(*) FROM CALENDAR PARTITION (part_q4);

仮表の削除


DROP TABLE TMP_CALENDAR PURGE;

Leave a Reply

Your email address will not be published. Required fields are marked *