Shulou(Shulou.com)06/01 Report--

Oracle 11g adds new partition, virtual column partition, and reference partition. For details, please see official documents:

Creating Partitions

1. partition

Interval partitioning is an extension of range partitioning. Before introducing spaced partitions, DBAs needed to explicitly define a range of values for each partition, and as partition values grew, the number of available partitions gradually decreased until there were no more available partitions. Interval partition is to solve the problem that the partition cannot be automatically created when the data inserted into the table exceeds all the range partitions. At least one range partition must be created, and the range partition's key determines the range partition's upper limit, beyond which the database server automatically creates partitions at a specific interval.

Mainly used to predictably add small range partitions or fixed-time type partitions.

The restrictions are as follows:

1)Only one partition key column can be specified, and the key column must be of type NUMBER or DATE.

2)Index tables do not support spaced partitioning.

3)Domain indexes cannot be created for interval partitions

4)You cannot specify a specific name for a partition. It is automatically generated by the system in the format SYS_Pnnn, where n is a number.

Date Type Test:

Specify NUMTOYMINTERVAL (n,day) when creating time type interval partitions| month| The year) clause determines the conditions for partition expansion, n is a number, and specifies that partitions are added in n days/months/years.

CREATE TABLE SH.SALES_INTERVAL PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3,tbs4)(PARTITION P1 values less than (TO_DATE('1-1-2002','dd-mm-yyyy')),PARTITION P2 values less than (TO_DATE('1-1-2003','dd-mm-yyyy')),PARTITION P3 values less than (TO_DATE('1-1-2004','dd-mm-yyyy')))ASSELECT * FROM SH.SALESWHERE TIME_ID

< TO_DATE('1-1-2004','dd-mm-yyyy'); select partition_name from user_tab_partitions where table_name='SALES_INTERVAL';PARTITION_NAME---------------------------P1P2P3insert into sh.SALES_INTERVAL values(101001,4,to_date('2005-1-10','yyyy-mm-dd'),10,124,100,200);insert into sh.SALES_INTERVAL values(101002,5,to_date('2006-1-14','yyyy-mm-dd'),11,125,100,300);select partition_name from user_tab_partitions where table_name='SALES_INTERVAL';PARTITION_NAME---------------------------P1P2P3SYS_P121SYS_P122 Number类型测试： create table sh.test_interval(id number,name varchar2(20))PARTITION BY RANGE (id)INTERVAL (100)(PARTITION P001 values less than(500) ,PARTITION P002 values less than(1000) );insert into sh.test_interval select rownum,'A'||rownum from dual connect by level select table_name,partition_name from user_tab_partitions where table_name like '%ORDER%' order by 1,2;TABLE_NAME PARTITION_NAME------------------------------ ------------------------------ORDERS Q1_2005ORDERS Q1_2006ORDERS Q2_2005ORDERS Q3_2005ORDERS Q4_2005ORDER_ITEMS Q1_2005ORDER_ITEMS Q1_2006ORDER_ITEMS Q2_2005ORDER_ITEMS Q3_2005ORDER_ITEMS Q4_2005 子表添加分区 SH@PROD3>

alter table ORDER_ITEMS add partition Q2_2006 values less than (TO_DATE('01-JUL-2006','DD-MON-YYYY'));alter table ORDER_ITEMS add partition Q2_2006 values less than (TO_DATE('01-JUL-2006','DD-MON-YYYY')) *ERROR at line 1:ORA-14650: operation not supported for reference-partitioned tables

Other notes:

If no tablespaces are explicitly specified, partitions referencing partitioned tables are saved in the same location as the corresponding partitions of the parent table. As with other partition tables, you can specify object-level default attributes and partition descriptors that override object-level defaults. Foreign key constraints that reference partitioned tables cannot be disabled. Adding or deleting partitions that reference partition tables is not allowed. However, partition maintenance operations performed on parent tables automatically cascade to child tables.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.