当前位置:首页 > 开发 > 数据库 > 正文

Oracle 11g Interval Partition(原创)

发表于: 2013-11-15   作者:czmmiao   来源:转载   浏览次数:
摘要: Interval PartitioningInterval partitioning is an extension of the familiar range partitioning scheme. Range partitioning is ideal for partitioning historical data. You use range partitioning to organi

Interval Partitioning
Interval partitioning is an extension of the familiar range partitioning scheme. Range partitioning is ideal for partitioning historical data. You use range partitioning to organize data by time intervals on a column of type DATE. The boundaries you set for the range partitions determine how the database orders the partitions in the table or indexes. Let’s first briefly review range partitioning, as interval partitioning is an extension of the range partitioning scheme.
In other words, the database will start off by creating one or more range partitions. Once the data reaches a value that’s beyond the transition point, the database will start creating interval partitions. 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 following code shows an example of a table using interval partitioning.

SQL> CREATE TABLE interval_tab (
       id           NUMBER,
       code         VARCHAR2(10),
       description  VARCHAR2(50),
       created_date DATE
      )
     PARTITION BY RANGE (created_date)
     INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
     (
       PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
     );    
Querying the USER_TAB_PARTITIONS view shows there is only a single partition.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');11g
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN high_value FORMAT A40
SQL> SELECT table_name, partition_name, high_value, num_rows
       FROM user_tab_partitions
      ORDER BY table_name, partition_name;
     TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
     -------------------- -------------------- ---------------------------------------- ----------
     INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          0
                                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
     1 row selected.
Provided we insert data with a created_date value less than '01-NOV-2007' the data will be placed in the existing partition and no new partitions will be created.
SQL> INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
SQL> INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SQL> SELECT table_name, partition_name, high_value, num_rows
       FROM user_tab_partitions
      ORDER BY table_name, partition_name;
    TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
    -------------------- -------------------- ---------------------------------------- ----------
    INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2
                                              M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
    1 row selected.
If we add data beyond the range of the existing partition, a new partition is created.
SQL> INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
SQL> INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SQL> SELECT table_name, partition_name, high_value, num_rows
     FROM   user_tab_partitions
     ORDER BY table_name, partition_name;
     TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
     -------------------- -------------------- ---------------------------------------- ----------
     INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2
     M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
     INTERVAL_TAB         SYS_P44              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2
     M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
     11g
     2 rows selected.
If we insert data for two months after the current largest transition point, only the required partition is created, not the intervening partitions.
SQL> INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));
SQL> INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SQL> SELECT table_name, partition_name, high_value, num_rows
     FROM   user_tab_partitions
     ORDER BY table_name, partition_name;
     TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
     -------------------- -------------------- ---------------------------------------- ----------
     INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2
     M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
     INTERVAL_TAB         SYS_P44              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2
     M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
     INTERVAL_TAB         SYS_P45              TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M          2
     M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
     3 rows selected.
Notice that a partition to hold January 2008 data has not been created. If we insert data for this time period, the missing partition is created.
SQL> INSERT INTO interval_tab VALUES (7, 'SEVEN', 'Seven', TO_DATE('01-DEC-2007', 'DD-MON-YYYY'));
SQL> INSERT INTO interval_tab VALUES (8, 'EIGHT', 'Eight', TO_DATE('31-DEC-2007', 'DD-MON-YYYY'));
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SQL> SELECT table_name, partition_name, high_value, num_rows
     FROM   user_tab_partitions
     ORDER BY table_name, partition_name;
     TABLE_NAME           PARTITION_NAME       HIGH_VALUE                                 NUM_ROWS
     -------------------- -------------------- ---------------------------------------- ----------
     INTERVAL_TAB         PART_01              TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M          2
     M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
     INTERVAL_TAB         SYS_P44              TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M          2
     M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
     INTERVAL_TAB         SYS_P45              TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M          2
     M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
     INTERVAL_TAB         SYS_P46              TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M          2
     M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA    
4 rows selected.

Moving the Transition Point
You can use Oracle’s partition merging capability to merge any two adjacent interval partitions. You can even merge the very first interval partition with the highest range partition. When you merge any two adjacent partitions, the new partition you create will have as its upper bound the higher of the upper bounds of the merged partitions.
When you merge two adjacent interval partitions, the transition point automatically moves to the higher of the two upper bounds. Remember that the transition point is defined as the high value of the range partitions. Thus, whenever you merge any two interval partitions, the range partition component of the interval-partitioned table will move up to the higher bound of the two merged partitions. If you have any interval partitions with boundaries below this new merged partition, the database will automatically convert them into range partitions.

