A detailed explanation of Oracle partitioning methods

Below I have a performance comparison of the concepts of these four partitioning methods, their usage scenarios, and the various partitioning methods.

First, the concept

1, Range partitioning

This is the most commonly used partitioning method, based on column range of values to do partitioning, the most common is based on the time field of the data range of the partition, for example: For sale table, sales time can be done in the month of a range partitioning. This partition is used in the Data Warehouse more, the following is

CREATE statment

CREATE TABLE Sales_range

(salesman_id number (5),

Salesman_name VARCHAR2 (30),

Sales_Amount Number (10),

Sales_date date)

COMPRESS

PARTITION by RANGE (sales_date)

(PARTITION sales_jan2000 VALUES less THAN (to_date (' 02/01/2000 ', ' dd/mm/yyyy ')),

PARTITION sales_feb2000 VALUES Less THAN (to_date (' 03/01/2000 ', ' dd/mm/yyyy ')),

PARTITION sales_mar2000 VALUES Less THAN (to_date (' 04/01/2000 ', ' dd/mm/yyyy ')),

PARTITION sales_apr2000 VALUES Less THAN (to_date (' 05/01/2000 ', ' dd/mm/yyyy '));

For the understanding of the Compress keyword, it will be mentioned in the subsequent compressed partition

2, Hash partitioning

Hash partitioning map data to the partition based on the hash algorithm, the hash algorithm will apply the partition key you specified, averaging the rows in the partitions. For each partition approximately the same size, to ensure that the data can be evenly distributed, the number of partitions is generally 2N. For example, if you need to insert sales_hash a piece of data, Oracle will process salesman_id through the hash algorithm and then find the partition table for insert. Hash partitioning is an ideal method for distributing data across devices, and hash algorithms can easily be transformed into range partitioning methods, especially when the data being partitioned is not historical data.

CREATE TABLE Sales_hash

(salesman_id number (5),

Salesman_name VARCHAR2 (30),

Sales_Amount Number (10),

Week_no number (2))

PARTITION by HASH (salesman_id)

Partitions 4;