Convert Range-Partitioned Table To Interval-Range-Partitioned Table

     first of all,Interval partitioning is an extension of range partitioning, where the system is able to create new partitions as they are required. The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.The INTERVAL clause of theCREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. 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.
   For example, if you create an interval partitioned table with monthly intervals and the transition point is at January 1, 2010, then the lower boundary for the January 2010 interval is January 1, 2010. The lower boundary for the July 2010 interval is July 1, 2010, regardless of whether the June 2010 partition was previously created. Note, however, that using a date where the high or low bound of the partition would be out of the range set for storage causes an error. For example, TO_DATE('9999-12-01', 'YYYY-MM-DD') causes the high bound to be 10000-01-01, which would not be storable if 10000 is out of the legal range.         For interval partitioning, the partitioning key can only be a single column name from the table and it must be of NUMBER or DATE type. The optional STORE IN clause lets you specify one or more tablespaces into which the database stores interval partition data using a round-robin algorithm for subsequently created interval partitions.

SQL> CREATE TABLE new_interval_sales
  2      ( prod_id        NUMBER(6) not null
  3      , cust_id        NUMBER  not null
  4      , time_id        DATE  not null
  5      , channel_id     CHAR(1)  not null
  6      , promo_id       NUMBER(6) not null
  7      , quantity_sold  NUMBER(3)  not null
  8      , amount_sold    NUMBER(10,2)
  9      )
10    PARTITION BY RANGE (time_id)
11    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
12      ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
13        PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
14        PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
15        PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

Table created.
SQL> SELECT table_name, partition_name, high_value, num_rows
  2  FROM   user_tab_partitions
  3  where  table_name=upper('new_interval_sales')
  4  ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
NEW_INTERVAL_SALES        P0                   TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

NEW_INTERVAL_SALES        P1                   TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

NEW_INTERVAL_SALES        P2                   TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

NEW_INTERVAL_SALES        P3                   TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


Now I will Convert Range-Partitioned Table To Interval-Partitioned Table like as:

SQL> CREATE TABLE new_sales
  2    ( prod_id       NUMBER(6)
  3    , cust_id       NUMBER
  4    , time_id       DATE
  5    , channel_id    CHAR(1)
  6    , promo_id      NUMBER(6)
  7    , quantity_sold NUMBER(3)
  8    , amount_sold   NUMBER(10,2)
  9    )
10   PARTITION BY RANGE (time_id)
11   ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
12   , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
13   , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
14   , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
15   );

Table created.

SQL> 
SQL> 
SQL> 
SQL> 
SQL> SELECT table_name, partition_name, high_value, num_rows
  2  FROM   user_tab_partitions
  3  where  table_name=upper('new_sales')
  4  ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
NEW_SALES                 SALES_Q1_2006        TO_DATE(' 2006-04-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

NEW_SALES                 SALES_Q2_2006        TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

NEW_SALES                 SALES_Q3_2006        TO_DATE(' 2006-10-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

NEW_SALES                 SALES_Q4_2006        TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> alter table new_sales set interval(interval '1' year);

Table altered.

SQL> 
SQL>  select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,INTERVAL  from user_part_tables where table_name=upper('new_sales');

TABLE_NAME                PARTITION PARTITION_COUNT INTERVAL
------------------------- --------- --------------- ------------------------------
NEW_SALES                 RANGE             1048575 INTERVAL '1' YEAR

SQL> 
SQL> 
SQL> SELECT table_name, partition_name, high_value, num_rows
  2  FROM   user_tab_partitions
  3  where  table_name=upper('new_sales')
  4  ORDER BY table_name, partition_name;

TABLE_NAME                PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
NEW_SALES                 SALES_Q1_2006        TO_DATE(' 2006-04-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

NEW_SALES                 SALES_Q2_2006        TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

NEW_SALES                 SALES_Q3_2006        TO_DATE(' 2006-10-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

NEW_SALES                 SALES_Q4_2006        TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-M
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Conditions and Restrictions
        The following restrictions apply to interval partitioned tables: 

•                 Interval partitioning is restricted to a single partition key that must be a numerical or date range.
•                 At least one partition must be defined when the table is created.
•                 Interval partitioning is not supported for index-organized tables.
•                 You cannot create a domain index on an interval partitioned table.
•                 Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can't be used at the subpartition level.
•                 A MAXVALUE partition cannot be defined for an interval partitioned table.
•                 NULL values are not allowed in the partition column.
  

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-2144177/，如需轉載，請註明出處，否則將追究法律責任。