人工的方式去區隔了不同年代 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:
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 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
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:
|
沒有留言:
張貼留言