[Oracle DB] Thủ tục chuyển bảng non-partition sang bảng partition
## Tạo bảng mới với cấu trúc như bảng cũ và 1 partition ban đầu:
--Thủ tục tạo bảng mới
CREATE TABLE TABLE1_NEW
PARTITION BY RANGE (CREATE_DATE)
(
PARTITION DATA20230101 VALUES LESS THAN (TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
TABLESPACE DATA2024
)
AS
SELECT *
FROM TABLE1
WHERE 1=0;## Trên bản mới, sử dụng thủ tục tạo thêm hàng loạt phân vùng từ phân vùng ban đầu:
--Thủ tục tạo thêm partition
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'PARTITION_02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
--Thủ tục tạo thêm partition
DECLARE
v_nam NUMBER (4) := 2023; -- Năm cần tạo partition
v_tablename VARCHAR2 (50) := 'TABLE1_NEW'; -- Tên bảng
v_date_from DATE := TO_DATE ('2023-01-02', 'yyyy-mm-dd'); -- Ngày bắt đầu
v_date_to DATE := TO_DATE ('2023-12-31', 'yyyy-mm-dd'); -- Ngày kết thúc
v_numday NUMBER (5);
v_tablespace VARCHAR2 (50) := 'DATA2024'; -- Tablespace
BEGIN
v_numday := v_date_to - v_date_from;
FOR i IN 0 .. v_numday
LOOP
EXECUTE IMMEDIATE
'ALTER TABLE ' || v_tablename || ' ADD PARTITION DATA' || TO_CHAR (v_date_from + i, 'YYYYMMDD')
|| ' VALUES LESS THAN (TO_DATE('''
|| TO_CHAR (v_date_from + i + 1, 'YYYY-MM-DD')
|| ' 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || v_tablespace;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/## Sau đó, bắt đầu insert dữ liệu từ bảng cũ sang bảng mới, commit theo từng lô, dừng nghỉ sau mỗi lô:
--insert dữ liệu từ bảng này sang bảng khác theo từng lô (bath)
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSERT_02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
CURSOR cur IS
SELECT * FROM TABLE1 WHERE CREATE_DATE >= TO_DATE('2023-01-01','YYYY-MM-DD');
cnt NUMBER := 0;
BEGIN
FOR rec IN cur LOOP
INSERT /*+ APPEND*/ INTO TABLE1_NEW (PHONE,CLIENT_CODE,ID_,EXPIRED_DATE,CREATE_DATE,PART_ID)
VALUES (rec.PHONE,rec.CLIENT_CODE,rec.ID_,rec.EXPIRED_DATE,rec.CREATE_DATE,rec.PART_ID);
cnt := cnt + 1;
IF cnt MOD 100000 = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(3);
END IF;
END LOOP;
COMMIT; -- To ensure any remaining rows are committed
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/## Ta có thể quan sát quá trình redo log file được ghi và switch liên tục khi thực hiện các câu lệnh DML. Lỗi ở hình dưới là khi tôi chỉ tạo partition đến hết năm 2023 nhưng lại insert cả dữ liệu năm 2024:
## Sau khi hoàn thành insert, đổi tên bảng hiện hành thành OLD, đổi tên bảng NEW thành bảng hiện hành. Thực hiện các câu lệnh trong cùng 1 job để đảm bảo liền mạch, tránh downtime:
--Đổi tên bảng mới thành bảng hiện hành
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'ROTATE_04',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 RENAME TO TABLE1_OLD';
EXECUTE IMMEDIATE 'ALTER TABLE TABLE1_NEW RENAME TO TABLE1';
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/## Để kiểm nghiệm hiệu quả giữa bảng non-partition và bảng partitioned, ta có thể chạy cùng một câu lệnh trên 2 bảng và so sánh cost của 2 lần chạy:
-- Bảng partitioned, COST = 2:
SQL_ID 2250tsxfadnua, child number 0
-------------------------------------
select * from TABLE1 where CREATE_DATE >=
to_date('2023-06-06','yyyy-mm-dd') and CREATE_DATE <
to_date('2023-06-07','yyyy-mm-dd')
Plan hash value: 2582374914
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | PARTITION RANGE SINGLE| | 1 | 158 | 2 (0)| 00:00:01 | 157 | 157 |
| 2 | TABLE ACCESS FULL | OTP_CONSENT_AGREE | 1 | 158 | 2 (0)| 00:00:01 | 157 | 157 |
-------------------------------------------------------------------------------------------------------------- Bảng non-partition, COST = 5310:
SQL_ID f300522mvkzqp, child number 0
-------------------------------------
select * from TABLE1_OLD where CREATE_DATE >=
to_date('2023-06-06','yyyy-mm-dd') and CREATE_DATE <
to_date('2023-06-07','yyyy-mm-dd')
Plan hash value: 4109661646
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5310 (100)| |
|* 1 | TABLE ACCESS FULL| OTP_CONSENT_AGREE_OLD | 2297 | 100K| 5310 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------- Qua đây chúng ta có thể thấy việc partition chính là chìa khóa để vận hành được các bảng có qui mô dữ liệu lên đến mức TB, hàng tỉ bản ghi của các tổ chức lớn như ngân hàng, viễn thông.
## Toàn bộ các thủ tục:
SELECT *--JOB_NAME,SLAVE_OS_PROCESS_ID,ELAPSED_TIME,CPU_USED
FROM USER_SCHEDULER_RUNNING_JOBS;
select * from ALL_SCHEDULER_JOB_RUN_DETAILS
--where OWNER = 'TUANTN'
order by LOG_ID desc;
--SYSDBA
--GRANT EXECUTE ON DBMS_LOCK TO TUANTN;
BEGIN
-- Stop the job first
DBMS_SCHEDULER.STOP_JOB(
job_name => 'TUANTN.PAR12',
force => TRUE -- Use force to immediately stop the job
);
-- Removed the sleep delay
-- Now drop the job
DBMS_SCHEDULER.DROP_JOB(
job_name => 'TUANTN.PAR12'
);
-- Removed COMMIT as it's not required for these operations
END;
--####################################
SELECT PHONE,CLIENT_CODE,ID_,EXPIRED_DATE,CREATE_DATE,PART_ID
FROM OTP_CONSENT_AGREE;
--Thủ tục tạo bảng mới
CREATE TABLE TABLE1_NEW
PARTITION BY RANGE (CREATE_DATE)
(
PARTITION DATA20230101 VALUES LESS THAN (TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
TABLESPACE DATA2024
)
AS
SELECT *
FROM TABLE1
WHERE 1=0;
--Thủ tục tạo thêm partition
DECLARE
v_nam NUMBER (4) := 2023; -- Năm cần tạo partition
v_tablename VARCHAR2 (50) := 'TABLE1_NEW'; -- Tên bảng
v_date_from DATE := TO_DATE ('2023-01-01', 'yyyy-mm-dd'); -- Ngày bắt đầu
v_date_to DATE := TO_DATE ('2023-12-31', 'yyyy-mm-dd'); -- Ngày kết thúc
v_numday NUMBER (5);
v_tablespace VARCHAR2 (50) := 'DATA2024'; -- Tablespace
BEGIN
v_numday := v_date_to - v_date_from;
FOR i IN 0 .. v_numday
LOOP
EXECUTE IMMEDIATE
'ALTER TABLE ' || v_tablename || ' ADD PARTITION DATA' || TO_CHAR (v_date_from + i, 'YYYYMMDD')
|| ' VALUES LESS THAN (TO_DATE('''
|| TO_CHAR (v_date_from + i + 1, 'YYYY-MM-DD')
|| ' 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || v_tablespace;
END LOOP;
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'PARTITION_02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
--Thủ tục tạo thêm partition
DECLARE
v_nam NUMBER (4) := 2024; -- Năm cần tạo partition
v_tablename VARCHAR2 (50) := 'TABLE1_NEW'; -- Tên bảng
v_date_from DATE := TO_DATE ('2024-01-01', 'yyyy-mm-dd'); -- Ngày bắt đầu
v_date_to DATE := TO_DATE ('2024-12-31', 'yyyy-mm-dd'); -- Ngày kết thúc
v_numday NUMBER (5);
v_tablespace VARCHAR2 (50) := 'DATA2024'; -- Tablespace
BEGIN
v_numday := v_date_to - v_date_from;
FOR i IN 0 .. v_numday
LOOP
EXECUTE IMMEDIATE
'ALTER TABLE ' || v_tablename || ' ADD PARTITION DATA' || TO_CHAR (v_date_from + i, 'YYYYMMDD')
|| ' VALUES LESS THAN (TO_DATE('''
|| TO_CHAR (v_date_from + i + 1, 'YYYY-MM-DD')
|| ' 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || v_tablespace;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/
--insert dữ liệu từ bảng này sang bảng khác theo từng lô (bath)
DECLARE
CURSOR cur IS
SELECT * FROM TABLE1 WHERE CREATE_DATE >= TO_DATE('2023-01-01','YYYY-MM-DD');
cnt NUMBER := 0;
BEGIN
FOR rec IN cur LOOP
INSERT /*+ APPEND*/ INTO TABLE1_NEW (PHONE,CLIENT_CODE,ID_,EXPIRED_DATE,CREATE_DATE,PART_ID)
VALUES (rec.PHONE,rec.CLIENT_CODE,rec.ID_,rec.EXPIRED_DATE,rec.CREATE_DATE,rec.PART_ID);
cnt := cnt + 1;
IF cnt MOD 100000 = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(3);
END IF;
END LOOP;
COMMIT; -- To ensure any remaining rows are committed
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSERT_02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
CURSOR cur IS
SELECT * FROM TABLE1 WHERE CREATE_DATE >= TO_DATE('2023-01-01','YYYY-MM-DD') ORDER BY CREATE_DATE ASC;
cnt NUMBER := 0;
BEGIN
FOR rec IN cur LOOP
INSERT /*+ APPEND*/ INTO TABLE1_NEW (PHONE,CLIENT_CODE,ID_,EXPIRED_DATE,CREATE_DATE,PART_ID)
VALUES (rec.PHONE,rec.CLIENT_CODE,rec.ID_,rec.EXPIRED_DATE,rec.CREATE_DATE,rec.PART_ID);
cnt := cnt + 1;
IF cnt MOD 100000 = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(3);
END IF;
END LOOP;
COMMIT; -- To ensure any remaining rows are committed
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/
--Đổi tên bảng mới thành bảng hiện hành
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'ROTATE_04',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 RENAME TO TABLE1_OLD';
EXECUTE IMMEDIATE 'ALTER TABLE TABLE1_NEW RENAME TO TABLE1';
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/## Phiên bản cải tiến, tạo partition trên nhiều năm:
SELECT *--JOB_NAME,SLAVE_OS_PROCESS_ID,ELAPSED_TIME,CPU_USED
FROM USER_SCHEDULER_RUNNING_JOBS;
select * from ALL_SCHEDULER_JOB_RUN_DETAILS
--where OWNER = 'TUANTN'
order by LOG_ID desc;
--SYSDBA
--GRANT EXECUTE ON DBMS_LOCK TO TUANTN;
BEGIN
-- Stop the job first
DBMS_SCHEDULER.STOP_JOB(
job_name => 'TUANTN.PARTITION_03',
force => TRUE -- Use force to immediately stop the job
);
-- Removed the sleep delay
-- Now drop the job
DBMS_SCHEDULER.DROP_JOB(
job_name => 'TUANTN.PARTITION_03'
);
-- Removed COMMIT as it's not required for these operations
END;
--####################################
SELECT PHONE,CLIENT_CODE,ID_,EXPIRED_DATE,CREATE_DATE,PART_ID
FROM TABLE1;
--Thủ tục tạo bảng mới
CREATE TABLE TABLE1_NEW
PARTITION BY RANGE (CREATE_DATE)
(
PARTITION DATA20221231 VALUES LESS THAN (TO_DATE('2022-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
TABLESPACE DATA2024
)
AS
SELECT * FROM TABLE1 WHERE 1=0;
--Thủ tục tạo thêm partition
DECLARE
v_start_year NUMBER(4) := 2023; -- Năm bắt đầu
v_end_year NUMBER(4) := 2024; -- Năm kết thúc
v_tablename VARCHAR2(50) := 'TABLE1_NEW'; -- Tên bảng
v_date_from DATE; -- Ngày bắt đầu
v_date_to DATE; -- Ngày kết thúc
v_numday NUMBER(5);
v_tablespace VARCHAR2(50) := 'DATA2024'; -- Tablespace
BEGIN
-- Lặp qua từng năm từ năm bắt đầu đến năm kết thúc
FOR y IN v_start_year..v_end_year LOOP
v_date_from := TO_DATE(y || '-01-01', 'yyyy-mm-dd'); -- Ngày đầu tiên của năm hiện tại
v_date_to := TO_DATE((y + 1) || '-01-01', 'yyyy-mm-dd') - 1; -- Ngày cuối cùng của năm hiện tại
v_numday := v_date_to - v_date_from;
-- Lặp qua từng ngày trong năm
FOR i IN 0..v_numday LOOP
EXECUTE IMMEDIATE
'ALTER TABLE ' || v_tablename || ' ADD PARTITION DATA' || TO_CHAR(v_date_from + i, 'YYYYMMDD')
|| ' VALUES LESS THAN (TO_DATE('''
|| TO_CHAR(v_date_from + i + 1, 'YYYY-MM-DD')
|| ' 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || v_tablespace;
END LOOP;
END LOOP;
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'PARTITION_03',
job_type => 'PLSQL_BLOCK',
job_action => q'[
--Thủ tục tạo thêm partition
DECLARE
v_start_year NUMBER(4) := 2023; -- Năm bắt đầu
v_end_year NUMBER(4) := 2024; -- Năm kết thúc
v_tablename VARCHAR2(50) := 'TABLE1_NEW'; -- Tên bảng
v_date_from DATE; -- Ngày bắt đầu
v_date_to DATE; -- Ngày kết thúc
v_numday NUMBER(5);
v_tablespace VARCHAR2(50) := 'DATA2024'; -- Tablespace
BEGIN
-- Lặp qua từng năm từ năm bắt đầu đến năm kết thúc
FOR y IN v_start_year..v_end_year LOOP
v_date_from := TO_DATE(y || '-01-01', 'yyyy-mm-dd'); -- Ngày đầu tiên của năm hiện tại
v_date_to := TO_DATE((y + 1) || '-01-01', 'yyyy-mm-dd') - 1; -- Ngày cuối cùng của năm hiện tại
v_numday := v_date_to - v_date_from;
-- Lặp qua từng ngày trong năm
FOR i IN 0..v_numday LOOP
EXECUTE IMMEDIATE
'ALTER TABLE ' || v_tablename || ' ADD PARTITION DATA' || TO_CHAR(v_date_from + i, 'YYYYMMDD')
|| ' VALUES LESS THAN (TO_DATE('''
|| TO_CHAR(v_date_from + i + 1, 'YYYY-MM-DD')
|| ' 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || v_tablespace;
END LOOP;
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/
--insert dữ liệu từ bảng này sang bảng khác theo từng lô (bath)
DECLARE
CURSOR cur IS
SELECT * FROM TABLE1 WHERE CREATE_DATE >= TO_DATE('2023-01-01','YYYY-MM-DD');
cnt NUMBER := 0;
BEGIN
FOR rec IN cur LOOP
INSERT /*+ APPEND*/ INTO TABLE1_NEW (PHONE,CLIENT_CODE,ID_,EXPIRED_DATE,CREATE_DATE,PART_ID)
VALUES (rec.PHONE,rec.CLIENT_CODE,rec.ID_,rec.EXPIRED_DATE,rec.CREATE_DATE,rec.PART_ID);
cnt := cnt + 1;
IF cnt MOD 100000 = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(3);
END IF;
END LOOP;
COMMIT; -- To ensure any remaining rows are committed
END;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSERT_02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
CURSOR cur IS
SELECT * FROM TABLE1 WHERE CREATE_DATE >= TO_DATE('2023-01-01','YYYY-MM-DD');
cnt NUMBER := 0;
BEGIN
FOR rec IN cur LOOP
INSERT /*+ APPEND*/ INTO TABLE1_NEW (PHONE,CLIENT_CODE,ID_,EXPIRED_DATE,CREATE_DATE,PART_ID)
VALUES (rec.PHONE,rec.CLIENT_CODE,rec.ID_,rec.EXPIRED_DATE,rec.CREATE_DATE,rec.PART_ID);
cnt := cnt + 1;
IF cnt MOD 100000 = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(3);
END IF;
END LOOP;
COMMIT; -- To ensure any remaining rows are committed
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => ''
);
END;
/
--Đổi tên bảng mới thành bảng hiện hành
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'ROTATE_04',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE TABLE1 RENAME TO TABLE1_OLD';
EXECUTE IMMEDIATE 'ALTER TABLE TABLE1_NEW RENAME TO TABLE1';
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/## Thủ tục tạo partition theo tháng:
DECLARE
v_start_year NUMBER(4) := 2023; -- Năm bắt đầu
v_end_year NUMBER(4) := 2024; -- Năm kết thúc
v_tablename VARCHAR2(50) := 'TABLE1_NEW'; -- Tên bảng
v_date_from DATE; -- Ngày bắt đầu
v_date_to DATE; -- Ngày kết thúc
v_month_start DATE;
v_month_end DATE;
v_tablespace VARCHAR2(50) := 'DATA2024'; -- Tablespace
BEGIN
-- Lặp qua từng năm từ năm bắt đầu đến năm kết thúc
FOR y IN v_start_year..v_end_year LOOP
-- Lặp qua từng tháng trong năm
FOR m IN 1..12 LOOP
-- Ngày đầu tiên của tháng
v_month_start := TO_DATE(y || '-' || LPAD(m, 2, '0') || '-01', 'YYYY-MM-DD');
-- Ngày cuối cùng của tháng
v_month_end := LAST_DAY(v_month_start);
-- Thêm partition cho tháng hiện tại
EXECUTE IMMEDIATE
'ALTER TABLE ' || v_tablename || ' ADD PARTITION DATA' || TO_CHAR(v_month_start, 'YYYYMM')
|| ' VALUES LESS THAN (TO_DATE('''
|| TO_CHAR(v_month_end + 1, 'YYYY-MM-DD')
|| ' 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || v_tablespace;
END LOOP;
END LOOP;
END;
/-- Trong thủ tục này, vòng lặp ngoài lặp qua từng năm từ v_start_year đến v_end_year, và vòng lặp bên trong lặp qua từng tháng từ tháng 1 đến tháng 12. Mỗi lần lặp, chúng ta tính toán ngày đầu tiên và ngày cuối cùng của tháng bằng cách sử dụng hàm TO_DATE và LAST_DAY. Sau đó, chúng ta sử dụng lệnh EXECUTE IMMEDIATE để thêm một partition cho tháng hiện tại vào bảng TABLE1_NEW, sử dụng tên partition là DATA theo định dạng YYYYMM và chỉ định ngày cuối cùng của tháng là giá trị LESS THAN.
===
Nhận xét
Đăng nhận xét