Let me show you a wrong merge statement firstly,

alter table INTERVAL_TAB merge partitions for(to_date('01-NOV-2007','dd-MON-yyyy')), for(TO_DATE('01-JAN-2008','dd-MON-yyyy'));

SQL ERROR: ORA-14274: partitions being merged are not adjacent
14274. 00000 -  "partitions being merged are not adjacent"
*Cause:    User attempt to merge two partitions that are not adjacent
           to each other which is illegal
*Action:   Specify two partitions that are adjacent

The partition need to be merged must be adjacent,otherwise ,it'll present ORA-14274 error

alter table INTERVAL_TAB
merge partitions for(to_date('01-DEC-2007','dd-MON-yyyy'))
, for(TO_DATE('01-JAN-2008','dd-MON-yyyy'));
table INTERVAL_TAB ALTERED。

SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, INTERVAL
  FROM user_tab_partitions
order by TABLE_NAME, PARTITION_NAME;

TABLE_NAME    PARTITION_NAME    HIGH_VALUE    INTERVAL

------------------      -------------------------      ------------------     ---------------
INTERVAL_TAB    PART_01    TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    NO
INTERVAL_TAB    SYS_P26    TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    NO
INTERVAL_TAB    SYS_P30    TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')    NO

The alter table . . . merge statement will do the following:

  1. Move the transition point for the table to Feburary 01, 2008, which is the non-inclusive high value of the two merged partitions.
  2. Create a new range partition, combining the values of the partition key in the two merged partitions as the value for its own partition key. The partition key for this new range partition is Feburary 01, 2008.

PARTITOIN SYNTAX

Note that in Oracle Database 11g, the partition syntax is extended so that you can use it to refer to a partition without specifying a name for the partition. If you use a value that represents a possible value for the partition, the database will know which partition the value belongs to. You can use this new syntax for all partition management operations such as a drop, truncate, merge, and split partition. You can use the syntax not just for the new interval partitioning scheme, but also to existing range, list, and hash partitioning schemas. Here is an example of the new syntax:
SQL> select * from interval_tab partition for (to_date('19-NOV-2007','dd-MON-yyyy'));
You use the new for clause to specify a value with which to directly reference a partition, instead of providing a partition name. In cases such as interval partitioning, where the database provides system-generated partitions, you may not even know the name of the partition you’re interested in. The new syntax of addressing a partition indirectly by the values contained in it rather than by its name is of great help in cases like this.
When to Use Interval Partitioning
Because interval partitioning is an extension of range partitioning, if range
partitioning is ideal for a situation, interval partitioning is ideal as well. Use interval
partitioning in the following situations:
When your SQL statements that access a large table use a range predicate on a partitioning column such as ORDER_DATE, using interval partitioning
helps you reap the benefits of partition pruning.
If you constantly load new data and purge old data to maintain a rolling window of data, interval partitioning is ideal because it lets the database
automatically create new interval partitions as the data is inserted.
If you want to cut up a large table into smaller logical pieces to complete administrative operations in short maintenance windows, once again,
interval partitioning is the way to go.

Restriction of Interval Parititon
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.
Extended Composite Partitioning
In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:
Range-Hash (available since 8i)
Range-List (available since 9i)
Range-Range
List-Range
List-Hash
List-List
Interval partitioning, described below, is a form of range partitioning, so the previous list also implies the following combinations:
Interval-Hash
Interval-List
Interval-Range

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

       http://www.oracle-base.com/articles/11g/partitioning-enhancements-11gr1.php

本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

Oracle 11g Interval Partition(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
Overview In Oracle Database 10g, Oracle introduced the new Transparent Data Encryption (TDE)
Automatic Diagnostic Repository The automatic diagnostic repository tracks all problems and i
1.概述 Parttion by 关键字是Oracle中分析性函数的一部分,它和聚合函数不同的地方在于它能够返回一
原文地址:http://www.2cto.com/database/201208/150620.html 一、Oracle 下载 注意Oracle分成两个文
oracle 11g 装好以后好久木有动了,今天找健健来一起研究,真是好。 先把oracle服务打开,这家伙太
一、Oracle 下载 注意Oracle分成两个文件,下载完后,将两个文件解压到同一目录下即可。 路径名称中
Given a set of non-overlapping intervals, insert a new interval into the intervals (merge if
Overview of Table Clusters A table cluster is a group of tables that share common columns and
一、硬件检测 1、内存检测 oracle11g要求最低1GB内存 命令: grep MemTotal /proc/meninfo 2、交换
数据库和client在不同的机器之上。 在安装之前,在安装Oracle数据库的server上导航到以下的文件夹。
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号