For statement expressed by Oracle partition (top)

Specify a partition in addition to using the partition name, many times you can use a for statement.

Starting with the 11g, you can use the partition name as well as the for statement when you are working on the partition.

In 10g, the statement for the MERGE range partition is as follows:

Sql> SELECT * from V$version;

BANNER

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

Oracle database10genterpriseedition Release10.2.0.3.0-64bi

Pl/sql Release 10.2.0.3.0-production

CORE 10.2.0.3.0 Production

TNS for Solaris:version 10.2.0.3.0-production

Nlsrtl Version 10.2.0.3.0-production

sql> CREATE TABLE T_part_range

(ID number,

3 NAME VARCHAR2 (30),

4 create_date DATE)

5 PARTITION by RANGE (create_date)

6 (PARTITION P1 VALUES less THAN (to_date (' 2009-1 ', ' yyyy-mm ')),

7 PARTITION P2 VALUES less THAN (to_date (' 2009-4 ', ' yyyy-mm ')),

8 PARTITION P3 VALUES less THAN (to_date (' 2009-7 ', ' yyyy-mm '));

Table has been created.

sql> ALTER TABLE T_part_range

MERGE partitions P2, P3

3 into PARTITION P3;

The table has changed.

In 11g, in addition to using a partition name, you can use a for statement instead, such as:

Sql> SELECT * from V$version;

BANNER

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

Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

sql> CREATE TABLE T_part_range

(ID number,

3 NAME VARCHAR2 (30),

4 create_date DATE)

5 PARTITION by RANGE (create_date)

6 (PARTITION P1 VALUES less THAN (to_date (' 2009-1 ', ' yyyy-mm ')),

7 PARTITION P2 VALUES less THAN (to_date (' 2009-4 ', ' yyyy-mm ')),

8 PARTITION P3 VALUES less THAN (to_date (' 2009-7 ', ' yyyy-mm '));

Table has been created.

sql> ALTER TABLE T_part_range

MERGE partitions

3 for (to_date (' 2009-01 ', ' yyyy-mm ')),

4 for (to_date (' 2009-04 ', ' yyyy-mm '))

5 into PARTITION P3;

The table has changed.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

The advantages of this syntax are not obvious to the scope partition, but to the interval partition is very meaningful. Because the partition name of the interval partition is system-generated, the user is most intuitive to the interval partition is the scope of the data in the partition, according to the definition of the partition and interval settings can easily determine the scope of the partition and its data, However, the name of the partition must be passed through the data dictionary to be queried.

A simple example of a interval partition:

sql> CREATE TABLE T_part_inter

(ID number,

3 NAME VARCHAR2 (30),

4 create_date DATE)

5 PARTITION by RANGE (create_date)

6 INTERVAL (INTERVAL ' 3 ' MONTH)

7 (PARTITION P1 VALUES less THAN (to_date (' 2009-1 ', ' yyyy-mm ')),

8 PARTITION P2 VALUES less THAN (to_date (' 2009-4 ', ' yyyy-mm '));

Table has been created.

Sql> INSERT into T_part_inter

SELECT rownum, object_name, Sysdate-rownum * 10

3 from User_objects;

9 lines have been created.

Sql> COMMIT;

Submit completed.

sql> ALTER TABLE T_part_inter

MERGE partitions

3 for (to_date (' 2009-10 ', ' yyyy-mm ')),

4 for (to_date (' 2010-1 ', ' yyyy-mm '));

The table has changed.

Author: 51cto Blog Oracle Little Bastard