HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Multiplication of partitions
ORACLE STUFFS
04-Aug-2008
Several partitioning strategies use partitioning by time (one of partitioning columns) to allow ageing of data. On the way of regular ETL process new partitions shold be added automatically in some kind of pre-process, anyway task to multiply partition either before an initial load or migration of current data into partitioned state (repartitioning) remains.

Following sample script is designed for pattern of partitioning by two columns, when the second is date (PARTITION BY RANGE (numbercolumn, datecolumn)). To be able split both empty and full partition it is implemented half-division algorithm. It is efficient mostly for huge amounts of data - minimizes amount of moved data.

Declare
  c_table_owner varchar2(30):='A2';
  c_table_name  varchar2(30):='TTT1';
  c_part_prefix varchar2(30):='P';
  c_granularity varchar2(10):='MM'; --YY ... year MM ... month; IW ... week; DD ... day
  c_min_date    date:=to_date('20081129','yyyymmdd');--safety minimum
  c_max_date    date:=to_date('20081230','yyyymmdd');--safety maximum
  hi_bound EXCEPTION;
  PRAGMA EXCEPTION_INIT(hi_bound,-14080);
  ---
  procedure splitPartition(
    p_partition_name varchar2
  ) is 
    hv_lo Varchar2(2000);
    hv_hi Varchar2(2000);
    lo_date Date;
    hi_date Date;
    v_dist  Integer;
    mid_date Date;
    v_stmt  varchar2(8000);
    v_split_name varchar2(30);
    v_split_yyyymmdd varchar2(30); 
  begin
    Select a.high_value, b.high_value prev_hv into hv_hi,hv_lo 
      from (select partition_name, partition_position,high_value,lag(partition_position)over(order by partition_position) prepos
            from all_tab_partitions 
            where table_owner=c_table_owner and table_name=c_table_name) a 
      left join all_tab_partitions b on b.table_owner=c_table_owner and b.table_name=c_table_name and a.prepos=b.partition_position 
      where a.partition_name=p_partition_name;
    if hv_lo is null then
      lo_date:=c_min_date;
    else  
      execute immediate 'select '||hv_lo||' from dual' into lo_date;
    end if;  
    lo_date:=trunc(greatest(c_min_date,lo_date),c_granularity);
    execute immediate 'select '||replace(hv_hi,'MAXVALUE','to_date(''40001231'',''yyyymmdd'')')||' from dual' into hi_date;
    hi_date:=trunc(least(hi_date,c_max_date+2),c_granularity);
    if regexp_like(c_granularity,'^Y','i') then --yearly
       v_dist:=floor(months_between(hi_date,lo_date)/12);  
    elsif regexp_like(c_granularity,'^M','i') then --monthly
       v_dist:=months_between(hi_date,lo_date);  
    elsif regexp_like(c_granularity,'W$','i') then --weekly
       v_dist:=floor((hi_date-lo_date)/7); 
    else -- daily
       v_dist:=hi_date-lo_date; 
    end if; 
    if v_dist>1 then
      if regexp_like(c_granularity,'^Y','i') then --yearly
         mid_date:=add_months(lo_date,v_dist*6);
      elsif regexp_like(c_granularity,'^M','i') then --monthly
         mid_date:=add_months(lo_date,floor(v_dist/2));
      elsif regexp_like(c_granularity,'W$','i') then --weekly
         mid_date:=lo_date+floor(v_dist*3.5);    
      else -- daily
         mid_date:=lo_date+floor(v_dist/2);    
      end if; 
      mid_date:=trunc(mid_date,c_granularity);
      v_split_name :=c_part_prefix||'_'||to_char(mid_date-1,'YYYYMMDD');--depends on naming convention
      v_split_yyyymmdd :=to_char(mid_date,'YYYYMMDD');
      v_stmt:='ALTER TABLE '||c_table_owner||'.'||c_table_name
       ||' split partition '||P_partition_name||' at (to_date('''||v_split_yyyymmdd||''',''YYYYMMDD''))'
     ||' into (PARTITION '||v_split_name||',PARTITION '||p_partition_name||')';
    begin  
        execute immediate v_stmt;
        if v_dist>2 then
          splitPartition(v_split_name);  
        end if;
      exception 
        when hi_bound then null;
      end;
      if v_dist>2 then
        splitPartition(p_partition_name);  
      end if;
    end if;
  end splitPartition;
Begin
  for r1 in (select partition_name from all_tab_partitions b where b.table_owner=c_table_owner and b.table_name=c_table_name) LOOP
    splitPartition(r1.partition_name);  
  end LOOP;
End;
/

[Download]

Following script is usefull to repair unwanted split. It merges all the partition into one default last partition.
It is intended as a correcting script only.

Declare
  c_table_owner varchar2(30):='A2';
  c_table_name  varchar2(30):='PARTY';
  b   boolean:=true;
  lp  varchar2(100);
  ---
  procedure mergePartition(
    p_partition_name varchar2
   ,p_prev varchar2
  ) is 
    v_stmt varchar2(8000);
  begin
    v_stmt:='ALTER TABLE '||c_table_owner||'.'||c_table_name||' merge partitions '||p_prev||','||p_partition_name
     ||' into PARTITION '||p_partition_name;
    execute immediate v_stmt;
  end mergePartition;
Begin
  for r1 in (select partition_name from all_tab_partitions b 
    where b.table_owner=c_table_owner and b.table_name=c_table_name 
    order by partition_position desc
  )LOOP
    if b then
      lp:=r1.partition_name;
      b:=false;
    else
      mergePartition(lp,r1.partition_name);  
    end if;
  end LOOP;
End;
/

[Download]

After all that actions it is necessary to rebuild indexes:

See: Rebuild indexes
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky