廣告聯播

2014年1月3日 星期五

Oracle Database 11g new feature : Example for Interval partitioning

From: Polin Wei

人工的方式去區隔了不同年代 tables ,以 DB 的作法,可以參考 Interval Partitioning ,固定一個區間,以月,或是年,自動去做分割 table ,可簡化人工介入的處理動作。

Title: Example for Interval partitioning - new feature in 11g
Version: 7
Created on: August 27, 2010 9:38 PM by LajosV - Moderator - Oracle
Last Modified: April 18, 2013 6:34 PM by LajosV - Moderator - Oracle
Viewed: 425 times
Since 11g, the new feature Interval partitioning makes it possible that the database automatically creates interval partitions as data for that partition arrives. 

We can establish interval partitioning on a table with: 

    • - CREATE TABLE statement for a new table, or
    • - ALTER TABLE statement for an existing ranged partitioned table.

In both cases the table needs to have at least one range partition (this is defined by the PARTITION clause).

The INTERVAL clause establishes interval partitioning for the table, here we set the interval for new partitions, which must be a
fix interval (e.g. Day, Month, Year). 

The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

The INSERT (data load) part of the following example is based on the Sales History (SH) demo schema. 

create table t_part (
    col_date date,
    col_text varchar2(100),
    col_number number(10))
    PARTITION BY RANGE (col_date)
     INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
    (
      PARTITION P_200105 VALUES LESS THAN (TO_DATE(' 2001-06-01', 'YYYY-MM-DD')),
      PARTITION P_200106 VALUES LESS THAN (TO_DATE(' 2001-07-01', 'YYYY-MM-DD'))
    );

SH@dwh112> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name ='T_PART'
  4  order by partition_position; 


PARTITION_NAME  HIGH_VALUE
--------------- ------------------------------------------------------------------------------------
P_200105        TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_200106        TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 

The range partitions have been created with the specified upper boundaries.

When we insert rows for which the partition key value is the same or higher than the high value of the last range partition, then
the corresponding interval partitions are created automatically. 

insert into t_part select time_id, CHANNEL_ID, amount_sold from sales where time_id between to_date('01-JUL-2001') and  ('02-JUL-2001');
commit;

PARTITION_NAME  HIGH_VALUE
--------------- -------------------------------------------------------------------------------------
P_200105        TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_200106        TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P4168       TO_DATE(' 2001-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P4167       TO_DATE(' 2001-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


According to the interval setting, new daily interval partitions have been created with system generated names.

In order to turn an existing the range partitioned table to interval partitioned, execute
 
 ALTER TABLE SET INTERVAL ()

e.g. if the above table was defined as a range partitioned table with 2 range partitions
 
  ...
    PARTITION BY RANGE (col_date)
    (
      PARTITION P_200105 VALUES LESS THAN (TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
      PARTITION P_200106 VALUES LESS THAN (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    ); 


The following statement would introduce interval partitioning with the same interval as we seen on the previous CREATE TABLE example: 

alter table t_part set INTERVAL(NUMTODSINTERVAL(1, 'DAY'));


The interval must be a fixed value for a particular table at a particular time, but it can be changed.

For example for the above table we can change the interval so that new partition will be created when new month value is inserted: 

alter table t_part SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

  

insert into t_part select time_id, CHANNEL_ID, amount_sold from sales where time_id between to_date('03-JUL-2001') and  ('02-SEP-2001');
commit;

PARTITION_NAME  HIGH_VALUE
--------------- ------------------------------------------------------------------------------------
P_200105        TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_200106        TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P4168       TO_DATE(' 2001-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P4167       TO_DATE(' 2001-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P4170       TO_DATE(' 2001-08-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P4169       TO_DATE(' 2001-09-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 

The high values of the newly created interval partitions reflect the change in interval to MONTH. 

alter table t_part
merge partitions
  for(TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
  for(TO_DATE(' 2001-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))  into partition P_2001_JUL1; 


Note, we specify the partition key value in the FOR clause, the partition where this partition key value falls will be merged.
We could obviously use the partition name instead of the FOR clause,
but being the partition name system generated using partition key value may give us a more generic command. 

PARTITION_NAME  HIGH_VALUE
--------------- ------------------------------------------------------------------------------------
P_200105        TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_200106        TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2001_JUL1     TO_DATE(' 2001-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P4170       TO_DATE(' 2001-08-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P4169       TO_DATE(' 2001-09-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 



We can further merge day partitions, or split, or rename partitions.

e.g. the following renames the SYS_P4169 system generated partition name to P_AUG 

alter table t_part rename partition SYS_P4169 to P_AUG;

PARTITION_NAME  HIGH_VALUE
--------------- ------------------------------------------------------------------------------------
P_200105        TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_200106        TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2001_JUL1     TO_DATE(' 2001-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P4170       TO_DATE(' 2001-08-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_AUG           TO_DATE(' 2001-09-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 


For complete list of partition maintenance operations and their implications on dependent objects see 
documentation .

We can disable interval partitioning with the following statement:  

 ALTER TABLE t_part SET INTERVAL ();


This makes the table range partitioned with the existing partitions as ranged ones, the INTERVAL clause longer appears in the DDL of the table,
and the system generated partition names SYS_P.. will be used as names of the range partitions

    e.g.
    CREATE TABLE "T_PART"
       (    "COL_DATE" DATE,
            "COL_TEXT" VARCHAR2(100),
            "COL_NUMBER" NUMBER(10,0)
       )
      PARTITION BY RANGE ("COL_DATE")
     (PARTITION "P_200105"  VALUES LESS THAN (TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))...,
     PARTITION "P_200106"  VALUES LESS THAN (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ...,
     PARTITION "P_2001_JUL1"  VALUES LESS THAN (TO_DATE(' 2001-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ...,
     PARTITION "SYS_P4170"  VALUES LESS THAN (TO_DATE(' 2001-08-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))...,
    ...

Further examples for interval partitioning can be found in the following sources:
Oracle by Example (ObE) documents:

Knowledge documents:

沒有留言:

張貼留言