Turn: Oracle Table partitioning

Oracle table partitioning is divided into four types: range partition, hash partition, list partition, and composite partition.

One: Range partitioning

is to partition partitions based on the range of values of a field in a database table, for example:

1.create table Graderecord  2.  (  3.  Sno Varchar2 (Ten),  4.  Sname varchar2 (  5).  Dormitory VARCHAR2 (3),  6.  Grade int  7.)  8.partition by range (grade)  9. (  partition Bujige values less than (60),--not one  .  Partition Jige values less than (85),--pass  .  Partition Youxiu values less than (MaxValue)-excellent  13.)  

Insert experimental data:

1.insert into Graderecord values (' 511601 ', ' qui ', ' 229 ', to-do);  2.insert into Graderecord values (' 511602 ', ' Kay ', ' 229 ', +);  3.insert into Graderecord values (' 511603 ', ' East ', ' 229 ', +);  4.insert into Graderecord values (' 511604 ', ' bright ', ' 228 ', and ' n ');  5.insert into Graderecord values (' 511605 ', ' to ', ' 228 ', n);  6.insert into Graderecord (sno,sname,dormitory) VALUES (' 511606 ', ' Peak ', ' 228 ');  7.insert into Graderecord values (' 511607 ', ' Ming ', ' + ', ' n ');  8.insert into Graderecord values (' 511608 ', ' nan ', ' + ', +);  9.insert into Graderecord values (' 511609 ', ' Tao ', ' + ');  10.insert into Graderecord values (' 511610 ', ' bo ', ' 240 ', 75);  

The following query all the data, and then query the individual partition data, the code is written together:

1.select * from Graderecord;  2.select * from Graderecord partition (Bujige);  3.select * from Graderecord partition (JIGE);  

All data are as follows

The failed data is as follows:

The passing data are as follows:

The excellent data are as follows:

Description: There is a null value in the data, Oracle The mechanism automatically plans it to MaxValue in the partition.

Two: Hash partition

The hash partition is evenly distributed according to the hash value of the field, as far as possible to achieve the data hash of each partition is equal.

Or just the table, just change the range partition to hash partition, the syntax is as follows (after deleting the table):

