Oracle Interval Partition 與 Housekeeping
前言:
當一個資料表(Table)擁有海量資料的同時，又需要提高搜尋效率、擴充性，我們可選擇分割資料表(Partition Table)的方式來執行
範例:
下列提供在Oracle建立Interval Partition(每月) 與 housekeeping 的簡易範例
1.建立Interval Partition table
create table test_interval_partitioning (
created_date date
)
partition by range (created_date)
interval (numtoyminterval(1,'MONTH'))
(
partition part_01 values less than (date '2020-01-01')
);
2.新增資料
insert into TEST_INTERVAL_PARTITIONING select date '2020-01-01' from dual;
insert into TEST_INTERVAL_PARTITIONING select date '2020-02-01' from dual;
insert into TEST_INTERVAL_PARTITIONING select date '2020-03-01' from dual;
insert into TEST_INTERVAL_PARTITIONING select date '2020-04-01' from dual;
insert into TEST_INTERVAL_PARTITIONING select date '2020-05-01' from dual;
3.查詢資料與PARTITION
select to_char(created_date,'yyyy-mm-dd') as created_date from TEST_INTERVAL_PARTITIONING;
select table_name, partition_name,high_value, interval from dba_tab_partitions where table_name = 'TEST_INTERVAL_PARTITIONING';
4.建立Stored procedure
create or replace procedure test_housekeeping
( p_year varchar2,
p_month varchar2,
p_table_name varchar2
)
as
l_part_name varchar2(30);
begin
select partition_name
into l_part_name
from all_tab_partitions
where table_name = p_table_name
and
extract (year from to_date (
trim (
'''' from regexp_substr (
extractvalue (
dbms_xmlgen.getxmltype (
'select high_value from all_tab_partitions where table_name='''
|| table_name
|| ''' and partition_name = '''
|| partition_name
|| ''''),
'//text()'),
'''.*?''')),
'yyyy-mm-dd hh24:mi:ss')) = p_year
and
extract (month from to_date (
trim (
'''' from regexp_substr (
extractvalue (
dbms_xmlgen.getxmltype (
'select high_value from all_tab_partitions where table_name='''
|| table_name
|| ''' and partition_name = '''
|| partition_name
|| ''''),
'//text()'),
'''.*?''')),
'yyyy-mm-dd hh24:mi:ss')) = p_month+1
;
-- truncating preceding partition
dbms_output.put_line('Trucating partition for preceding interval partition :' || l_part_name );
execute immediate 'ALTER TABLE ' || p_table_name || ' TRUNCATE PARTITION (' || l_part_name || ')';
dbms_output.put_line('Dropping partition for preceding interval partition :' || l_part_name);
execute immediate 'ALTER TABLE ' || p_table_name || ' DROP PARTITION (' || l_part_name || ')';
exception
when others then
dbms_output.put_line(sqlerrm);
raise;
end;
5.設定清除的月份
set serveroutput on;
begin
test_housekeeping('2021','1','TEST_INTERVAL_PARTITIONING');
end;
6.顯示結果
2月份已移除
參考資料
https://docs.oracle.com/database/121/CNCPT/schemaob.htm#CNCPT88859
https://docs.oracle.com/database/121/VLDBG/GUID-C121EA1B-2725-4464-B2C9-EEDE0C3C95AB.htm
https://datacadamia.com/db/oracle/partition/range_interval
