Table Partitioning (learning notes), Table Partitioning learning notes

Table Partitioning (learning notes), Table Partitioning learning notes

Table partition:

Application Scenario: For tables with a large data volume, for example, tables with 2 GB of data, 20 pieces can be queried, which is certainly faster than non-segmented queries.

Interval partition:It is often used to partition A Date Field.

Less than does not mean that the value in () is smaller

Example 1,Create a range Partition

-- Create table drawlist (draw_dt date not null) -- CREATE a table partition by range (draw_dt) (PARTITION part_1 values less than (to_date ('2017/123 ', 'dd-mm-yyyy'), PARTITION part_2 values less than (to_date ('2017/123', 'dd-mm-yyyy ')), PARTITION part_3 values less than (Maxvalue); -- tables and partitions must be created at the same time; otherwise, partitions cannot be created.

Insert data

-- Insert into drawlist (draw_dt) VALUES (to_date ('2017-12-31 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2017-1-1 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2017-6-6 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2017-12-31 ', 'yyyy-mm-dd ')); insert into drawlist (draw_dt) VALUES (to_date ('2017-6-6 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2017-1-1 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2017-5-5 ', 'yyyy-mm-dd '));

Query

-- Query SELECT * FROM drawlist; SELECT * FROM drawlist PARTITION (part_1); -- Query PARTITION 1 SELECT * FROM drawlist PARTITION (part_2 ); -- Query PARTITION 2 SELECT * FROM drawlist PARTITION (part_3); -- Query PARTITION 3

 

Example 2,Create a hash Partition

-- Create table drawlist (draw_dt date not null) -- CREATE a table partition by range (draw_dt) (PARTITION part_1 values less than (to_date ('2017/123 ', 'dd-mm-yyyy'), PARTITION part_2 values less than (to_date ('2017/123', 'dd-mm-yyyy ')), PARTITION part_3 values less than (Maxvalue); -- tables and partitions must be created at the same time; otherwise, partitions cannot be created -- INSERT data INTO drawlist (draw_dt) VALUES (to_date ('2014-12-31 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2014-1-1-1 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2017-6-6 ', 'yyyy-mm-dd ')); insert into drawlist (draw_dt) VALUES (to_date ('2017-12-31 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2017-6-6 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2017-1-1 ', 'yyyy-mm-dd'); insert into drawlist (draw_dt) VALUES (to_date ('2017-5-5 ', 'yyyy-mm-dd '));

-- Query SELECT * FROM hash_table; select count (*) FROM hash_table PARTITION (part_1); select count (*) FROM hash_table PARTITION (part_2 ); select count (*) FROM hash_table PARTITION (part_3); select count (*) FROM hash_table PARTITION (part_4 );

List partition:Mainly used zone code databases such as ZIP codes and area codes

Example 3,Create list partitions

-- Create table area (code integer not null, NAME VARCHAR2 (10) -- create list partition by list (CODE) (PARTITION part_1 VALUES (102203 ), PARTITION part_2 VALUES (164303,); -- use the data generator to insert records -- Query SELECT * FROM area; SELECT * FROM area PARTITION (part_1 ); SELECT * FROM area PARTITION (part_2 );

Example 4,Partition combination -- interval-Hash Partition

-- Interval HASH -- create table range_hash (dt_date date not null, hash_no integer not null) -- CREATE interval hash partition by range (dt_date) subpartition by hash (hash_no) (PARTITION part_1 values less than (to_date ('2014/1/123', 'dd-mm-yyyy '), PARTITION part_2 values less than (to_date ('2014/1/123 ', 'dd-mm-yyyy'), PARTITION part_3 values less than (maxvalue); -- hash partitions in the interval column, partitions by interval, and then hash partitions, at this time, the hash is not very meaningful, and the hash still needs to be partitioned by range.

Example 5,Combined partitioning -- interval-list partitioning

-- Range list -- create table range_list (dt_date date not null, code integer not null) -- create range list partition by range (dt_date) subpartition by list (CODE) (PARTITION part_1 values less than (to_date ('1970/123', 'dd-mm-yyyy ') (SUBPARTITION part1_list1 VALUES (1/1), SUBPARTITION part1_list2 VALUES (2009 )), PARTITION part_2 values less than (to_date ('1970/123', 'dd-mm-yyyy ') (SUBPARTITION part2_list1 VALUES (1/1), SUBPARTITION part2_list2 VALUES (2011 )), PARTITION part_3 values less than (MAXVALUE) (SUBPARTITION part3_list1 VALUES (102200), SUBPARTITION part3_list2 VALUES (164300); -- INSERT data INTO range_list (dt_date, code) VALUES (to_date ('1970-12-31 ', 'yyyy-mm-dd'), 2008); insert into range_list (dt_date, code) VALUES (to_date ('1970-12-31 ', 'yyyy-mm-dd'), 164300); insert into range_list (dt_date, code) VALUES (to_date ('2017-12-31 ', 'yyyy-mm-dd '), 102200); insert into range_list (dt_date, code) VALUES (to_date ('2017-12-31 ', 'yyyy-mm-dd'), 2010); insert into range_list (dt_date, code) VALUES (to_date ('1970-12-31 ', 'yyyy-mm-dd'), 2011); insert into range_list (dt_date, code) VALUES (to_date ('1970-12-31 ', 'yyyy-mm-dd'), 2009); -- Query SELECT * FROM range_list; SELECT * FROM RANGE_list PARTITION (part_1 ); SELECT * FROM range_list SUBPARTITION (part1_list1); -- Query subpartition select * FROM range_list SUBPARTITION (part1_list2 );

 

Highly bounded partitions cannot be merged into low-bounded partitions.

Maintain partitions

-Create table dlist (dl_date date not null) -- CREATE an interval partition by range (dl_date) (PARTITION part_1 values less than (to_date ('2017/123 ', 'dd-mm-yyyy'), PARTITION part_2 values less than (to_date ('2017/123', 'dd-mm-yyyy ')), PARTITION part_3 values less than (Maxvalue); -- INSERT data INTO dlist (dl_date) VALUES (to_date ('2017-12-31 ', 'yyyy-mm-dd ')); insert into dlist (dl_date) VALUES (to_date ('1970-1-1 ', 'yyyy-mm-dd'); insert into dlist (dl_date) VALUES (to_date ('2017-6-6 ', 'yyyy-mm-dd'); insert into dlist (dl_date) VALUES (to_date ('2017-12-31 ', 'yyyy-mm-dd'); insert into dlist (dl_date) VALUES (to_date ('2017-6-6 ', 'yyyy-mm-dd ')); insert into dlist (dl_date) VALUES (to_date ('1970-1-1 ', 'yyyy-mm-dd'); insert into dlist (dl_date) VALUES (to_date ('1970-5-5', 'yyyy-mm-dd'); insert into dlist (dl_date) VALUES (to_date ('1970-12-31 ', 'yyyy-mm-dd'); -- Query SELECT * FROM dlist; SELECT * FROM dlist PARTITION (part_1); SELECT * FROM dlist PARTITION (part_2 ); SELECT * FROM dlist PARTITION (part_3 );

Add Partition

-When adding a partition table, the newly added PARTITION must be higher THAN the last PARTITION limit that has already been divided. alter table dlist add partition part_4 values less than (to_date ('2017/123 ', 'dd-mm-yyyy'); -- you must first Delete PART_3 and maxvalue -- delete the partition table alter table dlist drop partition part_3; -- delete data together -- insert into dlist (dl_date) VALUES (to_date ('1996-5-5', 'yyyy-mm-dd'); insert into dlist (dl_date) VALUES (to_date ('1970-12-31 ', 'yyyy-mm-dd'); -- Query SELECT * FROM dlist; SELECT * FROM dlist PARTITION (part_1 ); SELECT * FROM dlist PARTITION (part_2); SELECT * FROM dlist PARTITION (part_4 );

Partition Truncation

-- Truncation partition alter table dlist truncate partition part_1; -- Query SELECT * FROM dlist PARTITION (part_1 );

Merge partitions

-- Merge partition alter table dlist merge partitions part_2, part_4 into partition part_4; -- the merged PARTITION disappears SELECT * FROM dlist PARTITION (part_2 ); -- the system will prompt that the PARTITION does not exist SELECT * FROM dlist PARTITION (part_4 );