1.create table Graderecord  2. (  3.  Sno Varchar2 (Ten),  4.  Sname varchar2 (  5).  Dormitory VARCHAR2 (3),  6.  Grade int  7.)  8.partition by Hash (SNO)  9. (  Ten.  partition P1, one by one  .  Partition P2, a  .  Partition P3  

　　

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

Then query the partition data:

1.select * from Graderecord partition (p1);  2.select * FROM Graderecord partition (P2);  3.select * FROM Graderecord partition (p3);  

P1 Partition data:

P2 Partition data:

P3 Partition data:

Description: Hashed partitions are hash partitions, Oracle using hash code technology partitioning, how the specific partition is Oracle say, or maybe my next search is not the data.

Three: List partition

A list partition explicitly specifies that partitioning is based on a specific value of a field, rather than as a range partition, based on the field's value range.

1.create table Graderecord  2. (  3.  Sno Varchar2 (Ten),  4.  Sname varchar2 (  5).  Dormitory VARCHAR2 (3),  6.  Grade int  7.)  8.partition by List (dormitory)  9. (  Ten.  partition d229 values (' 229 '), one by one  .  Partition d228 values (' 228 '),  .  Partition d240 values (' + ')  13.)  

　　

The above according to the dormitory to partition the list, insert the same data as the range partition experiment, do query as follows:

1.select * from Graderecord partition (d229);  2.select * from Graderecord partition (d228);  3.select * from Graderecord partition (d240);

The data obtained from the d229 partition is as follows

The data obtained from the d228 partition are as follows:

The data obtained from the d240 partition are as follows:

four: Composite partition (range-hash partition, range-list partition)

First, the range-hash partition. Make a statement: List partitions do not support multiple columns, but range partitions and hash partitions support multiple columns.

The code is as follows:

1.create table Graderecord  2. (  3.  Sno Varchar2 (Ten),  4.  Sname varchar2 (  5).  Dormitory VARCHAR2 (3),  6.  Grade int  7.)  8.partition by range (grade),  9.subpartition by Hash (sno,sname)  . (  partition P1 values less than  .            (               subpartition sp1,subpartition SP2            ),  . Partition P2 values less than (MaxValue)  .            (               subpartition sp3,subpartition SP4            )  19.);  

　　

Divide the range by grade, then partition the columns with Sno and sname, and when the data volume is large, the hash partition tends to "average".

Insert data:

1.insert into Graderecord values (' 511601 ', ' qui ', ' 229 ', 92);  2.insert into Graderecord values (' 511602 ', ' Kay ', ' 229 ', 62);  3.insert into Graderecord values (' 511603 ', ' East ', ' 229 ', 26);  4.insert into Graderecord values (' 511604 ', ' bright ', ' 228 ', 77);  5.insert into Graderecord values (' 511605 ', ' King ', ' 228 ', 47);  6.insert into Graderecord (sno,sname,dormitory) VALUES (' 511606 ', ' Peak ', ' 228 ');  7.insert into Graderecord values (' 511607 ', ' Ming ', ' 240 ', 90);  8.insert into Graderecord values (' 511608 ', ' nan ', ' 240 ', 100);  9.insert into Graderecord values (' 511609 ', ' Tao ', ' 240 ', 67);  10.insert into Graderecord values (' 511610 ', ' bo ', ' 240 ', 75);  11.insert into Graderecord values (' 511611 ', ' Zheng ', ' 240 ', 60);  12.insert into Graderecord values (' 511612 ', ' Beaver ', ' 244 ', 72);  13.insert into Graderecord values (' 511613 ', ' Jay ', ' 244 ', 88);  14.insert into Graderecord values (' 511614 ', ' wilt ', ' 244 ', 19);  15.insert into Graderecord values (' 511615 ', ' rustic ', ' 244 ', 65);  16.insert into Graderecord values (' 511616 ', ' Dan ', ' 244 ', 59); 17.insert into Graderecord values (' 511617 ', ' Jin ', ' 244 ', 95);  

The query is as follows:

1.select * from Graderecord partition (p1);  2.select * FROM Graderecord partition (P2);  3.select * FROM Graderecord subpartition (SP1);  4.select * from Graderecord subpartition (SP2);  5.select * from Graderecord subpartition (SP3);  6.select * from Graderecord subpartition (SP4);  

The partition P1 data is as follows, in this example 75 points below:

The partition P2 data is as follows, in this case 75 points including 75 points:

Sub-partition SP1:

Sub-partition SP2:

Sub-partition SP3:

Sub-partition SP4:

Note: When the amount of data is getting larger, the data in the partitioned table of the hash partition becomes more and more balanced.

Below range-list partition

Scope-list partition There are two ways to create, first of all, there is no template creation method, this table I want to rebuild:

1.create Table Mobilemessage 2. (3. Acct_month VARCHAR2 (6),--Account date format: Year Yyyymm 4. Area_no VARCHAR2 (10),--region number 5. day_id VARCHAR2 (2),--the day of the month in the format DD 6. Subscrbid VARCHAR2 (20),--User ID 7.  Svcnum VARCHAR2 (30)--Mobile number 8.) 9.partition by Range (acct_month,area_no) subpartition by list (day_id) 10.  (one. partition P1 values less than (' 200705 ', ' 012 ') 12.    (Subpartition shangxun1 values (' 01 ', ' 02 ', ' 03 ', ' 04 ', ' 05 ', ' 06 ', ' 07 ', ' 08 ', ' 09 ', ' 10 '), 14.    Subpartition zhongxun1 values (' 11 ', ' 12 ', ' 13 ', ' 14 ', ' 15 ', ' 16 ', ' 17 ', ' 18 ', ' 19 ', ' 20 '), 15.  Subpartition xiaxun1 values (' 21 ', ' 22 ', ' 23 ', ' 24 ', ' 25 ', ' 26 ', ' 27 ', ' 28 ', ' 29 ', ' 30 ', ' 31 ') 16.  ), 17.  Partition P2 values less than (' 200709 ', ' 014 ') 18.    (Subpartition shangxun2 values (' 01 ', ' 02 ', ' 03 ', ' 04 ', ' 05 ', ' 06 ', ' 07 ', ' 08 ', ' 09 ', ' 10 '), 20.    Subpartition zhongxun2 values (' 11 ', ' 12 ', ' 13 ', ' 14 ', ' 15 ', ' 16 ', ' 17 ', ' 18 ', ' 19 ', ' 20 '), 21.  Subpartition xiaxun2 values (' 21 ', ' 22 ', ' 23 ', ' 24 ', ' 25 ', ' 26 ', ' 27 ', ' 28 ', ' 29 ', ' 30 ', ' 31 ') 22.  ), 23. PartItion P3 values less than (' 200801 ', ' 016 ') 24.    (Subpartition shangxun3 values (' 01 ', ' 02 ', ' 03 ', ' 04 ', ' 05 ', ' 06 ', ' 07 ', ' 08 ', ' 09 ', ' 10 '), 26.    Subpartition zhongxun3 values (' 11 ', ' 12 ', ' 13 ', ' 14 ', ' 15 ', ' 16 ', ' 17 ', ' 18 ', ' 19 ', ' 20 '), 27.  Subpartition xiaxun3 values (' 21 ', ' 22 ', ' 23 ', ' 24 ', ' 25 ', ' 26 ', ' 27 ', ' 28 ', ' 29 ', ' 30 ', ' 31 ') 28.   ) 29.)

  Insert experimental data:

1.insert into mobilemessage values (' 200701 ', ' 010 ', ' ', ' ghk001 ', ' 13800000000 ');  2.insert into mobilemessage values (' 200702 ', ' 015 ', ' n ', ' myx001 ', ' 13633330000 ');  3.insert into mobilemessage values (' 200703 ', ' 015 ', ' + ', ' hjd001 ', ' 13300000000 ');  4.insert into mobilemessage values (' 200704 ', ' 010 ', ' ', ' ghk001 ', ' 13800000000 ');  5.insert into mobilemessage values (' 200705 ', ' 010 ', ' ', ' ghk001 ', ' 13800000000 ');  6.insert into mobilemessage values (' 200705 ', ' 011 ', ' + ', ' sxl001 ', ' 13222000000 ');  7.insert into mobilemessage values (' 200706 ', ' 011 ', ' + ', ' sxl001 ', ' 13222000000 ');  8.insert into mobilemessage values (' 200706 ', ' 012 ', ' One ', ' tgg001 ', ' 13800044400 ');  9.insert into mobilemessage values (' 200707 ', ' 010 ', ' ', ' ghk001 ', ' 13800000000 ');  10.insert into mobilemessage values (' 200708 ', ' 012 ', ' + ', ' tgg001 ', ' 13800044400 ');  11.insert into mobilemessage values (' 200709 ', ' 014 ', ' n ', ' zjj001 ', ' 13100000000 ');  12.insert into mobilemessage values (' 200710 ', ' 014 ', ' n ', ' zjj001 ', ' 13100000000 '); 13.insert into mobilemessAge values (' 200711 ', ' 014 ', ' n ', ' zjj001 ', ' 13100000000 ');  14.insert into mobilemessage values (' 200711 ', ' 013 ', ' a ', ' wgc001 ', ' 13444000000 ');  15.insert into mobilemessage values (' 200712 ', ' 013 ', ' a ', ' wgc001 ', ' 13444000000 ');  16.insert into mobilemessage values (' 200712 ', ' 010 ', ' A ', ' ghk001 ', ' 13800000000 ');   17.insert into mobilemessage values (' 200801 ', ' 015 ', ' n ', ' myx001 ', ' 13633330000 ');

　　

The query results are as follows:

1.select * from Mobilemessage;  

　　

The partition P1 query results are as follows:

The partition P2 query results are as follows:

Sub-partition XIAXUN2 query results are as follows:

Description: The partition law of Range partition range (A, b), the range partition is the values less than (a, b), usually a is the case, if the lower than a does not have to consider B, directly into the, if equal to a then consider B, if satisfied B also inserted.

Another range-list partition, which contains templates (more cumbersome, but more precise, is necessary to process mass storage data):

1.create Table Mobilemessage 2. (3. Acct_month VARCHAR2 (6),--Account date format: Year Yyyymm 4. Area_no VARCHAR2 (10),--region number 5. day_id VARCHAR2 (2),--the day of the month in the format DD 6. Subscrbid VARCHAR2 (20),--User ID 7.  Svcnum VARCHAR2 (30)--Mobile number 8.) 9.partition by Range (acct_month,area_no) subpartition by list (day_id) 10.subpartition template 11. (Subpartition sub1 values (' ""), Subpartition sub2 values (' in '), Subpartition sub3 values (' ""), Subpartition Sub 4 values (' 04 '), 14. Subpartition sub5 values (' a '), subpartition sub6 values (' 06 '), 15. Subpartition sub7 values (' "), subpartition sub8 values (' 08 '), 16. Subpartition sub9 VALUES (' the "), Subpartition sub10 values (' 10 '), 17. Subpartition SUB11 values (' one '), subpartition sub12 values (' 12 '), 18. Subpartition sub13 values (' + '), subpartition sub14 values (' 14 '), 19. Subpartition sub15 values (' a '), subpartition sub16 values (' 16 '), 20. Subpartition sub17 values (' + '), subpartition sub18 values (' 18 '), 21. Subpartition sub19 values (' + '), SubpartitIon Sub20 values (' 20 '), 22. Subpartition sub21 values (' + '), subpartition sub22 values (' 22 '), 23. Subpartition sub23 values (' All '), subpartition sub24 values (' 24 '), 24. Subpartition sub25 values (' + '), subpartition sub26 values (' 26 '), 25. Subpartition sub27 values (' subpartition '), sub28 values (' 28 '), 26. Subpartition sub29 values (' $ '), subpartition sub30 values (' 30 '), 27.  Subpartition sub31 values (' 31 ') 28.)  29. (30.  Partition p_0701_010 values less than (' 200701 ', ' 011 '), 31.  Partition p_0701_011 values less than (' 200701 ', ' 012 '), 32.  Partition p_0701_012 values less than (' 200701 ', ' 013 '), 33.  Partition p_0701_013 values less than (' 200701 ', ' 014 '), 34.  Partition p_0701_014 values less than (' 200701 ', ' 015 '), 35.  Partition p_0701_015 values less than (' 200701 ', ' 016 '), 36.  Partition p_0702_010 values less than (' 200702 ', ' 011 '), 37.  Partition p_0702_011 values less than (' 200702 ', ' 012 '), 38.  Partition p_0702_012 values less than (' 200702 ', ' 013 '), 39. Partition p_0702_013 VAlues less than (' 200702 ', ' 014 '), 40.  Partition p_0702_014 values less than (' 200702 ', ' 015 '), 41.  Partition p_0702_015 values less than (' 200702 ', ' 016 '), 42.  Partition p_0703_010 values less than (' 200703 ', ' 011 '), 43.  Partition p_0703_011 values less than (' 200703 ', ' 012 '), 44.  Partition p_0703_012 values less than (' 200703 ', ' 013 '), 45.  Partition p_0703_013 values less than (' 200703 ', ' 014 '), 46.  Partition p_0703_014 values less than (' 200703 ', ' 015 '), 47.  Partition p_0703_015 values less than (' 200703 ', ' 016 '), 48.  Partition p_0704_010 values less than (' 200704 ', ' 011 '), 49.  Partition p_0704_011 values less than (' 200704 ', ' 012 '), 50.  Partition p_0704_012 values less than (' 200704 ', ' 013 '), 51.  Partition p_0704_013 values less than (' 200704 ', ' 014 '), 52.  Partition p_0704_014 values less than (' 200704 ', ' 015 '), 53.  Partition p_0704_015 values less than (' 200704 ', ' 016 '), 54.  Partition p_0705_010 values less than (' 200705 ', ' 011 '), 55. Partition p_0705_011 values less than (' 200705 ', ' 012 '), 56.  Partition p_0705_012 values less than (' 200705 ', ' 013 '), 57.  Partition p_0705_013 values less than (' 200705 ', ' 014 '), 58.  Partition p_0705_014 values less than (' 200705 ', ' 015 '), 59.  Partition p_0705_015 values less than (' 200705 ', ' 016 '), 60.  Partition p_0706_010 values less than (' 200706 ', ' 011 '), 61.  Partition p_0706_011 values less than (' 200706 ', ' 012 '), 62.  Partition p_0706_012 values less than (' 200706 ', ' 013 '), 63.  Partition p_0706_013 values less than (' 200706 ', ' 014 '), 64.  Partition p_0706_014 values less than (' 200706 ', ' 015 '), 65.  Partition p_0706_015 values less than (' 200706 ', ' 016 '), 66.  Partition p_0707_010 values less than (' 200707 ', ' 011 '), 67.  Partition p_0707_011 values less than (' 200707 ', ' 012 '), 68.  Partition p_0707_012 values less than (' 200707 ', ' 013 '), 69.  Partition p_0707_013 values less than (' 200707 ', ' 014 '), 70.  Partition p_0707_014 values less than (' 200707 ', ' 015 '), 71. Partition p_0707_015 values less than (' 200707 ', ' 016 '), 72. Partition p_0708_010 values less than (' 200708 ', ' 011 '), 73.  Partition p_0708_011 values less than (' 200708 ', ' 012 '), 74.  Partition p_0708_012 values less than (' 200708 ', ' 013 '), 75.  Partition p_0708_013 values less than (' 200708 ', ' 014 '), 76.  Partition p_0708_014 values less than (' 200708 ', ' 015 '), 77.  Partition p_0708_015 values less than (' 200708 ', ' 016 '), 78.  Partition p_0709_010 values less than (' 200709 ', ' 011 '), 79.  Partition p_0709_011 values less than (' 200709 ', ' 012 '), 80.  Partition p_0709_012 values less than (' 200709 ', ' 013 '), 81.  Partition p_0709_013 values less than (' 200709 ', ' 014 '), 82.  Partition p_0709_014 values less than (' 200709 ', ' 015 '), 83.  Partition p_0709_015 values less than (' 200709 ', ' 016 '), 84.  Partition p_0710_010 values less than (' 200710 ', ' 011 '), 85.  Partition p_0710_011 values less than (' 200710 ', ' 012 '), 86.  Partition p_0710_012 values less than (' 200710 ', ' 013 '), 87.  Partition p_0710_013 values less than (' 200710 ', ' 014 '), 88. Partition p_0710_014 values less than (' 200710 ', ' 015 '), 89.  Partition p_0710_015 values less than (' 200710 ', ' 016 '), 90.  Partition p_0711_010 values less than (' 200711 ', ' 011 '), 91.  Partition p_0711_011 values less than (' 200711 ', ' 012 '), 92.  Partition p_0711_012 values less than (' 200711 ', ' 013 '), 93.  Partition p_0711_013 values less than (' 200711 ', ' 014 '), 94.  Partition p_0711_014 values less than (' 200711 ', ' 015 '), 95.  Partition p_0711_015 values less than (' 200711 ', ' 016 '), 96.  Partition p_0712_010 values less than (' 200712 ', ' 011 '), 97.  Partition p_0712_011 values less than (' 200712 ', ' 012 '), 98.  Partition p_0712_012 values less than (' 200712 ', ' 013 '), 99.  Partition p_0712_013 values less than (' 200712 ', ' 014 '), 100.  Partition p_0712_014 values less than (' 200712 ', ' 015 '), 101.  Partition p_0712_015 values less than (' 200712 ', ' 016 '), 102.  Partition p_0801_010 values less than (' 200801 ', ' 011 '), 103.  Partition p_0801_011 values less than (' 200801 ', ' 012 '), 104. Partition p_0801_012 Values Less Than (' 200801 ', ' 013 '), 105.  Partition p_0801_013 values less than (' 200801 ', ' 014 '), 106.  Partition p_0801_014 values less than (' 200801 ', ' 015 '), 107.  Partition p_0801_015 values less than (' 200801 ', ' 016 '), 108.  Partition P_other values less than (MaxValue, MaxValue) 109.);

　　

This is the day that the template sub-partition is detailed to the month with the template sub-partition. This partitioning mode automatically creates sub-partitions whenever a partition is established.

Insert the same data without template partitioning experiment, randomly query partition data:

Query Partition p_0701_010 data:

1.select * from Mobilemessage partition (p_0701_010);  

Query Result:

Querying sub-partition P_0701_010_SUB4 data:

The query results are as follows:

Here's how to maintain the partition:

(1) Split partition, take the first range partition as an example:

1.alter table Graderecord split partition Jige at (2)   .      Into (partition keyi,partition Lianghao);  

Divide the partition into two partitions: can and good.

(2) Merging partitions, taking the first range partition as an example:

1.alter table Graderecord Merge Partitions Keyi,lianghao   

It can be combined with a good two partitions to pass.

(3) Adding a partition, because adding partitions on the range partition requires that the added partition range is greater than the original partition maximum, but the original partition maximum is already maxvalue, so this is the second hash partition as an example:

1.alter table Graderecord Add partition P4;  

A partition P4 is added to the hash partition example.

(4) Delete partition, Syntax:

1.alter table table_name drop partition Partition_name;  

(5) Truncate the partition, emptying the data in the partition

　　

Description: The operation of a partition can also be treated as a sub-partition, with the same effect. Deleting a partition also deletes the sub-partition under it. Merging multiple partitions will also automatically merge their sub-partitions. Divide the partition with attention to the split point.

The difference between partitioned table operations without template sub-partitions and partition tables with template sub-partitions: Partitioned tables with sub-partition templates automatically add sub-partitions when adding partitions, and partition tables without template sub-partitions do not have this function; partitioned tables with sub-partition templates can only change partitions when changing partitions. Partitioned tables without template sub-partitions must be aware of changes with the child partitions when changing partitions.

Turn: Oracle Table partitioning