Oracle Database Partitioning Technology

First, the syntax for establishing partitions is as follows:

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: A list of partition keywords (fields in the table);

Partition_name: partition name;

Values_list: Corresponding to the column_list, the upper bound value of the partition;

Second, the Division maintenance

A) Extended partition

A, set up the partition table space:

(for example, create Tablespace bcmsphis_0603

DataFile ' F:\Oracle\BCMSPHIS_0603.DBF ' SIZE 1024K autoextend on NEXT 102400K MAXSIZE Unlimited

EXTENT MANAGEMENT local autoallocate;

B, establish the partition:

(example) ALTER TABLE bcponinpdatahisrecsht ADD PARTITION bcpinppart_0603 VALUES less THAN (to_date (' 2006-10-01 ', ' yyyy-mm-dd ') )

Tablespace bcpinphis_0603;

C, modify the partition index table space point to:

(example) ALTER INDEX Xie1bcponinpdatahisrecsht REBUILD PARTITION bcpinppart_0603

Tablespace bcpinphis_0603;

ALTER INDEX Xie2bcponinpdatahisrecsht REBUILD PARTITION bcpinppart_0603

Tablespace bcpinphis_0603;

Note: Of course, the expansion of the new partition process can also not create a new table space, you can create a new partitioned table by using a table space that you originally created, or you must create a new partition table with a maximum value that is greater than the upper limit of the existing partition; If the table has a local index defined when it is successfully added A new partition is also automatically created for each local index, with the same partition index name as the newly created partition table name. However, the table space of the partitioned index points to the table space of the index, so if you want to place the partitioned table and the partitioned index in the same table space, you need to modify the table space of the partitioned index, and you cannot explicitly add partitions to the local index. New partitions are automatically added to the local index only when the partition is added to the table;

Execution is as follows: ( Note: If you have linked the database before executing the SQL, please disconnect the database;)

b Partition Data Backup

c) partition deletion

Partition deletion syntax: ALTER TABLE drop PARTITION partition_name

Example: ALTER TABLE DROP PARTITION bctinppart_0502

This method deletes the table partition and the data in it, and the corresponding partition of the local index on the table is also deleted (that is, the indexed partition is marked as unavailable, they are also deleted); You cannot explicitly remove partitions from a local index, except to delete a local index partition when you delete a partition from a table; If you want to delete a table partition, but keep its data , you must merge the partition into a neighboring partition.

d) Partitioned Data recovery

Restore data for one partition:

The restored partition must exist when the partition data is restored, and the partition must be recreated if the recovery partition does not exist, or the recovery data fails; When the upper limit of the recovery partition created is greater than the maximum upper limit of the current partition, creating the recovery partition directly and then modifying the table space of the partitioned index ; When the upper-bound value for the recovery partition is created is less than the maximum value of the current partition, partitions can only be partitioned to create a recovery partition;

e) Partitioning partitions

You can split a partition into two partitions by using the split partition child name of the ALTER TABLE statement. The new partition inherits the property from the old partition, and the old partition-related segments are discarded, and the statement splits the corresponding partitions in each local index on the table (even if they are marked as unavailable);

In addition to the Tablespace property, the physical properties of the old local index partition are used for the new index partition;

Cases:

--Split partition

ALTER TABLE Bctoninpdatahisrecsht split partition Bctinppart _0702

At (to_date (' 2007-05-01 ', ' yyyy-mm-dd '))

Into (partition bctinppart_070201 tablespace bctinppart_070201,partition bctinppart_070202);

--Modifying the partitioned Index table space

ALTER INDEX XIE1 bctoninpdatahisrecsht REBUILD PARTITION bctinppart _070201

Tablespace Bctinppart _070201;

ALTER INDEX XIE2 bctoninpdatahisrecsht REBUILD PARTITION bctinppart _070202

Tablespace Bctinppart _070202;

Third, Other:

Oracle Error (ORA-14400: Inserted partition keyword does not map to any partition) if the recorded information exceeds the partition's upper bound value

Local index and global index

Local index: Separate indexes are created for each partition by using local, because each partition has its own independent index, so the partitioned index is partial to the table;

Global index: The global index contains values for multiple partitions, the value of the index spans multiple partitions, and when there are many transactions occurring in the partition or need to ensure uniqueness of all partition data values, the global index is typically used

Local indexes also ensure uniqueness, but global indexing performs uniqueness checking faster.