Oracle Interval Partition 與 Housekeeping

李易澤 Starck Lee 2020/12/05 14:53:57
616

前言:

當一個資料表(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

 

李易澤 Starck Lee