Oracle partition details

Conversion from http://love-flying-snow.iteye.com/blog/573303

Let's talk about partition syntax.

Oracle table partitions are divided into four types: range partitions, hash partitions, list partitions, and composite partitions.

 

I. Range partitioning

Partitions are based on the range of values of a field in the database table. For example:

Create Table graderecord (SNO varchar2 (10), sname varchar2 (20), dormitory varchar2 (3), grade INT) partition by range (grade) (partition bujige values less than (60), -- fail partition jige values less than (85), -- Pass Partition youxiu values less than (maxvalue) -- excellent)

Insert experiment data:

Insert into graderecord values ('20140901', 'kual', '20160901', 92); insert into graderecord values ('20160901', 'Kai', '20160901', 62 ); insert into graderecord values ('20160301', 'east', '20160301', 26); insert into graderecord values ('20160301', 'liang', '20160301', 77 ); insert into graderecord values ('20140901', 'jing', '20160901', 47); insert into graderecord (SNO, sname, dormitory) values ('20160301', 'feng ', '200'); insert into graderecord values ('200', 'ming', '200', 90); insert into graderecord values ('200', 'nan ', '20140901', 240); insert into graderecord values ('20160901', 'Tao', '20160901', 67); insert into graderecord values ('20160901', 'bo ', '000000', 75); insert into graderecord values ('000000', '000000', '000000', 60 );

Run the following code to query all the data in each partition:

select * from graderecord;select * from graderecord partition(bujige);select * from graderecord partition(jige);select * from graderecord partition(youxiu);

All data is as follows:

 

The failure data is as follows:

 

Pass data is as follows:

 

Excellent data is as follows:

Note: if there is a null value in the data, Oracle will automatically plan it to the maxvalue partition.

 

Ii. Hash partitioning

Hash partitions are evenly distributed based on the hash value of the field, and the data in the hash values of each partition is equal as much as possible.

In the same table, the range partition is changed to a hash partition. The syntax is as follows (delete the table and recreate it ):

create table graderecord(  sno varchar2(10),  sname varchar2(20),  dormitory varchar2(3),  grade int)partition by hash(sno)(  partition p1,  partition p2,  partition p3);

 

Insert the experiment data, which is the same as the data inserted in the range partition experiment.

Then query the partition data:

select * from graderecord partition(p1);select * from graderecord partition(p2);select * from graderecord partition(p3);

Data in the P1 partition:

 

P2 partition data:

 

Partition P3 data:

 

Note: Hash partitions are hash partitions. Oracle uses hash code technology to partition partitions. The specific partition is calculated by Oracle. This data may not be used for my next search.

 

Iii. List partitions

List partitions explicitly specify partitions based on a specific value of a field, rather than based on the value range of the field as in range partitions.

create table graderecord(  sno varchar2(10),  sname varchar2(20),  dormitory varchar2(3),  grade int)partition by list(dormitory)(  partition d229 values('229'),  partition d228 values('228'),  partition d240 values('240'))

The preceding table partitions are performed based on the dormitory, and the same data as the range partition experiment is inserted. The query result is as follows:

select * from graderecord partition(d229);select * from graderecord partition(d228);select * from graderecord partition(d240);

The data obtained from the d229 partition is as follows:

 

The data obtained from the d228 partition is as follows:

 

The data obtained from the d240 partition is as follows:

 

4. Composite partitioning(Range-Hash partition, range-list partition)

First, we will talk about the range-Hash partition. First, declare that list partitions do not support multiple columns, but range partitions and hash partitions support multiple columns.

The Code is as follows:

create table graderecord(  sno varchar2(10),  sname varchar2(20),  dormitory varchar2(3),  grade int)partition by range(grade)subpartition by hash(sno,sname)(  partition p1 values less than(75)            (               subpartition sp1,subpartition sp2            ),  partition p2 values less than(maxvalue)            (               subpartition sp3,subpartition sp4            ));

Use grade to divide the range, and then use SnO and sname to partition scattered columns. When the data volume is large, hash partitions tend to be "average ".

Insert data:

