Partition Operation Type

I. Partition definition:

A partitioned table divides data from a large table into many small subsets, which are called partitions.

Ii. Advantages of partitioning:

1. Enhanced availability: If a partition in the table cannot be used due to system failure, the remaining good partitions in the table can still be used;

2. Reduce the shutdown time: if a system failure only affects some partitions in the table, only these partitions need to be repaired, so it takes less time to repair than the entire large table;

3. Easy Maintenance: If you need to recreate a table, it is much easier to manage each partition independently than to manage a single large table;

4. Balanced I/O: You can allocate different partitions of a table to different disks to balance I/O and improve performance;

5. Improved performance: Query, add, modify, and other operations on large tables can be divided into different partitions of the table for parallel execution, which can make the operation faster;

6. partitions are transparent to users, and the end user cannot feel the existence of partitions.

Iii. partition management: many operations on a partition will lead to index failure and re-indexing is required. However, you can avoid using update indexes.

1. Partition tables are divided into three types: range, list, and hash. Their creation statements are as follows:

-- Range Partition

Create table p_range

(Sale_date date not null)

Partition by range (sale_date)

(PARTITION p1 values less than (TO_DATE ('1970-04-01 ', 'yyyy-MM-DD') TABLESPACE system,

PARTITION p2 values less than (TO_DATE ('2017-07-01 ', 'yyyy-MM-DD') TABLESPACE system,

PARTITION pmax values less than (maxvalue) TABLESPACE system );

-- List partitions

Create table p_list

(Sale_date varchar2 (10) not null) partition by list (sale_date)

(PARTITION p1 VALUES ('20140901') TABLESPACE system,

PARTITION p2 VALUES ('20140901') TABLESPACE system,

Partition pdefault values (default) TABLESPACE system );

-- Hash Partition

Create table p_hash

(Sale_date date not null) partition by hash (sale_date)

(PARTITION p1 TABLESPACE system,

PARTITION p2 TABLESPACE system,

PARTITION p3 TABLESPACE system );

-- Composite Partition

Create table p_box (I NUMBER, j NUMBER)

Partition by range (j)

Subpartition by hash (I)

(PARTITION p1 values less than (10) SUBPARTITION t2_pls1 SUBPARTITION t2_pls2,

PARTITION p2 values less than (20) SUBPARTITION t2_p2s1 SUBPARTITION t2_p2s2 ));

From the example above, we have introduced a composite index, and the composite index in ORACLE10G is limited to the composite partitions in range-hash and range-list, but in 11g, ORACLE edge has added four new combinations to make full use of composite indexes: range-range, list-range, list-hash, and list-list.

2. How to ADD a partition ):

If a list partition has a default value or a range partition has a maxvalue, The add partition operation cannot be performed. The value of add partition must be greater than that of all partitions ..

Alter table p_list add partition p_3 values ('20140901 ');

3. TRUNCATE ):

Alter table p_list truncate partition p_3

4. DROP)

Alter table p_list drop partition p_3

Delete sub-partitions; alter table p_list drop subpartition xxx;

5. split)

It is usually used to split the MAXVALUE/DEFAULT partition.

Alter table p_range split partition pmax at (to_date ('1970-11-13 ', 'yyyy-mm-dd') into (partition p_3, partition p_max );

Alter table p_list split partition pdefault values ('20140901') into (partition p_3, partition p_defalut );

In this case, the '123' value in pmax or pdefault is put into P_3, and other data is put into p_max or p_defalut.

6. exchange partition (Introduction)

The speed is very fast. It can be the exchange of partitions and non-partition tables, the exchange of sub-partitions and non-partition tables, and the exchange of combined partitions and partition tables.

Create table p_u_list (sale_date1 varchar2 (10) not null)

Insert into p_u_list values ('20140901 ');

Alter table p_list exchange partition p1 with table p_u_list with validation;

Insert into p_u_list values ('20140901 ');

Alter table p_list exchange partition p1 with table p_u_list with validation; -- in this case, an error occurs because 20121115 does not belong to partition P1. In addition, The with validation check is performed. If you want to switch successfully, without validation must be added. If you specify with validation (default), the exchanged data will be checked legally to see if it meets the Partition Rules, without validation ignores Legal checks (for example, records with ID = 12 can be exchanged to the partitions with id values less than (10). However, if the table has a primary key or unique constraint, the specified without validation will be ignored.

7. merge partitions (merge and coalesce)

Coalesce is only applicable to hash subpartitions of hash partitions and composite partitions. It automatically shrinks the current table partition. For example, a table currently has five hash partitions. After coalesce is executed, it becomes four, once again, it becomes three... until one. Merge is not applicable to hash partitions. If the list partition has default or the range partition has maxvalue, merge operations cannot be performed.

Alter table p_hash coalesce partition;

Alter table p_list merge partitions p1, p2 into partition P0;

8 rename a partition)

Alter table xxx rename partition/subpartition p1 to p1_new;

9. move)

Change the tablespace of a partition.

Alter table p_list move partition p1 tablespace sysaux;

10. EXPORT partition:

Exp sales/sales_password tables = sales: sales%_q1 rows = Y file = sales%_q1.dmp

11. IMPORT partition:

Imp sales/sales_password FILE = sales%_q1.dmp TABLES = (sales: sales%_q1) IGNORE = y

12. modify default attributes)

Alter table xxx modify default attributes...

Alter table xxx modify default attributes for partition p1...

It only affects the partitions that will be added later. It applies to all partitions, where hash partitions can only modify the tablespace attributes. For example:

Alter table xxx modify default attributes tablespace users;

13. Modify the subpartition template (set subpartition template)

Alter table xxx set subpartition template (...);

Only subsequent subpartitions are affected. The attributes of the current subpartition are not changed. For example:

Alter table xxx set subpartition template (partition p1 tablespace tbs_1, Partition p2 tablespace tbs_2 );

To cancel the subpartition template:

Alter table xxx set subpartition template ();