Oracle partitioning Solution

1. syntax for creating a partition:

Create table table_name

(......)

Partition by range (column_list)

(

PARTITION partition_name values less than (values_list) TABLESPACE tablespace_name,

......

)

Table_name: Table name;

Column_list: List of partition keywords (fields in the table;

Partition_name: partition name;

Values_list: corresponds to column_list, which is the upper limit of the partition;

Ii. Partition Maintenance

A) Expand the partition

A. Create A partition table space:

(For example, create tablespace BCMSPHIS_0603)

DATAFILE 'f: \ Oracle \ bcmsphis_0603.dbf' SIZE 1024 k autoextend on next 102400 K MAXSIZE UNLIMITED

Extent management local autoallocate;

B. Create a partition:

(Example) alter table BCPOnInPDataHisRecSht add partition BCPINPPART_0603 values less than (TO_DATE ('2017-10-01 ', 'yyyy-MM-DD '))

TABLESPACE BCPINPHIS_0603;

C. Modify the partition index tablespace pointing:

(Example) alter index XIE1BCPOnInPDataHisRecSht rebuild partition BCPINPPART_0603

TABLESPACE BCPINPHIS_0603;

Alter index XIE2BCPOnInPDataHisRecSht rebuild partition BCPINPPART_0603

TABLESPACE BCPINPHIS_0603;

Note: Of course, you can use the previously created tablespace instead of creating a new tablespace during the process of expanding a new partition. The upper limit of creating a new partition table must be greater than the upper limit of an existing partition, otherwise, a new partition table cannot be created. When a partition is successfully added to a table and a local index is defined for the table, a new partition is automatically created for each Local index, the partition index name is the same as the new partition table name, but the partition index tablespace points to the index tablespace. Therefore, if you want to put the partition table and the partition index in the same tablespace, You need to modify the tablespace of the partition index. You cannot explicitly add partitions to the local index, you can only add new partitions to a local index when adding partitions to a table;

The execution method is as follows: (Note: If you have linked the database before executing the SQL statement, please disconnect the database and re-link it ;)

B) partition data backup

 

C) delete a partition
PARTITION deletion Syntax: alter table drop partition partition_name

Example: alter table drop partition bctinppart_0502

This method deletes table partitions and data in the table, and the corresponding partitions of the Local index on the table are also deleted (that is, the partitions marked as unavailable and deleted ); you cannot delete a partition from a local index explicitly. You only need to delete a partition from the table. If you want to delete a table partition but keep its data, the partition must be merged into the adjacent partition.

D) partition data recovery

Restore data in a partition:

When restoring partition data, the recovered partition must exist. If the recovered partition does not exist, you must re-create the partition; otherwise, Data Recovery fails; when the upper limit of the restored partition is greater than the upper limit of the current partition, you can directly create the restored partition and modify the tablespace of the partition index. When the upper limit of the restored partition is less than the upper limit of the current partition, only partitions can be split to create and restore partitions;

E) Partition

Use the split partition sub-name of the alter table statement to SPLIT a PARTITION into two partitions. New partitions inherit attributes from old partitions; segments related to old partitions are discarded; this statement also splits the corresponding partitions in each local index of the table (even if they are marked as unavailable );

In addition to the TABLESPACE attribute, the physical attribute of the old partial index partition is used for the new index partition;

Example:

-- Partition

Alter table bctoninpdatahisrecsht split partition bctinppart _ 0702

At (to_date ('2017-05-01 ', 'yyyy-MM-DD '))

Into (partition bctinppart_070201 tablespace bctinppart_070201, partition bctinppart_070202 );

-- Modify partition index tablespace

Alter index XIE1 bctoninpdatahisrecsht rebuild partition bctinppart _ 070201

TABLESPACE bctinppart _ 070201;

Alter index XIE2 bctoninpdatahisrecsht rebuild partition bctinppart _ 070202

TABLESPACE bctinppart _ 070202;

Iii. Others:

Oracle returns an error if the record information exceeds the upper limit of the partition (ORA-14400: The inserted partition keyword is not mapped to any partition '))

Local and global indexes

Partial index: use LOCAL to create independent indexes for each partition. Because each partition has its own independent index, the partition index is partial for the table;

Global index: the global index contains the values of multiple partitions. The index value spans multiple partitions. When many transactions occur in the partition or the data values of all partitions need to be unique, generally, global indexes are used.

The local index also ensures uniqueness, but the global index performs the uniqueness check faster.