Insert into graderecord values ('20140901', 'kual', '20160901', 92); insert into graderecord values ('20160901', 'Kai', '20160901', 62 ); insert into graderecord values ('20160301', 'east', '20160301', 26); insert into graderecord values ('20160301', 'liang', '20160301', 77 ); insert into graderecord values ('20140901', 'jing', '20160901', 47); insert into graderecord (SNO, sname, dormitory) values ('20160301', 'feng ', '200'); insert into graderecord values ('200', 'ming', '200', 90); insert into graderecord values ('200', 'nan ', '20140901', 240); insert into graderecord values ('20160901', 'Tao', '20160901', 67); insert into graderecord values ('20160901', 'bo ', '000000', 75); insert into graderecord values ('000000', '000000', '000000', 60); insert into graderecord values ('000000', 'raccoon ', '20140901', 72); insert into graderecord values ('20160901', 'je', '20160901', 88); insert into graderecord values ('20160901', 'wild ', '20140901', 19); insert into graderecord values ('20160901', '20160901', 65); insert into graderecord values ('20160901', 'dan ', '20140901', 59); insert into graderecord values ('20160901', 'hangzhou', '20160901', 95 );

The query is as follows:

select * from graderecord partition(p1);select * from graderecord partition(p2);select * from graderecord subpartition(sp1);select * from graderecord subpartition(sp2);select * from graderecord subpartition(sp3);select * from graderecord subpartition(sp4);

Partition P1 data is as follows:

 

The P2 partition data is as follows. In this example, 75 points out:

 

Subpartition SP1:

 

Sub-partition SP2:

 

Subpartition SP3:

 

Subpartition SP4:

 

Note: As the data volume increases, the data in the partition tables of hash partitions is becoming more and more balanced.

 

Scope-list partitions

Range-list partitions can be created in two ways. Let's talk about the creation method without a template. I want to recreate this table:

