Oracle table Partitioning (Partition)

The table partitioning feature improves application performance, improves database manageability and availability, and is a key technology for database management. The database simplifies routine administrative maintenance by using partitioning to improve query performance.

1 Advantages of partitioning

1) Reduce maintenance effort, managing each table partition independently is much easier than managing the whole big table

2) Increase the availability of the database, reducing the likelihood of data corruption due to the spread of data across partitions

3) Balanced I/O, reduced competition, balanced I/O performance by assigning different partitions of the table to different disks

4) The partition is transparent to the user and the user does not feel it exists

5) Improve query speed, for large table DML operations can be decomposed into different partitions of the table to execute, can speed up the execution speed

　　

2 Partitioning Disadvantages

A table that already exists and cannot be converted directly to a partitioned table

3 When to use a partitioned table

1) Table size exceeds 2gb2) table contains historical data, new data is added to new Partition 4 partition type 1) range partition 2) hash partition (hash partition) 3) list partition 4) combined partition (composite partition) 1) Range Partition

A range partition is a wide-ranging table partitioning method that uses the range of column values as the partitioning criteria for partitions, storing records in the range partition where the column values reside.

In terms of time, the first quarter of 2017 data is placed in the first partition, the two quarter data is placed in the second partition, and when created, it is necessary to specify the columns based on and the range values of the partitions. When partitioning by time, if some records are temporarily unpredictable, you can create a maxvalue partition, and all records that are not in the specified range will be stored in the partition where the MaxValue resides.

Suppose there is an EMP table with data 200000 rows in the table, we partition this table through hire_date, each partition stores 50000 rows, and we save each partition in a separate tablespace so that the data file can span multiple physical disks. Here is the code to create the table and partition, as follows:

