[Oracle DB] Thủ tục tự động tạo partition, index local, index global, di chuyển index sang tablespace khác
- Tạo bảng partition theo ngày, partition từ 01/01/2024 - 31/12/2024
- Thêm dữ liệu 1 triệu row hoặc 10 triệu row (link https://www.tranvanbinh.vn/2022/03/sinh-du-lieu-ngau-nhien-e-thuc-hanh.html)
- Gather statistic để có thông tin row, size, block của bảng, partition
- Tạo index local và index global trên 1 cột, phân biệt 2 loại index này? (dùng TOAD)
- Truncate 1 partition và xem index global có bị unusable không? Cách khắc phục như thế nào để không bị unusable?
- Tạo index 2 cột dạng local.
====
-- 1.Tạo bảng với các trường như sau:
CREATE TABLE oaz21.table1
(
id VARCHAR2 (15) NOT NULL,
start_datetime DATE NOT NULL,
col3 VARCHAR2 (20),
col4 NUMBER (10, 2)
)
TABLESPACE DATA2024
PARTITION BY RANGE (start_datetime)
(
PARTITION
DATA20240101
VALUES LESS THAN
(TO_DATE ('2024-01-02 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
TABLESPACE DATA2024
)
;-- Câu lệnh drop bảng nếu không vừa ý:
DROP TABLE OAZ21.TABLE1 CASCADE CONSTRAINTS PURGE;
--Xem các Partition đã được tạo, trong lúc này thì bảng mới chỉ có 1 partition khởi tạo ban đầu:
SELECT partition_name, high_value, partition_position FROM user_tab_partitions WHERE table_name = 'TABLE1';
--2. Thủ tục tạo thêm partition hàng loạt:
--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'; -- 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;
/
--3. Hoặc đưa vào khối job để khi chạy lệnh không phụ thuộc vào session của công cụ bạn đang dùng (SQL*, Toad, SQL Developer):
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'CREATE_PARTITIONS_JOB01',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_nam NUMBER (4) := 2024; -- Năm 2024
v_tablename VARCHAR2 (50) := 'table1'; -- Tên bảng
v_date_from DATE := TO_DATE ('02/01/2024', 'dd/mm/yyyy'); -- Ngày bắt đầu
v_date_to DATE := TO_DATE ('31/12/2024', 'dd/mm/yyyy'); -- 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 => 'Job to create daily partitions for table1 for the year 2024'
);
END;
/--4. Thủ tục sinh 5 triệu bản ghi và chèn vào bảng:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INSERT_JOB05',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_start_date TIMESTAMP := TO_TIMESTAMP('2024-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS');
v_end_date TIMESTAMP := TO_TIMESTAMP('2024-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
v_interval INTERVAL DAY TO SECOND;
BEGIN
-- Tính khoảng cách thời gian giữa mỗi bản ghi
v_interval := (v_end_date - v_start_date) / 5000000;
FOR i IN 1..5000000 LOOP
INSERT /*+ APPEND*/ INTO OAZ21.TABLE1 (ID, START_DATETIME, COL3, COL4)
VALUES (
LPAD(i, 15, '0'), -- Điền ID với các số 0 phía trước để đảm bảo độ dài 15 ký tự
(v_start_date + (i - 1) * v_interval), -- Tăng thời gian bắt đầu lên
'Value ' || i, -- Giả sử giá trị cho COL3
i -- Giả sử giá trị cho COL4
);
IF MOD(i, 500000) = 0 THEN
COMMIT;
DBMS_LOCK.SLEEP(3); -- Dừng 3s sau mỗi lần commit
END IF;
END LOOP;
COMMIT; -- Đảm bảo rằng tất cả các thay đổi được lưu lại
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Job to create daily partitions for table1 for the year 2024'
);
END;
/
-- Gọi chạy thủ tục trên:
BEGIN Generate_Records; END; /
--5. Hoặc tạo job tương tự như phía trên:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'CREATE_PARTITIONS_JOB03',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_start_date TIMESTAMP := TO_TIMESTAMP('2024-01-01 00:00:01', 'YYYY-MM-DD HH24:MI:SS');
v_end_date TIMESTAMP := TO_TIMESTAMP('2024-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
v_interval INTERVAL DAY TO SECOND;
BEGIN
-- Tính khoảng cách thời gian giữa mỗi bản ghi
v_interval := (v_end_date - v_start_date) / 5000000;
FOR i IN 1..5000000 LOOP
INSERT /*+ APPEND*/ INTO OAZ21.TABLE1 (ID, START_DATETIME, COL3, COL4)
VALUES (
LPAD(i, 15, '0'), -- Điền ID với các số 0 phía trước để đảm bảo độ dài 15 ký tự
(v_start_date + (i - 1) * v_interval), -- Tăng thời gian bắt đầu lên
'Value ' || i, -- Giả sử giá trị cho COL3
i -- Giả sử giá trị cho COL4
);
END LOOP;
COMMIT; -- Đảm bảo rằng tất cả các thay đổi được lưu lại
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Job to create daily partitions for table1 for the year 2024'
);
END;
/-- 6. Sau đó có thể select thử trong bảng này, xem plan để đánh giá hiệu quả của việc partition:
--7. Chạy job gather để lấy thông tin thống kê của bảng vừa tạo:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'GATHER_JOB01',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'OAZ21', -- Tên ch? s? h?u c?a b?ng
tabname => 'TABLE1', -- Tên b?ng
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- Ph?n tram u?c lu?ng, AUTO_SAMPLE_SIZE cho phép Oracle t? d?nh lu?ng
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- Tùy ch?n phuong pháp cho t?t c? các c?t
cascade => TRUE, -- Thu th?p th?ng kê cho các indexes liên quan
degree => DBMS_STATS.DEFAULT_DEGREE -- Ð? d?ng th?i
);
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'No comment'
);
END;
/
--8. Tạo 2 index local và global cho bảng này:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INDEX_01',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
EXECUTE IMMEDIATE '
create index oaz21.table1_I1 on oaz21.table1(id) local parallel 8 nologging online tablespace INDX2024
';
EXECUTE IMMEDIATE '
create index oaz21.table1_I2 on oaz21.table1(start_datetime) parallel 8 nologging online tablespace INDX2024
';
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/
## Phân biệt Global Indexes và Local Indexes:
### Local Indexes
- **Phân vùng**: Tông tại trên bảng đã được partition, Mỗi phân vùng của một local index chỉ liên kết với một phân vùng duy nhất của bảng được phân vùng, mỗi phân vùng bảng sẽ có một phần của index riêng biệt.
- **Quản lý**: Việc thêm hoặc xóa phân vùng trong bảng sẽ tự động thêm hoặc xóa các phân vùng tương ứng trong local index. Điều này giúp quản lý index trở nên đơn giản hơn khi làm việc với dữ liệu được phân vùng.
### Global Indexes
- **Phân vùng**: Global index có thể lập chỉ mục dữ liệu trên tất cả các phân vùng của bảng.
- **Quản lý**: Việc thêm hoặc xóa phân vùng trong bảng có thể ảnh hưởng đến cấu trúc của global index có thể dẫn đến unusable index, phải xử lí thủ công (re-build index).
- **Hiệu suất**: Phù hợp với các truy vấn cần truy cập dữ liệu trên toàn bảng (nhiều partition).
### Sự lựa chọn giữa Local và Global Indexes
- **Local indexes** thích hợp cho các ứng dụng mà dữ liệu được truy cập và quản lý chủ yếu theo phân vùng. Chúng đơn giản hóa quản lý và có thể cung cấp hiệu suất tốt khi làm việc với dữ liệu phân vùng.
- **Global indexes** có thể hữu ích khi cần một cái nhìn tổng quan về toàn bộ dữ liệu hoặc khi thực hiện các truy vấn không thể được tối ưu hóa cho một phân vùng cụ thể.
## Truncate 1 partition và xem index global có bị unusable không? Cách khắc phục như thế nào để không bị unusable?
-- Câu lệnh kiểm tra các Indexes:
--Kiểm tra các index global và local select INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS,TABLESPACE_NAME,PCT_FREE,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,STATUS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED from user_indexes --where table_name = 'TABLE1' ; select INDEX_NAME,COMPOSITE,PARTITION_NAME,PARTITION_POSITION,STATUS,TABLESPACE_NAME,PCT_FREE,DISTINCT_KEYS,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED from user_ind_partitions --where index_name='TABLE1_I1' ; --Kiểm tra các partition SELECT * FROM user_tab_partitions --WHERE table_name = 'TABLE1' ;
-- Thử truncate một phân vùng sau đó kiểm tra lại index thì trạng thái của Global index chuyển sang "unusable":
alter table oaz21.table1 truncate partition data20240101;
-- Các câu lệnh kiểm tra khác, khi này nếu select theo partition "data20240101" sẽ ra kết quả rỗng:
select rowid, a.* from oaz21.table1 a; select rowid, a.* from oaz21.table1 partition(data20240101) a;
-- Để xử lí Global index bị unusable ta sẽ tiến hành re-build index. Câu lệnh này rất mất thời gian nên tôi sẽ đưa vào khối job để không phụ thuộc vào sesion:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INDEX_REBUILD_01',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
EXECUTE IMMEDIATE '
ALTER INDEX oaz21.TABLE1_I2 REBUILD
';
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/-- Kiểm tra kết quả sau khi rebuild index, trạng thái của global index đã chuyển sang Valid:
-- Để truncate một phân vùng mà không ảnh hưởng đến Global index, sử dụng option như sau:
ALTER TABLE oaz21.table1 TRUNCATE PARTITION data20240101 UPDATE INDEXES;
-- Trong trường hợp Index bị đặt sai tablespace, cần di chuyển index tới một tablespace khác để phù hợp với qui hoạch lưu trữ. Ta cần có tủ tục để rebuild index tới tablespace mới INDX_NEW. Đây là quá trình tiêu tốn nhiều thời gian và tài nguyên nên việc đưa thủ tục vào job để không phụ thuộc vào sesion là bắt buộc để đảm bảo an toàn hệ thống:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INDEX_MOVE_01',
job_type => 'PLSQL_BLOCK',
job_action => q'[
--Chỉ lặp 1 lần
DECLARE
v_tablespace VARCHAR2(50) := 'INDX_NEW';
CURSOR c1 IS
SELECT a.index_name, a.partition_name
FROM USER_IND_PARTITIONS a
WHERE a.index_name = 'TABLE1_I1'
AND a.status = 'USABLE';
BEGIN
FOR i1 IN c1
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX oaz21.'
|| i1.index_name
|| ' REBUILD PARTITION '
|| i1.partition_name
|| ' TABLESPACE '
|| v_tablespace
|| ' nologging parallel 8 online';
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/-- Kiểm tra tablespace INDX_NEW ta thấy hệ thống đang tạo các index mới:
-- Sau khi tạo xong index mới, tiếp tục chạy thủ tục set nologging, noparallel cho các index:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'INDEX_NOPARALLEL_02',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
CURSOR c1 IS
SELECT DISTINCT index_name
FROM USER_IND_PARTITIONS
WHERE index_name = 'TABLE1_I1'
AND status = 'USABLE';
BEGIN
FOR i1 IN c1 LOOP
EXECUTE IMMEDIATE 'ALTER INDEX oaz21.' || i1.index_name || ' NOLOGGING NOPARALLEL';
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'no comment'
);
END;
/-- Thủ tục tự động tạo partition kết hợp re-build cho part mới:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'CREATE_PART_INDX_JOB09',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE
v_nam NUMBER (4) := 2025; -- Năm 2025
v_tablename VARCHAR2 (50) := 'table1'; -- Tên bảng
v_date_from DATE := TO_DATE ('31/12/2025', 'dd/mm/yyyy'); -- Ngày bắt đầu
v_date_to DATE := TO_DATE ('01/07/2025', 'dd/mm/yyyy'); -- 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;
-- Thêm các phân vùng mới
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;
-- Rebuild lại chỉ mục cho các phân vùng mới
FOR i IN 0 .. v_numday LOOP
EXECUTE IMMEDIATE
'ALTER INDEX ' || v_tablename || '_I1 REBUILD PARTITION DATA' || TO_CHAR (v_date_from + i, 'YYYYMMDD') || ' TABLESPACE ' || v_tablespace || ' ONLINE';
EXECUTE IMMEDIATE
'ALTER INDEX ' || v_tablename || '_I2 REBUILD PARTITION DATA' || TO_CHAR (v_date_from + i, 'YYYYMMDD') || ' TABLESPACE ' || v_tablespace || ' ONLINE';
END LOOP;
END;
]',
start_date => SYSTIMESTAMP,
repeat_interval => NULL,
end_date => NULL,
enabled => TRUE,
auto_drop => TRUE,
comments => 'Job to create daily partitions for table1 for the year 2024'
);
END;
/====
-- Tất cả mọi thứ mình đều tham khảo ở đây. Bản blog này chỉ đơn giản là mình muốn có một chỗ lưu các script vì công cụ lưu như Git, OneNote, Keep không phải lúc nào cũng tiện dụng:
7.TABLESPACE
https://www.tranvanbinh.vn/2020/07/quan-tri-tablespace-oracle.html
https://www.tranvanbinh.vn/2021/02/xu-ly-khi-u01-u02-chua-datafile-ay-99.html
https://www.tranvanbinh.vn/2021/02/cach-xu-ly-loi-ora-19815-warning.html
https://www.tranvanbinh.vn/2021/04/job-tu-ong-xoa-log-file-trong-oracle.html
https://www.tranvanbinh.vn/2021/02/xu-ly-khi-u01-u02-chua-datafile-ay-99.html
https://www.tranvanbinh.vn/2021/01/quy-trinh-kiem-tra-nhanh-oracle.html
https://www.tranvanbinh.vn/2021/10/thu-tuc-add-ia-vao-asm-diskgroup-trong.html
https://www.tranvanbinh.vn/2020/07/quy-trinh-add-o-ia-moi-vao-diskgroup.html
https://www.tranvanbinh.vn/2018/08/thu-tuc-add-ia-vao-diskgroup-data-trong.html
https://www.tranvanbinh.vn/2020/11/script-resize-datafile.html
https://www.tranvanbinh.vn/2018/09/chuyen-du-lieu-tu-tablespace-indx-sang.html
https://www.tranvanbinh.vn/2020/11/asmchuyen-du-lieu-tu-phan-vung-data.html
https://www.tranvanbinh.vn/2018/09/chuyen-du-lieu-tu-tablespace-khac-sang.html
https://www.tranvanbinh.vn/2020/11/thu-tuc-chuyen-du-lieu-sang-phan-vung.html
https://www.tranvanbinh.vn/p/admin.html
PARTITION,INDEX:
https://www.tranvanbinh.vn/p/admin-objects.html
https://www.tranvanbinh.vn/2022/02/khi-nao-can-tao-partition-cho-csdl.html
https://www.tranvanbinh.vn/2020/12/quan-ly-bang-partitionfull.html
https://www.tranvanbinh.vn/2019/12/lam-nao-tao-partion-het-nam-2020-cho-ca.html
Nhận xét
Đăng nhận xét