文档章节

oracle Interval 分区维护与管理要点

易野
 易野
发布于 2017/06/02 15:36
字数 5268
阅读 81
收藏 0

本文取自oracle网官网,以在日常工作中备查

Interval Partitioning Essentials - Common Questions - Top Issues (Doc ID 1479115.1

 

In this Document

 

Purpose

Questions and Answers

What is interval partitioning?

Where can i find examples?

Can i specify interval partitioning at subpartition level?

Are there any restrictions for the partition key (e.g. data type)?

Can i have Domain Index on an interval partitioned table?

How to specify/change the tablespace storage for the interval partitions

What will be the names of the automatically created interval partitions?

What happens if i want to set a new interval for the table?

How to perform Partition Maintenance Operations (PMOP)?

Exchanging a Partition of an Interval Partitioned Table

Dropping interval partition

Merging partitions

Splitting interval partition

Renaming interval partition

How to disable / turn off interval partitioning for a table?

Partition Count/Pruning

Can i specify NULL value for the partitioning key column?

Why the system generated interval partitions are missing from the DDL of the table when i retrieve it with the function DBMS_METADATA.GET_DDL?

Can interval partitioned table be a parent table for reference partitioning?

References

 

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 12.1.0.2 [Release 11.1 to 12.1]

Information in this document applies to any platform.

Purpose

To answer common questions, to provide directions to readers to the relevant information related to interval partitioning.

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

 

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.

Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Datawarehousing.

 

Questions and Answers

What is interval partitioning?

Interval partitioning is new feature introduced in version 11.1. This feature makes it possible to get the corresponding partitions created automatically as data comes in for the corresponding partition. Before the interval partition feature became available often a maxvalue partition was created to avoid the ORA-14400: inserted partition key does not map to any partition, and time to time the max partition was split into 2 partition to reduce the catch all maxvalue partition.

As an extension of range partitioning the interval partitioning instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition. The range partition key value determines the high value of the range partitions, which is called the transition point, and the database creates interval partitions for data with values that are beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

See also Document 805976.1 11g New Features:INTERVAL PARTITIONING

 

 

Where can i find examples?

Interval partitioning can be specified at creation time of the table or it can be set for an existing range partitioned table.

CREATE TABLE

...

PARTITION BY RANGE (<partition key>)

     INTERVAL(<number or interval expression>) [STORE IN (<tablespace list>)]

...

(

      PARTITION <partition name> ...          ---> definition of range partition(s)

...

);

In order to turn an existing range partitioned table into interval partitioned table, execute the following command:

ALTER TABLE <table_name> SET INTERVAL (<number or interval expression>);

You can find examples for interval partitioned tables in the following sources.

Knowledge documents:

Oracle by Example (ObE) documents:

MOS Database DataWarehousing document:

See also Interval Literals in the Oracle Database SQL Language Reference.

 

 

Can i specify interval partitioning at subpartition level?

No, interval partitioning cannot be specified at subpartition level. There is an enhancement logged to allow this feature at subpartition level.

If you attempt doing that you may get error ORA-00922: missing or invalid option.

    

 

    

    This is documented in 11.2 documentation  (Oracle Database SQL Language Reference at CREATE TABLE):

   

    "Restrictions on Interval Partitioning

    ...

    Interval partitioning is not supported at the subpartition level."

 

 

Are there any restrictions for the partition key (e.g. data type)?

Yes. You can only specify one partitioning key column, and it must be of NUMBER or DATE type.

The TIMESTAMP data type is an extension of the DATE data type, and taking into account the general restrictions on Partitioning Key Columns columns of TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE can be used in the partitioning key (TIMESTAMP WITH TIME ZONE not).

 

    Refer to Oracle® Database VLDB and Partitioning Guide - Chapter 2 Partitioning Concepts (e.g. for 11.2):

 

    "When using interval partitioning, consider the following restrictions:

           * You can only specify one partitioning key column, and it must be of NUMBER or DATE type."

   Oracle Database SQL Language Reference at CREATE TABLE (e.g. for 11.2):

        "...Restriction on Partitioning Key Columns

        The columns in the column list can be of any built-in data type except

        ROWID, LONG, LOB, XMLType, or TIMESTAMP WITH TIME ZONE.

        However, columns of TIMESTAMP or TIMESTAMP WITH LOCAL TIME ZONE can be used in the partitioning key.

        ..."

If you attempt to use varchar2 column for the partition key, then you may get ORA-14037: partition bound of partition "string" is too high

or ORA-14751: Invalid data type for partitioning column of an interval partitioned table (for the latter see Note 1635063.1).

 

 

Can i have Domain Index on an interval partitioned table?

In 11g Domain Index is not supported on an interval partitioned table. Trying to create domain index on interval partitioned table results in error ORA-14762: Domain index creation on interval partitioned tables is not permitted, which is expected.  ALTER TABLE SET INTERVAL (..) command, that introduces interval partitioning on a range partitioned table may leave existing domain index unnoticed, this is bug 16811830.

In 12 Domain Index is supported on an interval partitioned table, hence you do not get the above mentioned error.

 

 

How to specify/change the tablespace storage for the interval partitions

The optional STORE IN part of the INTERVAL clause is for specifying which tablespaces the interval partitions should be created. If a list of tablespaces are specified, then the intervals partitions get created on those tablespaces in a round robin fashion:

INTERVAL(<number of interval expression>) STORE IN (<tablespace list>)

Round Robin according to the logical interval partitions not according to the interval partitions physically realized. See Note 1552239.1 Placement Into Tablespaces of Interval Partitions Are Not Round Robin.

See more information in the following knowledge documents:

  • Document 874374.1 How to change the tablespace storage of the INTERVAL-PARTITIONED table
  • Document 1329950.1 Ability to define Tablespace with INTERVAL PARTITION Based on the Value of the Partition Key

For interval partitions that have been created already, you can use the following command to move it into a particular tablespace:

ALTER TABLE <table name> MOVE PARTITION <partition name> TABLESPACE <tablespace name>; 

 

The STORE IN (<tablespace list>) clause does not accept storage parameters, Oracle considers default parameters set for table or for tablespace when creates interval partitions.

 For example, interval partitions (SYS_P12016, SYS_P12017, SYS_P12018) are created with 8MB (default) initial extent size in 11.2.0.2 and above unless table default attribute is set differently

dba_segments:

SEGMENT_NA PARTITION_NAME            INITIAL_EXTENT in KB TABLESPACE_NAME

---------- ------------------------- -------------------- -------------------------

T_PART     P_200105                                  8192 USERS

T_PART     P_200106                                  8192 USERS

T_PART     SYS_P12016                                8192 EXAMPLE

T_PART     SYS_P12017                                8192 EXAMPLE

T_PART     SYS_P12018                                8192 EXAMPLE

 

SQL> alter table t_part  modify default attributes storage(initial 64k);

 

Table altered.

 

SQL> insert into t_part select time_id, CHANNEL_ID, amount_sold from sales where time_id between to_date('04-JUL-2001') and  to_date('07-JUL-2001');

 

1691 rows created.

 

SQL> commit;

 

Commit complete.

 

dba_segments:

SEGMENT_NA PARTITION_NAME            INITIAL_EXTENT in KB TABLESPACE_NAME

---------- ------------------------- -------------------- -------------------------

T_PART     P_200105                                  8192 USERS

T_PART     P_200106                                  8192 USERS

T_PART     SYS_P12016                                8192 EXAMPLE

T_PART     SYS_P12017                                8192 EXAMPLE

T_PART     SYS_P12018                                8192 EXAMPLE

T_PART     SYS_P12019                                  64 EXAMPLE

T_PART     SYS_P12020                                  64 EXAMPLE

T_PART     SYS_P12021                                  64 EXAMPLE

T_PART     SYS_P12022                                  64 EXAMPLE

 

9 rows selected.

 

 

 

What will be the names of the automatically created interval partitions?

The names of the interval partitions created by the database will be system generated names.

You can see the partition names by querying the dba_tab_partitions view or its variants:

    Table             PARTITION_NAME            HIGH_VALUE                          

    ----------------- ------------------------- -------------------------------

...

    T_PART            SYS_P5793                 TIMESTAMP' 2001-07-03 00:00:00'

    T_PART            SYS_P5794                 TIMESTAMP' 2001-07-04 00:00:00'

Currently it is not possible to specify a mask or template for partition names, but the system generated name can be renamed, see partition maintenance section of this note for further information.

 

 

What happens if i want to set a new interval for the table?

You just alter table with the new interval setting.

e.g. you can change the interval in the following way:

ALTER TABLE t_part SET INTERVAL(NUMTODSINTERVAL(1,'DAY'));

 Indexes are not made unusable due to the above operation.

Please note this operation will turn all existing interval partitions to range partitions, and move the transition point to the highest bound.

The following example illustrates this.

  

SH@dw24> create table t_part (

  2       col_date date,

  3        col_number number(10))

  4       PARTITION BY RANGE (col_date)

  5       interval (NUMTOYMINTERVAL(1,'MONTH'))

  6       (

  7       PARTITION P_200105 VALUES LESS THAN (TO_DATE('2001-06-01', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN')),

  8       PARTITION P_200106 VALUES LESS THAN (TO_DATE('2001-07-01', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'))

  9       )

 10      ;

 

Table created.

 

SH@dw24>

SH@dw24> insert into t_part values (TO_DATE('2001-07-05', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SH@dw24> insert into t_part values (TO_DATE('2001-08-07', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SH@dw24> insert into t_part values (TO_DATE('2001-09-08', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SH@dw24> insert into t_part values (TO_DATE('2001-09-11', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SH@dw24> commit;

 

Commit complete.

 

SH@dw24>

SH@dw24> -- 3 interval partitions created after the above 4 rows inserted, 1 interval partition for each month 07,08,09 (INTERVAL column is YES).

SH@dw24> select table_name, partition_name, partition_position part_pos,interval,high_value from user_tab_partitions   where table_name = 'T_PART'   order by table_name, partition_position;

 

TABLE_NAME      PARTITION_NAME    PART_POS INTERVAL HIGH_VALUE

--------------- --------------- ---------- -------- --------------------------------------------------------------------------------

T_PART          P_200105                 1 NO       TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          P_200106                 2 NO       TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61329               3 YES      TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61330               4 YES      TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61331               5 YES      TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

SH@dw24>

SH@dw24> -- Change partition interval

SH@dw24> alter table t_part set INTERVAL(NUMTODSINTERVAL(1,'DAY'));

 

Table altered.

 

SH@dw24>

SH@dw24> -- Notice that the formerly interval partitions became range partitions (INTERVAL column is NO)

SH@dw24> -- and the high bound of the last partition became the transition point, where the interval partitions start

SH@dw24>

SH@dw24> select table_name, partition_name, partition_position part_pos,interval,high_value from user_tab_partitions   where table_name = 'T_PART'   order by table_name, partition_position;

 

TABLE_NAME      PARTITION_NAME    PART_POS INTERVAL HIGH_VALUE

--------------- --------------- ---------- -------- --------------------------------------------------------------------------------

T_PART          P_200105                 1 NO       TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          P_200106                 2 NO       TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61329               3 NO       TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61330               4 NO       TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61331               5 NO       TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

SH@dw24>

SH@dw24> -- Note that due to the transition point changed the following 3 inserts will not create new day partitions,

SH@dw24> -- rows will go into existing range partitions

SH@dw24> insert into t_part values (TO_DATE('2001-07-07', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SH@dw24> insert into t_part values (TO_DATE('2001-07-08', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SH@dw24> insert into t_part values (TO_DATE('2001-07-11', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SH@dw24> commit;

 

Commit complete.

 

SH@dw24>

SH@dw24> select table_name, partition_name, partition_position part_pos,interval,high_value from user_tab_partitions   where table_name = 'T_PART'   order by table_name, partition_position;

 

TABLE_NAME      PARTITION_NAME    PART_POS INTERVAL HIGH_VALUE

--------------- --------------- ---------- -------- --------------------------------------------------------------------------------

T_PART          P_200105                 1 NO       TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          P_200106                 2 NO       TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61329               3 NO       TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61330               4 NO       TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61331               5 NO       TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

SH@dw24>

SH@dw24> -- These rows will create new day partitions because the partition keys inserted are higher than the transition point

SH@dw24> insert into t_part values (TO_DATE('2001-10-01', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SH@dw24> insert into t_part values (TO_DATE('2001-10-02', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SH@dw24> commit;

 

Commit complete.

 

SH@dw24>

SH@dw24> select table_name, partition_name, partition_position part_pos,interval,high_value from user_tab_partitions   where table_name = 'T_PART'   order by table_name, partition_position;

 

TABLE_NAME      PARTITION_NAME    PART_POS INTERVAL HIGH_VALUE

--------------- --------------- ---------- -------- --------------------------------------------------------------------------------

T_PART          P_200105                 1 NO       TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          P_200106                 2 NO       TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61329               3 NO       TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61330               4 NO       TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61331               5 NO       TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61332               6 YES      TO_DATE(' 2001-10-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART          SYS_P61333               7 YES      TO_DATE(' 2001-10-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

7 rows selected.

 

 

How to perform Partition Maintenance Operations (PMOP)?

In pmop operations you have to refer to the partition(s).  You can query the *_TAB_PARTITIONS data dictionary view to find out the system-generated partition name, or you can use the FOR syntax to identify a partition that was system-generated (currently FOR syntax works for table only, not for indexes). 

Exchanging a Partition of an Interval Partitioned Table

You can exchange interval partitions in an interval-partitioned table. However, you have to make sure the interval partition has been created before you can exchange the partition. You can let the database create the partition by locking the interval partition.

 

The following example shows a partition exchange for the T_PART table, interval-partitioned using daily partitions. This example shows how to add data for 6th of July 2001 to the table using partition exchange load.

    Table             PARTITION_NAME            HIGH_VALUE                          

    ----------------- ------------------------- -------------------------------

    T_PART            P_200105                  TIMESTAMP' 2001-06-01 00:00:00'

    T_PART            P_200106                  TIMESTAMP' 2001-07-01 00:00:00'

    T_PART            SYS_P5793                 TIMESTAMP' 2001-07-03 00:00:00'

    T_PART            SYS_P5794                 TIMESTAMP' 2001-07-04 00:00:00'

    T_PART            SYS_P5795                 TIMESTAMP' 2001-07-02 00:00:00'

    T_PART            SYS_P5796                 TIMESTAMP' 2001-07-05 00:00:00'

 

-- as can be see in dba_tab_partitions, there is no partition created to accommodate 06-JUL-2001,

-- hence we execute the following command, which creates the corresponding partition

 

    LOCK TABLE t_part

    PARTITION FOR (TO_DATE('06-JUL-2001','dd-MON-yyyy'))

    IN SHARE MODE;

 

-- the corresponding partition SYS_P5813 is created

 

    Table             PARTITION_NAME            HIGH_VALUE                          

    ----------------- ------------------------- --------------------------------

    T_PART            P_200105                  TIMESTAMP' 2001-06-01 00:00:00'     

    T_PART            P_200106                  TIMESTAMP' 2001-07-01 00:00:00'     

    T_PART            SYS_P5793                 TIMESTAMP' 2001-07-03 00:00:00'     

    T_PART            SYS_P5794                 TIMESTAMP' 2001-07-04 00:00:00'     

    T_PART            SYS_P5795                 TIMESTAMP' 2001-07-02 00:00:00'     

    T_PART            SYS_P5796                 TIMESTAMP' 2001-07-05 00:00:00'     

    T_PART            SYS_P5813                 TIMESTAMP' 2001-07-07 00:00:00'     

 

-- so you can execute the exchange partition command now

 

    ALTER TABLE interval_sales

    EXCHANGE PARTITION FOR (TO_DATE('06-JUL-2001','dd-MON-yyyy'))

    WITH TABLE interval_sales_jul_2001

    ....;    

Dropping interval partition

This operation drops the data for the interval only and leaves the interval definition intact. The partition segment is dropped, so it does no longer appear in the dba_tab_partitions view. If data is inserted in the interval just dropped, then the database again creates an interval partition.

    ALTER TABLE T_PART DROP PARTITION FOR (TO_DATE('06-JUL-2001','dd-MON-yyyy'));

See also Document 1285036.1 How To Drop The Interval Partitions?

Merging partitions

The contents of two adjacent interval partitions can be merged into one partition. Nonadjacent interval partitions cannot be merged. The first interval partition can also be merged with the highest range partition.

The resulting partition can be a new partition name or the name of the highest partition of the merged ones (otherwise error ORA-14275: cannot reuse lower-bound partition as resulting partition).

Worth noting that merging interval partitions will move the transition point where the interval partitioning starts to the high bound of the resulting partition. All partitions below the transition point became range partitions.

An example can be seen below.

SQL>  create table t_part (

  2   col_date date,

  3    col_number number(10))

  4   PARTITION BY RANGE (col_date)

  5   interval(numtodsinterval(1,'day'))

  6   (

  7   PARTITION P_200105 VALUES LESS THAN (TO_DATE('2001-06-01', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN')),

  8   PARTITION P_200106 VALUES LESS THAN (TO_DATE('2001-07-01', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'))

  9   )

 10   ;

 

Table created.

 

SQL> insert into t_part values (TO_DATE('2001-07-05', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SQL> insert into t_part values (TO_DATE('2001-07-07', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SQL> insert into t_part values (TO_DATE('2001-07-08', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SQL> insert into t_part values (TO_DATE('2001-07-11', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select table_name, partition_name, partition_position part_pos

  2  ,interval,high_value

  3  from user_tab_partitions

  4  where table_name = 'T_PART'

  5  order by table_name, partition_position;

 

TABLE_NAME                     PARTITION_NAME                   PART_POS INT HIGH_VALUE

------------------------------ ------------------------------ ---------- --- --------------------------------------------------------------------------------

T_PART                         P_200105                                1 NO  TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         P_200106                                2 NO  TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22469                              3 YES TO_DATE(' 2001-07-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22470                              4 YES TO_DATE(' 2001-07-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22471                              5 YES TO_DATE(' 2001-07-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22472                              6 YES TO_DATE(' 2001-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

6 rows selected.

 

The interval partitioning starts from the high bound of the highest range partition P_200106, and due to the inserts 4 interval partitions are created named SYS_P%.

Not that SYS_P22469 and SYS_P22470 partitions though look adjecent according to the current physical structure of the partitions, but considering the 1 DAY interval partitioning that is currently applied for the table,

these partitions are not adjecent, hence the merge of these partitions are expected to fails as it can be seen below:

SQL> alter table t_part merge partitions SYS_P22469, SYS_P22470 into partition MyFav;

alter table t_part merge partitions SYS_P22469, SYS_P22470 into partition MyFav

                                    *

ERROR at line 1:

ORA-14274: partitions being merged are not adjacent

 

 

SQL> alter table t_part merge partitions for (TO_DATE('2001-07-05', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN')),

  2  for (TO_DATE('2001-07-07', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN')) into partition MyFav;

alter table t_part merge partitions for (TO_DATE('2001-07-05', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN')),

*

ERROR at line 1:

ORA-14274: partitions being merged are not adjacent

Interval partitions SYS_P22470 and SYS_P22471 are adjecent even according to the interval partitioning clause applied on the table, hence these can be merged.

SQL> alter table t_part merge partitions SYS_P22470,SYS_P22471 into partition MyFav;

 

Table altered.

 

SQL> select table_name, partition_name, partition_position part_pos

  2  ,interval,high_value

  3  from user_tab_partitions

  4  where table_name = 'T_PART'

  5  order by table_name, partition_position;

 

TABLE_NAME                     PARTITION_NAME                   PART_POS INT HIGH_VALUE

------------------------------ ------------------------------ ---------- --- --------------------------------------------------------------------------------

T_PART                         P_200105                                1 NO  TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         P_200106                                2 NO  TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22469                              3 NO  TO_DATE(' 2001-07-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         MYFAV                                   4 NO  TO_DATE(' 2001-07-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22472                              5 YES TO_DATE(' 2001-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

 

Note however that by merging 2 interval partitions the transition point, where the interval partitioning starts, now moved to the high bound of the resulting partition MYFAV. All partitions below the transition point (including partition MYFAV) became range partitions, as this can be verified below.

 

SQL> insert into t_part values (TO_DATE('2001-07-04', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select table_name, partition_name, partition_position part_pos

  2  ,interval,high_value

  3  from user_tab_partitions

  4  where table_name = 'T_PART'

  5  order by table_name, partition_position;

 

TABLE_NAME                     PARTITION_NAME                   PART_POS INT HIGH_VALUE

------------------------------ ------------------------------ ---------- --- --------------------------------------------------------------------------------

T_PART                         P_200105                                1 NO  TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         P_200106                                2 NO  TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22469                              3 NO  TO_DATE(' 2001-07-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         MYFAV                                   4 NO  TO_DATE(' 2001-07-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22472                              6 YES TO_DATE(' 2001-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

 

SQL> insert into t_part values (TO_DATE('2001-07-10', 'YYYY-MM-DD', 'NLS_CALENDAR=GREGORIAN'),1);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select table_name, partition_name, partition_position part_pos

  2  ,interval,high_value

  3  from user_tab_partitions

  4  where table_name = 'T_PART'

  5  order by table_name, partition_position;

 

TABLE_NAME                     PARTITION_NAME                   PART_POS INT HIGH_VALUE

------------------------------ ------------------------------ ---------- --- --------------------------------------------------------------------------------

T_PART                         P_200105                                1 NO  TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         P_200106                                2 NO  TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22469                              3 NO  TO_DATE(' 2001-07-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         MYFAV                                   4 NO  TO_DATE(' 2001-07-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22473                              5 YES TO_DATE(' 2001-07-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22472                              6 YES TO_DATE(' 2001-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

6 rows selected.

Splitting interval partition

When you split an interval partition, the operation will also move the transition point to the upper-most endpoint of the new partition created from the split.

In the following example let's use table t_part from the previous section where the table is partitioned by interval 1 DAY.

Partition SYS_P22473 has its high bound 2001-07-11 00:00:00, so it is supposed to contain rows for 2001-07-10.

We split interval partition SYS_P22473 into a partition called MORNING and into the original partition SYS_P22473.

SQL> select table_name, partition_name, partition_position part_pos

  2  ,interval,high_value

  3  from user_tab_partitions

  4  where table_name = 'T_PART'

  5  order by table_name, partition_position;

 

TABLE_NAME                     PARTITION_NAME                   PART_POS INT HIGH_VALUE

------------------------------ ------------------------------ ---------- --- --------------------------------------------------------------------------------

T_PART                         P_200105                                1 NO  TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         P_200106                                2 NO  TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22469                              3 NO  TO_DATE(' 2001-07-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         MYFAV                                   4 NO  TO_DATE(' 2001-07-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22473                              5 YES TO_DATE(' 2001-07-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22472                              6 YES TO_DATE(' 2001-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

6 rows selected.

SQL> alter table t_part split partition SYS_P22473 at (TO_DATE(' 2001-07-10 12:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  2  into (partition morning, partition SYS_P22473);

 

Table altered.

 

SQL> select table_name, partition_name, partition_position part_pos

  2  ,interval,high_value

  3  from user_tab_partitions

  4  where table_name = 'T_PART'

  5  order by table_name, partition_position;

 

TABLE_NAME                     PARTITION_NAME                   PART_POS INT HIGH_VALUE

------------------------------ ------------------------------ ---------- --- --------------------------------------------------------------------------------

T_PART                         P_200105                                1 NO  TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         P_200106                                2 NO  TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22469                              3 NO  TO_DATE(' 2001-07-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         MYFAV                                   4 NO  TO_DATE(' 2001-07-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         MORNING                                 5 NO  TO_DATE(' 2001-07-10 12:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22473                              6 NO  TO_DATE(' 2001-07-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

T_PART                         SYS_P22472                              7 YES TO_DATE(' 2001-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

7 rows selected.

 The transition point where the interval partitions start moved to the high bound of partition SYS_P22473.

Renaming interval partition

You can rename interval partition name e.g. rename system generated partition name

Table             PARTITION_NAME            HIGH_VALUE                          

----------------- ------------------------- -------------------------------

...

T_PART            SYS_P5793                 TIMESTAMP' 2001-07-03 00:00:00'

T_PART            SYS_P5794                 TIMESTAMP' 2001-07-04 00:00:00'

..

SQL> ALTER TABLE T_PART rename partition SYS_P5793 to p2001_07_02;

 

Table altered.

 

                                                                                

Table             PARTITION_NAME            HIGH_VALUE                          

----------------- ------------------------- -------------------------------

...

T_PART            P2001_07_02               TIMESTAMP' 2001-07-03 00:00:00'

T_PART            SYS_P5794                 TIMESTAMP' 2001-07-04 00:00:00'

...

 

How to disable / turn off interval partitioning for a table?

You can disable interval partitioning with the following statement:  

    ALTER TABLE <table name> SET INTERVAL ();

This makes the table range partitioned with the existing partitions as ranged ones.

Please note turning off and then on interval partitioning will move the transition point where the interval partitions will start to the high bound of the highest existing partition. This is because turning interval partitioning off makes the interval partitions range, and then turning interval partition on will use the high bound of the highest range partition as the transition point.

  

 

 

Partition Count/Pruning

Note that the maximum number of partitions in a table is 1024K -1 = 1048575. With a particular interval set the physical interval partition is created when data comes for that partition, but the database reserves the interval partitions in between physically created partitions as well as upto the maximum number of partitions, since future values can go into any of these partitions.

Consider the following example.

CREATE TABLE t_num

(col1 number)

PARTITION BY RANGE (col1)

     INTERVAL(1)

(

      PARTITION p1 values less than (1)

);

 

insert into t_num values (1);

insert into t_num values (2);

insert into t_num values (100);

 At this point  dba_tab_partitions shows 4 partitions, 3 of those were created due to the 3 above inserts, 1 partition is the initial range partition created at create table time:

Table             PARTITION_NAME            HIGH_VALUE  

----------------- ------------------------- -----------

T_NUM             P1                        1           

T_NUM             SYS_P5913                 2           

T_NUM             SYS_P5914                 3           

T_NUM             SYS_P5915                 101        

 The value of the column PARTITION_COUNT in ALL|DBA|USER_part_table shows the theoretical max number of partitions:

TABLE_NAME           PARTITION_COUNT

-------------------- ---------------

T_NUM                        1048575

If you look into the execution plan of a query that cannot do partition pruning, you see PSTOP=<theoretical max number of partitions>:

SQL> set autotrace on

SQL> select * from t_num;

 

      COL1

----------

         1

         2

       100

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 122616543

 

---------------------------------------------------------------------------------------------

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |       |     7 |    91 |    35   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ALL|       |     7 |    91 |    35   (0)| 00:00:01 |     1 |1048575|

|   2 |   TABLE ACCESS FULL | T_NUM |     7 |    91 |    35   (0)| 00:00:01 |     1 |1048575|

---------------------------------------------------------------------------------------------

Now insert value 1048574 into the T_NUM table, this creates a partition with the high value 1048575.

SQL> insert into t_num values (1048574);

 

1 row created.

 

Table             PARTITION_NAME            HIGH_VALUE

----------------- ------------------------- ----------

T_NUM             P1                        1           

T_NUM             SYS_P5913                 2           

T_NUM             SYS_P5914                 3           

T_NUM             SYS_P5915                 101         

T_NUM             SYS_P5916                 1048575 

dba_tab_partitions shows only 5 physically created partitions as 4 rows are in the table, but due to interval(1) is set for the table, we cannot insert a larger value e.g. 1048575, because the corresponding partition would need to have high value 1048576, and this would exceed the theoretical number of partitions for the table:

insert into t_num values (1048575)

            *

ERROR at line 1:

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

 

See also 

Document 1447928.1 PARTITION_COUNT Shows Large Value 1048575 With Interval Partitioning

Document 754642.1 How the PSTART/PSTOP Numbers are computed when a Interval Partition is used

Document 1472941.1 Insert Fails With ORA-14300 On Partition Table

 

Can i specify NULL value for the partitioning key column?

No.

Null value would map to a partition with the highest possible value of the partition key as high bound, e.g. with pure range partitioned table it would go to the MAXVALUE partition [PARTITION pmax VALUES LESS THAN (MAXVALUE)].

Hence NULL value to the partitioning key of an interval partitioned table would result in 'ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions', because the partition count would exceed the maximum number of partitions a table can have: 1024K -1 = 1048575. See more details under 'Partition Count/Pruning' section above.

 

This restriction is documented under Restrictions on Interval Partitioning at the CREATE TABLE part of the SQL Reference documentation:

"You cannot specify NULL values for the partitioning key column."

 

Why the system generated interval partitions are missing from the DDL of the table when i retrieve it with the function DBMS_METADATA.GET_DDL?

Per Bug 10005550 this is expected behavior, because "DBMS_METADATA.GET_DDL" will provide the output as the object was created (manual/user DDL). It does not include new partitions created by the system during data manipulation.

If you want to display the system generated partitions as part of the CREATE TABLE DDL, then set the EXPORT parameter of the dbms_metadata to true

e.g.

exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);

SELECT DBMS_METADATA.GET_DDL('TABLE' ,'TEST_INTERVAL','SCOTT') FROM DUAL;

See Document 1491820.1 for more details.

 

Can interval partitioned table be a parent table for reference partitioning?

Starting from 12.1 yes. See Note 1519042.1 for more information.

 

The window below is a live discussion of this article (not a screenshot).  We encourage you to join the discussion by clicking the "Reply" link below for the entry you would like to provide feedback on.  If you have questions or implementation issues with the information in the article above, please share that below.

References

NOTE:1552239.1 - Placement Into Tablespaces of Interval Partitions Are Not Round Robin

NOTE:1295484.1 - Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later

NOTE:466352.1 - 11g Feature: Interval Partitioning Example

NOTE:805976.1 - 11g New Features:INTERVAL PARTITIONING

 

NOTE:1491820.1 - "DBMS_METADATA.GET_DDL" Does Not Show Newly Created Interval Partitions for Interval partitioned Tables & Indexes

NOTE:874374.1 - How to change the tablespace storage of the INTERVAL-PARTITIONED table

NOTE:1329950.1 - Ability to define Tablespace with INTERVAL PARTITION Based on the Value of the Partition Key

NOTE:754642.1 - How the PSTART/PSTOP Numbers are computed when a Interval Partition is used

NOTE:1285036.1 - How To Drop The Interval Partitions?

 

NOTE:1472941.1 - Insert Fails With ORA-14300 On Partition Table

NOTE:854332.1 - How To Introduce Interval Partitioning into a Range Partitioned Table

NOTE:757754.1 - Interval Partitioning By Week

NOTE:1447928.1 - PARTITION_COUNT Shows Large Value 1048575 With Interval Partitioning


 

© 著作权归作者所有

易野
粉丝 6
博文 182
码字总数 132885
作品 0
深圳
私信 提问
PostgreSQL从继承到分区(三)

三、Pg_partman 3.1 介绍 pg_partman是基于PostgreSQL分区开发的一个分区表管理工具,通过多个引入函数实现了对分区表的管理,相比手工创建分区表、触发器函数、触发器显得更加快捷方便,同时...

LIAN-SA
2014/03/05
1K
0
解读 Oracle 12c 的 12 个新特性

原文出处:askmaclean 在OOW 2012上Tom kyte介绍了Oracle新一代重量级数据库产品12c 的12个新特性, 目前Open World 2012的主要PDF都可以下载了,传送门在此:Search Content Catalog for Or...

红薯
2012/10/10
40.8K
22
Oracle间隔分区

一、间隔分区的特点 1、由range分区派生而来 2、以定长宽度创建分区(比如年、月、具体的数字(比如100、500等)) 3、分区字段必须是number或date类型 4、必须至少指定一个range分区(永久分区)...

MGwilliam
2014/04/02
0
0
细说Oracle数据库与操作系统存储管理二三事

作者介绍 杨建荣,【DBAplus社群】联合发起人。现就职于搜狐畅游,Oracle ACE-A、YEP成员,超7年数据库开发和运维经验,擅长电信数据业务、数据库迁移和性能调优。持Oracle 10G OCP,OCM,M...

杨建荣
2016/07/26
0
0
Oracle 分区表的新增、修改、删除、合并。普通表转分区表方法

一. 分区表理论知识 Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。 Oracle的分区表可以包...

sjzmlb
2015/12/23
392
0

没有更多内容

加载失败,请刷新页面

加载更多

Giraph源码分析(八)—— 统计每个SuperStep中参与计算的顶点数目

作者|白松 目的:科研中,需要分析在每次迭代过程中参与计算的顶点数目,来进一步优化系统。比如,在SSSP的compute()方法最后一行,都会把当前顶点voteToHalt,即变为InActive状态。所以每次...

数澜科技
今天
4
0
Xss过滤器(Java)

问题 最近旧的系统,遇到Xss安全问题。这个系统采用用的是spring mvc的maven工程。 解决 maven依赖配置 <properties><easapi.version>2.2.0.0</easapi.version></properties><dependenci......

亚林瓜子
今天
10
0
Navicat 快捷键

操作 结果 ctrl+q 打开查询窗口 ctrl+/ 注释sql语句 ctrl+shift +/ 解除注释 ctrl+r 运行查询窗口的sql语句 ctrl+shift+r 只运行选中的sql语句 F6 打开一个mysql命令行窗口 ctrl+l 删除一行 ...

低至一折起
今天
9
0
Set 和 Map

Set 1:基本概念 类数组对象, 内部元素唯一 let set = new Set([1, 2, 3, 2, 1]); console.log(set); // Set(3){ 1, 2, 3 } [...set]; // [1, 2, 3] 接收数组或迭代器对象 ...

凌兮洛
今天
4
0
PyTorch入门笔记一

张量 引入pytorch,生成一个随机的5x3张量 >>> from __future__ import print_function>>> import torch>>> x = torch.rand(5, 3)>>> print(x)tensor([[0.5555, 0.7301, 0.5655],......

仪山湖
今天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部