CREATE TABLE Emp_range
(
EMPNO number NOT NULL primary key,
DEPTNO number is not NULL,
First_Name VARCHAR2 (+) NOT NULL,
Last_Name VARCHAR2 (+) NOT NULL,
Status char (1),
Hire_date date NOT NULL
)
Partition by range (hire_date)
(
Partition Hire_part1 values less than (to_date (' 2017-04-01 ', ' yyyy-mm-dd ') tablespace emp_space01,
Partition Hire_part2 values less than (to_date (' 2017-07-01 ', ' yyyy-mm-dd ') tablespace emp_space02,
Partition HIRE_PART3 values less than (to_date (' 2017-10-01 ', ' yyyy-mm-dd ') tablespace emp_space03,
Partition HIRE_PART4 values less than (to_date (' 2018-01-01 ', ' yyyy-mm-dd ') tablespace emp_space04
);

Test data

Insert into Emp_range (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (Seq_par_id.nextval, ten, ' latiny1 ', ' Liu ', ' 1 ', to_date (' 2017-01-02 ', ' yyyy-mm-dd '));

Insert into Emp_range (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (Seq_par_id.nextval, +, ' latiny2 ', ' liu2 ', ' 1 ', to_date (' 2017-04-02 ', ' yyyy-mm-dd '));

Insert into Emp_range (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (Seq_par_id.nextval, +, ' latiny3 ', ' liu3 ', ' 1 ', to_date (' 2017-07-02 ', ' yyyy-mm-dd '));

Insert into Emp_range (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (Seq_par_id.nextval, max, ' Latiny4 ', ' liu4 ', ' 1 ', to_date (' 2017-10-02 ', ' yyyy-mm-dd '));

Query results by partition

SELECT *
From Emp_range partition (HIRE_PART1);

2) Hash partition

A hash partition is a hash algorithm used on column values to determine which partition the row is placed in. Hash partitioning is recommended when the value of the column does not have an appropriate condition. A hash partition is a type of partition that distributes data evenly by specifying the partition number, because the partitions are identical in size by hashing on the I/O device.

drop table Emp_hash;
CREATE TABLE Emp_hash
(
EMPNO number NOT NULL primary key,
DEPTNO number is not NULL,
First_Name VARCHAR2 (+) NOT NULL,
Last_Name VARCHAR2 (+) NOT NULL,
Status char (1),
Hire_date date NOT NULL
)
Partition by hash (DEPTNO)
(
Partition Dep_part1 tablespace emp_space01,
Partition Dep_part2 tablespace EMP_SPACE02,
Partition Dep_part3 tablespace emp_space03
);

Insert into Emp_hash (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (Seq_par_id.nextval, ten, ' latiny1 ', ' Liu ', ' 1 ', to_date (' 2017-01-02 ', ' yyyy-mm-dd '));

Insert into Emp_hash (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (Seq_par_id.nextval, +, ' latiny2 ', ' liu2 ', ' 1 ', to_date (' 2017-04-02 ', ' yyyy-mm-dd '));

Insert into Emp_hash (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (Seq_par_id.nextval, +, ' latiny3 ', ' liu3 ', ' 1 ', to_date (' 2017-07-02 ', ' yyyy-mm-dd '));

Insert into Emp_hash (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (Seq_par_id.nextval, max, ' Latiny4 ', ' liu4 ', ' 1 ', to_date (' 2017-10-02 ', ' yyyy-mm-dd '));

Insert into Emp_hash (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (seq_par_id.nextval, ' latiny5 ', ' liu5 ', ' 1 ', to_date (' 2017-11-02 ', ' yyyy-mm-dd '));

Insert into Emp_hash (empno, Deptno, first_name, last_name, status, Hire_date)
VALUES (seq_par_id.nextval, ' latiny6 ', ' liu6 ', ' 1 ', to_date (' 2017-08-02 ', ' yyyy-mm-dd '));

SELECT *
From Emp_hash partition (DEP_PART1);

Hash partition The most important mechanism is based on the hash algorithm to calculate the specific record should be inserted into which partition, hash algorithm is the most significant hash function, Oracle if you want to use the hash partition, only specify the number of partitions. It is recommended that the number of partitions be 2 N, which makes the data distribution more evenly between partitions.

3) List partition the feature of this partition is that there are only a few values for a column, and we can use the list partition based on this feature.

CREATE TABLE EMP
(
EMPNO number NOT NULL primary key,
DEPTNO number is not NULL,
First_Name VARCHAR2 (+) NOT NULL,
Last_Name VARCHAR2 (+) NOT NULL,
Status char (1),
Hire_date date NOT NULL
)
Partition by list (status)
(
Partition status_active values (' active ') tablespace emp_ls01,
Partition status_inactive values (' inactive ') tablespace EMP_LS02
)

4) combined partition range--hash partition This partition is based on a range partition and a hash partition, and the table is first scoped by a column and then hashed by a column.

CREATE TABLE EMP
(
EMPNO number NOT NULL primary key,
DEPTNO number is not NULL,
First_Name VARCHAR2 (+) NOT NULL,
Last_Name VARCHAR2 (+) NOT NULL,
Status char (1),
Hire_date date NOT NULL
)
Partition by Range (hire_date) subpartition by hash (DEPTNO) Subpartitions 4 store in (emp_space01,emp_space02,emp_space03 , EMP_SPACE04)
(
Partition part_01 values less than (to_date (' 2017-01-01 ', ' yyyy-mm-dd ')),
Partition part_02 values less than (to_date (' 2017-04-01 ', ' yyyy-mm-dd ')),
Partition part_03 values less than (to_date (' 2017-07-01 ', ' yyyy-mm-dd ')),
Partition part_04 values less than (to_date (' 2017-10-01 ', ' yyyy-mm-dd ')
);

Scope--List partition
This partition is based on a range partition and a list partition, where the table is first partitioned by a column, then a list is partitioned by a column, and the partitions in the partition are called sub-partitions.

CREATE TABLE EMP
(
EMPNO number NOT NULL primary key,
DEPTNO number is not NULL,
First_Name VARCHAR2 (+) NOT NULL,
Last_Name VARCHAR2 (+) NOT NULL,
Status char (1),
Hire_date date NOT NULL
)
Partition by Range (hire_date) Subpartition by list (status)
(
Partition part_01 values less than (to_date (' 2017-01-01 ', ' yyyy-mm-dd ') tablespace emp_space01
(
Subpartition p1sub1 values (' 1 ') tablespace emp_space01,
Subpartition p1sub2 values (' 0 ') tablespace emp_space01

),
Partition part_02 values less than (to_date (' 2017-04-01 ', ' yyyy-mm-dd ') tablespace emp_space02
(
Subpartition p2sub1 values (' 1 ') tablespace emp_space02,
Subpartition p2sub2 values (' 0 ') tablespace emp_space02
)
);

Oracle Table Partitioning (Partition)