Detailed introduction to the oracle partition table method

The concept of a partitioned table is simply as follows: the data in the original table is stored in a tablespace. After a partitioned table is used, the table data is stored in multiple tablespaces.

PS: a tablespace is a logical concept that includes all files under a user.

In a database, several tables (especially historical tables) occupy more than 50% of the database space, or hundreds of GB of disk capacity. In this case, we need to consider using partition tables.

Oracle table partitions are divided into four types: Range partitions, Hash partitions, and List partitions) and composite partitions (range-hash, range-list partition range-list)

The benefits of partitioning mainly use the following two points:
Improves query performance
Easy to maintain backup data

I. Range partitioning
Range partitioning refers to partitioning based on the range of values of a field in the table. It is particularly suitable for partitioning by time, date, and ID.

1. Create a test user

Create user wangyi identified by wangyi;
Grant dba to wangyi;
Conn wangyi/wangyi;

2. Create test tables and data
// The policy is to create partitions by month, retain data for one year, and back up data for the year before the end of the year.

Example:

Create table range_table (
Inst_date date,
Produce_id number,
Amt varchar (12)
)
Partition by range (inst_date)
(
Partition p1 values less than (to_date ('2017/123456', 'DD/MM/YYYY ')),
Partition p2 values less than (to_date ('2017/123456', 'DD/MM/YYYY ')),
Partition p3 values less than (to_date ('2017/123456', 'DD/MM/YYYY ')),
Partition p4 values less than (to_date ('2014/1/123', 'DD/MM/YYYY ')),
Partition p5 values less than (to_date ('2017/123456', 'DD/MM/YYYY '))
);

Insert test data:

Insert into range_table values (to_date ('2014/1/8080', 'DD/MM/YYYY '), 1, '0. 1 ');
Insert into range_table values (to_date ('2017/123456', 'DD/MM/YYYY '), 2, '0. 2 ');
Insert into range_table values (to_date ('2017/123456', 'DD/MM/YYYY '), 3, '0. 3 ');

PS: partition tables do not have to be placed in different tablespaces. To put different tablespaces, you must first create a tablespace file and then add tablespace space_nameXX to the end of the partition statement.

3. Query partition information
1)

Select * from dba_part_tables where owner = 'wangyi ';

2)

Select count (*) from range_table;

COUNT (*)
----------
  3

3)

Select count (*) from range_table partition (p2 );

COUNT (*)
----------
  1

We can see three records in three partitions.

4. 11 GB can be automatically partitioned using the interval function

Create table range_table (
Inst_date date,
Produce_id number,
Amt varchar (12)
)
Partition by range (inst_date)
Interval (numtoyminterval (1, 'month '))
(
Partition p1 values less than (to_date ('2017-01-01 ', 'yyyy-MM-DD '))
);

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.

Example:

Create table hash_table (
Inst_date date,
Produce_id number,
Amt varchar (12)
)
Partition by hash (inst_date)
(
Partition p1,
Partition p2,
Partition p3
);

Insert into hash_table values (to_date ('2017/123456', 'DD/MM/YYYY '), 1, '0. 1 ');
Insert into hash_table values (to_date ('2017/123456', 'DD/MM/YYYY '), 2, '0. 2 ');
Insert into hash_table values (to_date ('2017/123456', 'DD/MM/YYYY '), 3, '0. 3 ');

2. Query partition information
1)

Select count (*) from hash_table;

COUNT (*)
----------
  3

2)

Select count (*) from hash_table partition (p1 );

COUNT (*)
----------
  1

3)

Select count (*) from hash_table partition (p2 );

COUNT (*)
----------
  2

The partition to which the record falls is determined by the hash value calculated by oracle.

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.

List partitions do not support multiple columns, but range partitions and hash partitions support multiple columns.

Example:

Create table list_table (
Inst_date date,
Produce_id number,
Amt varchar (12)
)
Partition by list (produce_id)
(
Partition p1 values (1 ),
Partition p2 values (2 ),
Partition p3 values (3)
);

Insert into list_table values (to_date ('2017/123456', 'DD/MM/YYYY '), 1, '0. 1 ');
Insert into list_table values (to_date ('2017/123456', 'DD/MM/YYYY '), 2, '0. 2 ');
Insert into list_table values (to_date ('2017/123456', 'DD/MM/YYYY '), 3, '0. 3 ');

3. Query partition information
1)

Select count (*) from list_table partition (p1 );

COUNT (*)
----------
  1

2)

Select count (*) from list_table partition (p2 );

COUNT (*)
----------
  1

3)

Select count (*) from list_table partition (p3 );

COUNT (*)
----------
  1

4. Composite partition (range-hash partition, range-list partition)
A composite partition allows you to create a subpartition in the partition.

Complex. For more information, see the first link of references...

5. Partition maintenance

1. Clear table partition data

Alter table name truncate partition name;

2. Directly delete partitions

Alter table name drop partition name;