Introduction to the Add values and drop values statements for Oracle list partitions

The partition key value of the list partition table is a discrete value, so it has DDL maintenance statements that are unique to other partitions, ADD values, and Dorp values.

Add values and drop values increase or decrease the corresponding partition key values for the specified partition. Unlike add partition and drop partition, add partition and drop partition are partitioning operations, adding partitions and deleting partitions separately, and add values and drop values operate on existing partitions.

When different from other DDL, even if it does not involve data modification add values or drop values operations can still be slow, see an example:

sql> CREATE TABLE t_part_list

2 (

3 OWNER VARCHAR2 (30),

4 NAME VARCHAR2 (30),

5 Tablespace_name VARCHAR2 (30),

6 TYPE VARCHAR2 (18)

7)

8 PARTITION by LIST (Tablespace_name)

9 (

PARTITION P1 VALUES (' SYSTEM '),

One PARTITION P2 VALUES (' yangtk '),

PARTITION P3 VALUES (DEFAULT)

13);

Table has been created.

Sql> SELECT COUNT (*)

2 from Dba_segments;

COUNT (*)

----------

5627

Sql> SELECT COUNT (*)

2 from Dba_sequences;

COUNT (*)

----------

224

Sql> INSERT into T_part_list

2 SELECT A.owner, Segment_name, Tablespace_name, Segment_type

3 from Dba_segments A, dba_sequences;

1260448 lines have been created.

Sql> COMMIT;

Submit completed.

A table with a large amount of data is built, following the add values statement for the partition partition P1:

Sql> SET TIMING on

sql> ALTER TABLE t_part_list

2 MODIFY PARTITION P1

3 ADD VALUES (' JUST TEST ');

The table has changed.

Time used: 00:00:05.33

sql> ALTER TABLE t_part_list

2 MODIFY PARTITION P1

3 DROP VALUES (' JUST TEST ');

The table has changed.

Time used: 00:00:30.08

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

There is no modification to any data, just modifying the data dictionary, which causes add values to take 5 seconds, and drop values in half a minute. This is because when the add values and drop values are executed, Oracle is going to query for the partition or operation of the default partition, check for the presence of new or deleted columns, so the larger the corresponding partition, the longer the DDL will take.

The simplest method of optimization is to index the partitioning column:

sql> CREATE INDEX Ind_t_list_tbsspc_name

2 on T_part_list (Tablespace_name);

The index has been created.

Time used: 00:00:05.24

sql> ALTER TABLE t_part_list

2 MODIFY PARTITION P1

3 ADD VALUES (' JUST TEST ');

The table has changed.

Time used: 00:00:00.04

sql> ALTER TABLE t_part_list

2 MODIFY PARTITION P1

3 DROP VALUES (' JUST TEST ');

The table has changed.

Time used: 00:00:00.02

As you can see, the ADD values and drop values statements can be completed within a second after the index is indexed.

Author: 51cto Blog Oracle Little Bastard