Oracle Partition Table

When the partition

1. Tables larger than 2GB
2. With 10 million records above, the more data that is contained in the table, the slower the SQL operation will be executed.
3. A table that will contain a large amount of data.
4. A table that can be used to facilitate parallel operations after breaking the split.
5. Contains a table that requires periodic archiving of logs or deletions

Advantages of partitioned Tables

1. Reduce the likelihood of damage due to the spread of data across regions

2. Individual partitions can be backed up and restored

3. You can map partitions to different physical disks to scatter I/O

4. Improved manageability, usability

Partitioning table Policy classifications

Range--Create partitions based on date, number, or character
List--Create partitions based on list values
Hash-Create partitions evenly without obvious partition keys
Combination--Use multiple partitioning methods
Interval--Expands the range partition by automatically assigning new partitions when the new partition key value exceeds the existing maximum range
Reference--Create a partition from a parent table column as a child table
Virtual column Partitioning--creating partitions on virtual columns
System to create partitions based on the application that inserted the data

Common types of partitions are: range, list, and hash

List of top ten query data volumes

Range Partitioning

--Create a statement of a plain table person1 (ID int primary key, name VARCHAR2 (), birth date);--the data will be insert into Perso in the same space of the same tablespace N1 VALUES (1, ' SSS ', sysdate); --Create a partition table--here is the partition according to the birthday--the basic format of the statement is to add the partition by Statement block CREATE TABLE Person2 (name Varchar2 (), birth date) par on the normal build statement Tition by Range (birth) (partition P1 values less than (to_date (' 19950101 ', ' YYYYMMDD '),--"values less than" partitio                         n P2 values less than (to_date (' 20000101 ', ' YYYYMMDD '), partition P3 values less than (MaxValue)  --the default partition);--insert, the data will enter different partitions according to the situation of the INSERT into Person2 values (' Zhang San ', To_date (' 19940707 ')); INSERT into person2 values (' John Doe ', To_date (' 19980707 ')); INSERT into person2 values (' Harry ', To_date (' 20040707 '));--Query all data in the table select * from person2;--query on a specific partition number  According to select * FROM Person2 partition (p3); --You can specify different table spaces for different partitions--partitions without a specified tablespace, using the user's default tablespace--so that data within a table can exist in different table spaces, which can be stored in different data files, on different disks--so the partition table can enhance the security of the data create Table Person3 (name varchar2), birth date) partition by range (birth) (partition p1 values less than (to_date (' 19950101 ', ' YYYYMMDD ')) tablespace system, partition P2 values less than (to_date (' 20000101 ',  ' YYYYMMDD ') tablespace Sysaux, partition P3 values less than (MaxValue) tablespace users); --can be scoped on other types--or range partitions on multiple fields create table Person4 (name Varchar2 (), birth date, score number) partition by range (b Irth, score) (partition P1 values less than (to_date (' 19900101 ', ' YYYYMMDD '), partition P2 values less than (to_date (' 19900101 ', ' YYYYMMDD '), partition P3 values less than (to_date (' 19990101 ', ' YYYYMMDD '), partition P4 values Le SS Than (To_date (' 19990101 ', ' YYYYMMDD '), and partition P5 values less than (MaxValue, MaxValue));

  List Partitioning

--If this is a birthday field, the data is contiguous and should be divided into partitions create table person (name varchar2, birth date) partition by range (birth) (partition P1 Values less than (to_date (' 19900101 ', ' YYYYMMDD ')) tablespace the users, partition P2 values less than (MaxValue)); Insert int  o Person values (' AAA ', to_date (' 19871212 ', ' YYYYMMDD ')); select * FROM person partition (P1); /* Where birth between 1987 and 1990 where Sex in (' Male ', ' female ') */-but like the gender, ethnicity and other fields, more suitable for the use of the list partition--The following example, the use of gender as a partition field, male one area, female a district C reate table Person2 (name varchar2), sex varchar (ten)) partition by list (sex) (Partition P1 values (' Male '), partitio n P2 values (' female '); insert into person2 values (' aaa ', ' Male '), insert into person2 values (' BBB ', ' female '); insert into Person2 val  UEs (' CCC ', ' unknown '); --Error SELECT * FROM Person2 partition (P2);    --The default partition is written as CREATE table Person3 (name varchar2, sex varchar) partition by list (sex) (Partition P1 values (' Male '), Partition P2 values (' female '), partition P3 values (default), insert into Person3 values (' CCC ', ' unknown '); SelecT * from Person3 partition (p3); --You can specify tablespace for each partition CREATE table Person3 (name varchar2, sex varchar) partition by list (partition P1 values (' Male ') tablespace users, partition P2 values (' female ') tablespace system, Partition P3 values (default));

Hash partition

--Hash partitioning--is used primarily in discrete, poorly categorized data, such as product names--creating statements that allow Oracle to automatically calculate data using a hashing algorithm--Create a statement, very simple. CREATE table Person4 (name varchar2), Sex V ARCHAR2 () partition by hash (name) (  partition P1,  partition p2 tablespace users); INSERT into person4 values (' A AA ', ' Male '); insert into person4 values (' collections ', ' Men '); select * from Person4 partition (P1); The above statement can be further simplified to: CREATE TABLE Person5 (name varchar2 (), Sex varchar2 (Ten)) partition by hash (name) partitions 5; --Specify tablespace for each partition CREATE table Person6 (name varchar2), Sex varchar2 (Ten)) partition by hash (name) Partitions 3 store in (user S, System, sysaux);

Range and hash combinations

--The data is divided into three regions according to birthdays, and then divided into three zones within each partition, followed by a hashing algorithm--so that the data within each partition is as small as possible and the partition is balanced CREATE TABLE person7 (name varchar2), birth Date) partition by range (birth) subpartition by hash (name) subpartitions 3 (  partition P1 values less than (to_date (' 19 900101 ', ' YYYYMMDD '),  partition P2 values less than (to_date (' 20000101 ', ' YYYYMMDD '),  partition P3 values Less Than (MaxValue));

Non-partitioned table to partition table

12c previously non-partitioned tables need to be converted to partitions, and if you can use the online redefinition if you are not doing business, there will be a short lock table when the table is switched. The ALTER TABLE online clause in 12c implements an existing index on the table that is valid and non-stop business.

--Oracle 12ccreate table person7 (name varchar2, birth date);--Converting non-partitioned tables to partitioned tables, using hash ALTER TABLE person7 ModifyPartition by Hash (name) (  partition PH1,  partition PH2) onlineupdate indexes;

Operation Partition

--Add partition ALTER TABLE person add partition P9 values less than (MAXVALUE); ALTER TABLE person add partition P9 values (1, 2); c0/>--for list partition ALTER TABLE person add partition;                    --for hash partition--delete partition ALTER TABLE person drop partition P3; --delete partition within data ALTER TABLE person TRUNCATE partition P3; --merge adjacent partitions ALTER TABLE person merge partitions P2, p3 into partition P8; --Split partition ALTER TABLE person split partition P2 at (+) into (partition P3, partition P14);     --split of range partition ALTER TABLE person split partition P2 values (partition P3, partition P4);   --The split of the list partition ALTER TABLE person split partition P2 into (partition P3 values (1, 2), Partition P4 values (3), partition P5); c4/>--list partition split-rename partition ALTER TABLE person rename partition P2 to P12;

Oracle Partition Table