Detailed analysis: Oracle Table Partitioning (1)

In Oracle table partitions, a table can be divided into multiple small parts. This improves the query performance of Oracle table partitions, manages table data, and optimizes the performance of backup and recovery operations.

Oracle table partitions are divided into several types (range partitions, hash partitions, subpartitions, list partitions, and index partitions ).

Now let's create a [range partition]

 
 

  
  
create table RangeTable(  
  
  
 id int primary key,  
  
  
 name varchar(20),  
  
  
 grade int  
  
  
)  
  
  
partition by rang(grade)  
  
  
(  
  
  
partition part1 values less then(50) tablespace Part1_tb,  
  
  
partition part2 values less then(MAXVALUE) tablespace Part2_tb  
  
  
);  
 
 

If the grade value is less than 50, the record will be placed in the partition named part1, And the part1 partition will be stored in the Part1_tb tablespace.
Others are placed in part2. MAXVALUE is the keyword of Oracle to indicate the maximum value.

[Hash partition]

 
 

  
  
create table HashTable(  
  
  
 id int primary key,  
  
  
 name varchar(20),  
  
  
 grade int  
  
  
)  
 
 

/* There are two methods: 1 is to specify the number of partitions and the tablespace used, 2 is to specify the partition named */

 
 

  
  
Partition by hash (grade)
  
  
Partitions 10 -- specify the number of partitions
  
  
Store in (Part1_tb, Part2_tb, Part3_tb) -- if the number of specified partitions is greater than that of the tablespace, the partitions are allocated to the tablespace cyclically.
  
  
/*------------------------------------*/
  
  
Partition by rang (grade) -- this method specifies the partition named after
  
  
(
  
  
Partition part1 tablespace Part1_tb,
  
  
Partition part2 tablespace Part2_tb
  
  
);