Partitioning – RANGE INTERVAL

1.) Create table:

drop table cmagno.test_pt_int;  
create table cmagno.test_pt_int (
  num number,
  text varchar2(1000),
  dt date
)
PARTITION BY RANGE (dt) INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
( 
   PARTITION p1 VALUES LESS THAN (TO_DATE('08-04-2016', 'DD-MM-YYYY'))
); 

2.) Inserting Data:


declare

i number;
v_dt date;

begin

  for i in 1..100 loop    
      v_dt := trunc(sysdate+i);
      insert into cmagno.test_pt_int 
      values (i,'DATE: ' || to_char(v_dt) ,v_dt);
  end loop;
  commit;

end;
/

3.) Stats:

exec dbms_stats.gather_table_stats('CMAGNO','TEST_PT_INT');

4.) Partitions:

select count(*) 
from dba_tab_partitions 
where table_name='TEST_PT_INT';

            COUNT(*)
--------------------
                 101

1 row selected.

-- 1 row per partition

5.) Changing the interval, from 1 day to 1 week:

ALTER TABLE cmagno.TEST_PT_INT  SET INTERVAL(NUMTODSINTERVAL (7, 'DAY')) ;

16:17:50 SQL> L
  1  select OWNER, TABLE_NAME, PARTITIONING_TYPE, INTERVAL
  2* from dba_part_tables where table_name='TEST_PT_INT'
16:17:53 SQL> /

OWNER           TABLE_NAME                     PARTITION INTERVAL
--------------- ------------------------------ --------- ----------------------------------------
CMAGNO          TEST_PT_INT                    RANGE     NUMTODSINTERVAL (7, 'DAY')

1 row selected.

6.) Inserting Data:

declare

i number;
v_dt date;

begin

  for i in 102..202 loop
      v_dt := trunc(sysdate+i);
      insert into cmagno.test_pt_int 
      values (i,'DATE: ' || to_char(v_dt) ,v_dt);
  end loop;
  commit;

end;
/

7.) Stats:

exec dbms_stats.gather_table_stats('CMAGNO','TEST_PT_INT');

8.) Partitions:

select count(*), num_rows
from dba_tab_partitions
where table_name='TEST_PT_INT'
group by num_rows;

            COUNT(*)             NUM_ROWS
-------------------- --------------------
                 100                    1 --> Interval with 1 DAY
                   1                    6 --> Interval with 7 DAY
                   1                    4 --> Interval with 7 DAY
                  13                    7 --> Interval with 7 DAY
                   1                    0 --> Start partition

5 rows selected.


9.) Conclusion:

With INTERVAL option on partition tables is easy 
to adapt your data in a desired RANGE PARTITION.
You can start with a RANGE PARTITION by week and 
if you data are growing up fast you can change 
to RANGE PARTITION per day.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s