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

Partition has always been a technology that Oracle database is proud of. It is the existence of partition that makes it possible for Oracle to deal with massive data efficiently. In Oracle 11g, partition technology has been enhanced again in terms of ease of use and scalability. In the 10g version of Oracle, adjustments to partition tables, especially the addition of new partitions to RANGE partitions, need to be manually added regularly by DBA, or both are managed using stored procedures. Interval Partition in the 11G version no longer requires DBA to interfere with the addition of new partitions, and Oracle automatically does so, reducing the workload of DBA. Interval Partition is an extension of the Range partition.

There are also some limitations to using Interval Partition:

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

Interval partitioning is not supported for index-organized tables.

You cannot create a domain index on an interval-partitioned table.

Interval Partition can also create composite partitions:

Interval-range

Interval-hash

Interval-list

Create an Interval partition table:

Sys@ORCL > CREATE TABLE interval_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 PARTITION BY RANGE) 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-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.

The test data inserted within the specified partition range was inserted successfully.

Sys@ORCL > insert into interval_sales values (1 row created.sys@ORCL > commit;Commit complete.sys@ORCL > select * from interval_sales partition (p1) PROD_ID CUST_ID TIME_ID CHA PROMO_ID QUANTITY_SOLD AMOUNT_SOLD- 1 101 2008 -06-01 00:00:00 a 201 101 10

Insert test data that is not within the specified partition. The data is inserted successfully.

Sys@ORCL > insert into interval_sales values (2 row created.sys@ORCL > commit;Commit complete.sys@ORCL > select * date); 1 row created.sys@ORCL > commit;Commit complete.sys@ORCL > select * date PROD_ID CUST_ID TIME_ID CHA PROMO_ID QUANTITY_SOLD AMOUNT_SOLD- 1 101 2008 -06-01 00:00:00 a 201 101 10 2 101 2010-01-03 00:00:00 a 201 101 10

View all partitions of the table now

Sys@ORCL > col table_owner for a10sys@ORCL > col table_name for a15sys@ORCL > col partition_name for a20sys@ORCL > col high_value for a100sys@ORCL > set linesize 300sys@ORCL > select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_SALES' TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE -SYS INTERVAL_SALES P0 TO_DATE ('2008-01-01 00VOV 0000' 'SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES P1 TO_DATE ('2009-01-01 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES P2 TO_DATE (' 2009-07-01 00 SYYYY-MM-DD HH24:MI:SS', 'SYYYY-MM-DD HH24:MI:SS' 'NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES P3 TO_DATE (' 2010-01-01 00 TO_DATE, 'SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES SYS_P41 TO_DATE ('2010-02-01 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

You can see that there is an extra SYS_P41 partition in the INTERVAL_sales table. The HIGH_VALUE of the partition is 2010-02-01, and the partition is increased by one month.

Then insert the test data with a larger interval to see the changes.

Sys@ORCL > insert into interval_sales values (2 row created.sys@ORCL > commit;Commit complete.sys@ORCL > select * date); 1 row created.sys@ORCL > commit;Commit complete.sys@ORCL > select * date PROD_ID CUST_ID TIME_ID CHA PROMO_ID QUANTITY_SOLD AMOUNT_SOLD- 1 101 2008 -06-01 00:00:00 a 201 101 10 2 101 2010-01-03 00:00:00 a 201 101 10 2 101 2010-08-03 00:00:00 a 201 101 10sys@ORCL > select table_owner Table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_SALES' TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE -SYS INTERVAL_SALES P0 TO_DATE ('2008-01-01 00VOV 0000' 'SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES P1 TO_DATE ('2009-01-01 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES P2 TO_DATE (' 2009-07-01 00 SYYYY-MM-DD HH24:MI:SS', 'SYYYY-MM-DD HH24:MI:SS' 'NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES P3 TO_DATE (' 2010-01-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES SYS_P41 TO_DATE ('2010-02-01 00 NLS_CALENDAR=GREGORIAN',' SYYYY-MM-DD HH24:MI:SS' 'NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES SYS_P42 TO_DATE (' 2010-09-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN') 6 rows selected.sys@ORCL > insert into interval_sales values. 1 row created.sys@ORCL > commit;Commit complete.sys@ORCL > select * from interval_sales PROD_ID CUST_ID TIME_ID CHA PROMO_ID QUANTITY_SOLD AMOUNT_SOLD- 1 101 2008 -06-01 00:00:00 a 201 101 10 2 101 2010-01-03 00:00:00 a 201 101 10 2 101 2010-05-03 00:00:00 a 201 101 10 2 101 2010-08-03 00:00:00 a 201101 10sys@ORCL > select table_owner Table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_SALES' TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE -SYS INTERVAL_SALES P0 TO_DATE ('2008-01-01 00VOV 0000' 'SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES P1 TO_DATE ('2009-01-01 00 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES P2 TO_DATE (' 2009-07-01 00 SYYYY-MM-DD HH24:MI:SS', 'SYYYY-MM-DD HH24:MI:SS' 'NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES P3 TO_DATE (' 2010-01-01 00 SYYYY-MM-DD HH24:MI:SS', NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES SYS_P41 TO_DATE ('2010-02-01 00 NLS_CALENDAR=GREGORIAN',' SYYYY-MM-DD HH24:MI:SS' 'NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES SYS_P42 TO_DATE (' 2010-09-01 00 SYS INTERVAL_SALES SYS_P43 TO_DATE, 'SYYYY-MM-DD HH24:MI:SS',' NLS_CALENDAR=GREGORIAN') SYS INTERVAL_SALES SYS_P43 TO_DATE ('2010-06-01 00 SYS INTERVAL_SALES SYS_P43 TO_DATE,' SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 7 rows selected.

You can see that inserting 2010-08-03 data creates a 2010-09-01 partition, and then inserting 2010-05-03 data creates a 2010-06-01 partition, indicating that all automatically created partitions are controlled for the whole month. It is NUMTOYMINTERVAL (1, 'MONTH') that controls this interval.

NUMTOYMINTERVAL (1, 'YEAR') one year

NUMTOYMINTERVAL (1, 'MONTH') for one month

NUMTODSINTERVAL (1, 'DAY') one day

NUMTODSINTERVAL (1, 'HOUR') one hour

NUMTODSINTERVAL (1, 'MINUTE') one minute

NUMTODSINTERVAL (1, 'SECOND') one second

Using numeric values as partition keys, you can also use Interval Partition

Sys@ORCL > create table interval_num 2 (id number, 3 name varchar2 (20), 4 time_id date 5) 6 partition by range (id) 7 interval (20) 8 (partition p0 values less than (20)); Table created.sys@ORCL > insert into interval_num values 1 row created.sys@ORCL > insert into interval_num values (21 row created.sys@ORCL > commit;Commit complete.sys@ORCL > select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_NUM') TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE -SYS INTERVAL_NUM P0 20SYS INTERVAL_NUM SYS_P44 40

Considerations for using Interval Partition, automatically created partition names are automatically assigned, similar to SYS_P**, and can be modified if you think this name is not compliant.

Sys@ORCL > alter table interval_num rename partition sys_p44 to p1terTable altered.sys@ORCL > select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_NUM' TABLE_OWNE TABLE_NAME PARTITION_NAME HIGH_VALUE -SYS INTERVAL_NUM P0 20SYS INTERVAL_NUM P1 40

Convert non-Interval Partition to Partition, using alter table table_name set interval (...)

Sys@ORCL > create table interval_num 2 (id number, 3 name varchar2 (20), 4 time_id date 5) 6 partition by range (id) 7 (partition p0 values less than (20)); Table created.sys@ORCL > insert into interval_num (id) values (1) 1 row created.sys@ORCL > insert into interval_num (id) values (21); insert into interval_num (id) values (21) * ERROR at line 1:ORA-14400: inserted partition key does not map to any partitionsys@ORCL > alter table interval_num set interval (20); Table altered.sys@ORCL > insert into interval_num (id) values (21); 1 row created.sys@ORCL > select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_NUM' TABLE_OWNER TABLE_NAME PARTITION_ HIGH_VALUE -SYS INTERVAL_NUM P0 20SYS INTERVAL_NUM SYS_P45 40

Official document: http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm#BAJHFFBE

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.