Create Table mobilemessage (acct_month varchar2 (6), -- accounting format: yyyymm area_no varchar2 (10), -- region number day_id varchar2 (2 ), -- dd subscrbid varchar2 (20), -- User ID svcnum varchar2 (30) -- mobile number) partition by range (acct_month, area_no) subpartition by list (day_id) (partition P1 values less than ('2017012', '012') (subpartition shangxun1 values ('01', '02', '03', '04 ', '05 ', '06', '07', '08', '09', '10'), subpartition zhongxun1 values ('11', '12', '13 ', '14', '15', '16', '17', '18', '19', '20'), subpartition xiaxun1 values ('21 ', '22', '23', '24', '25', '26', '27', '28', '29', '30 ', '31'), partition P2 values less than ('2017014', '014') (subpartition shangxun2 values ('01', '02', '03 ', '04 ', '05', '06 ', '07', '08 ', '09', '10'), subpartition zhongxun2 values ('11 ', '12', '13', '14', '15', '16', '17', '18', '19', '20 '), subpartition xiaxun2 values ('21', '22', '23', '24', '25', '26', '27', '28', '29 ', '30', '31'), partition P3 values less than ('2017016', '016') (subpartition shangxun3 values ('01', '02 ', '03 ', '04', '05 ', '06', '07 ', '08', '09 ', '10 '), subpartition zhongxun3 values ('11', '12', '13', '14', '15', '16', '17', '18', '19 ', '20'), subpartition xiaxun3 values ('21', '22', '23', '24', '25', '26', '27 ', '28', '29', '30', '31 ')))

 

Insert experiment data:

insert into MobileMessage values('200701','010','04','ghk001','13800000000');insert into MobileMessage values('200702','015','12','myx001','13633330000');insert into MobileMessage values('200703','015','24','hjd001','13300000000');insert into MobileMessage values('200704','010','04','ghk001','13800000000');insert into MobileMessage values('200705','010','04','ghk001','13800000000');insert into MobileMessage values('200705','011','18','sxl001','13222000000');insert into MobileMessage values('200706','011','21','sxl001','13222000000');insert into MobileMessage values('200706','012','11','tgg001','13800044400');insert into MobileMessage values('200707','010','04','ghk001','13800000000');insert into MobileMessage values('200708','012','24','tgg001','13800044400');insert into MobileMessage values('200709','014','29','zjj001','13100000000');insert into MobileMessage values('200710','014','29','zjj001','13100000000');insert into MobileMessage values('200711','014','29','zjj001','13100000000');insert into MobileMessage values('200711','013','30','wgc001','13444000000');insert into MobileMessage values('200712','013','30','wgc001','13444000000');insert into MobileMessage values('200712','010','30','ghk001','13800000000');insert into MobileMessage values('200801','015','22','myx001','13633330000');

The query result is as follows:

select * from MobileMessage;

The query result is as follows:

 

 

The query result of partition P1 is as follows:

 

The query results of partition P2 are as follows:

 

The query results of the subpartition xiaxun2 are as follows:

 

Note: The Partition Rules of range (A, B) are all values less than (a, B). Generally, a prevails, if B is less than a, insert B directly. If B is equal to a, consider B. If B is satisfied, insert B.

 

Another range-list partitions, including templates (complex, but more precise, it is necessary to process massive storage data ):

Create Table mobilemessage (acct_month varchar2 (6), -- accounting format: yyyymm area_no varchar2 (10), -- region number day_id varchar2 (2 ), -- dd subscrbid varchar2 (20), -- User ID svcnum varchar2 (30) -- mobile number) partition by range (acct_month, area_no) subpartition by list (day_id) subpartition template (subpartition sub1 values ('01'), subpartition sub2 values ('02'), subpartition sub3 values ('03'), subpartition sub4 values ('04 '), subpartition sub5 values ('05 '), subpartition sub6 values ('06'), subpartition sub7 values ('07'), subpartition sub8 values ('08 '), subpartition sub9 values ('09'), subpartition sub10 values ('10'), subpartition sub11 values ('11'), subpartition sub12 values ('12 '), subpartition sub13 values ('13'), subpartition sub14 values ('14'), subpartition sub15 values ('15'), subpartition sub16 values ('16 '), subpartition sub17 values ('17'), subpartition sub18 values ('18'), subpartition sub19 values ('19'), subpartition sub20 values ('20 '), subpartition sub21 values ('21'), subpartition sub22 values ('22'), subpartition sub23 values ('23'), subpartition sub24 values ('24 '), subpartition sub25 values ('25'), subpartition sub26 values ('26'), subpartition sub27 values ('27'), subpartition sub28 values ('28 '), subpartition sub29 values ('29'), subpartition sub30 values ('30'), subpartition sub31 values ('31') (partition p_0701_010 values less than ('123 ', '011'), partition p_0701_011 values less than ('2017012', '012'), partition p_0701_012 values less than ('2017013', '013 '), partition p_0701_013 values less than ('2017014', '014'), partition p_0701_014 values less than ('2017015', '015'), partition p_0701_015 values less ', '016'), partition p_0702_010 values less than ('2017011', '011'), partition p_0702_011 values less than ('2017012', '012 '), partition lower values less than ('2017013 '), partition p_0702_013 values less than ('2017014', '014'), partition p_0702_014 values less than ('2016080 ', '015'), partition p_0702_015 values less than ('2017016', '016'), partition p_0703_010 values less than ('2017011', '011 '), partition partition values less than ('2017012', '012'), partition p_0703_012 values less than ('2017013', '013'), partition p_0703_013 values less ', '014'), partition p_0703_014 values less than ('2017015', '015'), partition p_0703_015 values less than ('2017014', '016 '), partition p_0704_010 values less than ('2017011'), partition values less than ('2017011', '012'), partition p_0704_012 values less than ('2017080 ', '013'), partition p_0704_013 values less than ('2017014', '014'), partition p_0704_014 values less than ('2017013', '015 '), partition partition values less than ('2017016'), partition p_0705_010 values less than ('2017011', '011'), partition p_0705_011 values less than ('2017080 ', '012'), partition p_0705_012 values less than ('2017013', '013'), partition p_0705_013 values less than ('2017012', '014 '), partition lower values less than ('2017015', '015'), partition p_0705_015 values less than ('2017016', '016'), partition p_0706_010 values less ', '011'), partition p_0706_011 values less than ('2017012', '012'), partition p_0706_012 values less than ('2017011', '013 '), partition lower values less than ('2017014'), partition p_0706_014 values less than ('2017015', '015'), partition p_0706_015 values less than ('2017014 ', '016'), partition p_0707_010 values less than ('2017011', '011'), partition p_0707_011 values less than ('2017012', '012 '), partition partition values less than ('2017013 '), partition p_0707_013 values less than ('2017014', '014'), partition p_0707_014 values less than ('2016080 ', '015'), partition p_0707_015 values less than ('2017016', '016'), partition p_0708_010 values less than ('2017011', '011 '), partition partition values less than ('2017012', '012'), partition p_0708_012 values less than ('2017013', '013'), partition p_0708_013 values less ', '014'), partition p_0708_014 values less than ('2017015', '015'), partition p_0708_015 values less than ('2017014', '016 '), partition p_0709_010 values less than ('2017011'), partition values less than ('2017011', '012'), partition p_0709_012 values less than ('2017080 ', '013'), partition p_0709_013 values less than ('2017014', '014'), partition p_0709_014 values less than ('2017013', '015 '), partition p_0709_015 values less than ('000000', '016'), partition p_0710_010 values less than ('000000', '011'), partition p_0710_011 values less than ('000000 ', '012'), partition p_0710_012 values less than ('000000', '013'), partition p_0710_013 values less than ('2017014', '014 '), partition p_0710_014 values less than ('2017015', '015'), partition p_0710_015 values less than ('2017016', '016'), partition p_0711_010 values less ', '011'), partition p_0711_011 values less than ('2017012', '012'), partition p_0711_012 values less than ('2017011', '013 '), partition p_0711_013 values less than ('2017014', '014'), partition p_0711_014 values less than ('2017015', '015'), partition p_0711_015 values less ', '016'), partition p_0712_010 values less than ('2017011', '011'), partition p_0712_011 values less than ('2017012', '012 '), partition p_0712_012 values less than ('2017013 '), partition p_0712_013 values less than ('2017014', '014'), partition p_0712_014 values less than ('20160 ', '015'), partition p_0712_015 values less than ('2017016', '016'), partition p_080385010 values less than ('2017011', '011 '), partition p_0800000011 values less than ('2017012', '012'), partition p_0800000012 values less than ('2017013 ', '013'), partition p_0800000013 values less ', '014'), partition p_0800000014 values less than ('000000', '015'), partition p_0800000015 values less than ('2017016', '016 '), partition p_other values less than (maxvalue, maxvalue ));

This is a subpartition with a template. The subpartition of the template is detailed to the day of the month. In this partition mode, as long as a partition is created, the subpartition is automatically created.

Insert the same data in the partition experiment without a template, and randomly query the partition data:

 

Query the data of the partition p_0701_010:

select * from MobileMessage partition(p_0701_010);

Query results:

 

Query the data of the subpartition p_0701_010_sub4:

Select * From mobilemessage subpartition (p_0701_010_sub4 );

The query result is as follows:

 

Query the data of the partition p_0706_011:

Select * From mobilemessage partition (p_0706_011 );

The query result is as follows:

 

Query the data of the subpartition p_0706_011_sub21:

Select * From mobilemessage subpartition (p_0706_011_sub21 );

The query result is as follows:

 

The following describes how to maintain a partition:

(1) split the partition. Take the first range partition as an example:

Alter table graderecord split partition jige at (75) into (partition keyi, partition lianghao );

Split the Pass Partition into two partitions: Yes and good.

 

(2) merge partitions. Take the first range partition as an example:

Alter table graderecord merge partitions keyi, lianghao into partition jige;

Merge the two shards that can be used with the good ones into pass.

(3) Add a partition. Because you need to add a partition to a range partition, the range of the partition must be greater than the maximum value of the original partition, but the maximum value of the original partition is already maxvalue, the second hash partition is used as an example:

Alter table graderecord add partition P4;

In the hash partition example, a partition p4 is added.

(4) Delete A partition. Syntax:

Alter table table_name drop partition partition_name;

(5) truncate the partition and clear the data in the partition.

Alter table table_name truncate partition partition_name;

 

Note: operations on partitions can also be performed on subpartitions, with the same effect. Deleting a partition deletes its subpartitions at the same time. Merging multiple partitions will also automatically merge their subpartitions. Pay attention to the splitting points when splitting a partition.

 

In addition, there is a difference between a partition table operation without a template subpartition and a partition table with a template subpartition: a partition table with a subpartition template automatically adds a subpartition when adding a partition, this function is not available for partition tables without template subpartitions. When you change a partition, you only need to change the partition, when you change a partition table without a template subpartition, you must change the partition together with the subpartition.