Detailed explanation of the difference between Oracle 10 partitions and Mysql partitions

Oracle10g partitions are commonly used: range (range partition), list (list partition), hash (hash partition), range-hash (range-hash partition), range-list (list-composite partition).

Range partition: Range partition is a widely used table partition mode, which takes the range of column values as the partition partition condition, and stores records in the range partition where column values are located.

If divided by time, the data in January 2010 will be put into a partition, and the data in February 2010 will be put into b partition. When creating, it is necessary to specify the columns based on and the range value of the partition.

When partitioning by time, if some records can't predict the range temporarily, you can create an maxvalue partition, and all records that are not within the specified range will be stored in the partition where maxvalue is located. Such as:

createtable pdba (id number, time date) partition by range (time)
(
partitionp1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),
partitionp2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),
partitionp3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),
partitionp4 values less than (maxvalue)
)

Hash partition:

For tables that cannot be effectively scoped, you can use hash partitioning, which will definitely help to improve performance. hash partition will evenly distribute the data in the table to several partitions you specify. The partition where the columns are located is automatically allocated according to the hash value of the partition column, so you can't control or know which record will be placed in which partition. hash partition can also support multiple dependent columns. Such as:

createtable test
(
transaction_idnumber primary key,
item_idnumber(8) not null
)
partitionby hash(transaction_id)
(
partitionpart_01 tablespace tablespace01,
partitionpart_02 tablespace tablespace02,
partitionpart_03 tablespace tablespace03
);

Here, we specify the tablespace for each partition.

List partition:

List partition also needs to specify the column value, and its partition value must be explicitly specified. There can only be one partition column. It is impossible to specify multiple columns as partition dependent columns at the same time like range or hash partition, but its single partition corresponding value can be multiple.

When partitioning, it is necessary to determine the possible values of partitioned columns. If the inserted column value is not within the partition range, the insert/update will fail. Therefore, when using list partition, it is generally recommended to create an default partition to store records that are not within the specified range, similar to maxvalue partition in range partition.

When partitioning according to a field, such as city code, you can specify default and put all the data without partitioning rules into this default partition. Such as:

createtable custaddr
(
idvarchar2(15 byte) not null,
areacodevarchar2(4 byte)
)
partitionby list (areacode)
(partition t_list025 values ('025'),
partitiont_list372 values ('372') ,
partitiont_list510 values ('510'),
partitionp_other values (default)
)

Combined partition:

If a table is still large after being partitioned according to a certain column, or if it is required by 1 other, the partition can be repartitioned by rebuilding sub-partitions within the partition, that is, the combined partition mode.

There are two kinds of combined partitions in 10g: range-hash and range-list. Note the order, the root partition can only be an range partition, and the child partition can be an hash partition or an list partition.

Such as:

createtable test
(
transaction_idnumber primary key,
transaction_datedate
)
partitionby range(transaction_date) subpartition by hash(transaction_id)
subpartitions3 store in (tablespace01,tablespace02,tablespace03)
(
partitionpart_01 values less than(to_date('2009-01-01','yyyy-mm-dd')),
partitionpart_02 values less than(to_date('2010-01-01','yyyy-mm-dd')),
partitionpart_03 values less than(maxvalue)
);
createtable emp_sub_template (deptno number, empname varchar(32), grade number)
partitionby range(deptno) subpartition by hash(empname)
subpartitiontemplate
(subpartitiona tablespace ts1,
subpartitionb tablespace ts2,
subpartitionc tablespace ts3,
subpartitiond tablespace ts4
)
(partitionp1 values less than (1000),
partitionp2 values less than (2000),
partitionp3 values less than (maxvalue)
);
createtable quarterly_regional_sales
(deptnonumber, item_no varchar2(20),
txn_datedate, txn_amount number, state varchar2(2))
tablespacets4
partitionby range (txn_date)
subpartitionby list (state)
(partitionq1_1999 values less than (to_date('1-apr-1999','dd-mon-yyyy'))
(subpartitionq1_1999_northwest values ('or', 'wa'),
subpartitionq1_1999_southwest values ('az', 'ut', 'nm'),
subpartitionq1_1999_northeast values ('ny', 'vm', 'nj'),
subpartitionq1_1999_southeast values ('fl', 'ga'),
subpartitionq1_1999_northcentral values ('sd', 'wi'),
subpartitionq1_1999_southcentral values ('ok', 'tx')
),
partitionq2_1999 values less than ( to_date('1-jul-1999','dd-mon-yyyy'))
(subpartitionq2_1999_northwest values ('or', 'wa'),
subpartitionq2_1999_southwest values ('az', 'ut', 'nm'),
subpartitionq2_1999_northeast values ('ny', 'vm', 'nj'),
subpartitionq2_1999_southeast values ('fl', 'ga'),
subpartitionq2_1999_northcentral values ('sd', 'wi'),
subpartitionq2_1999_southcentral values ('ok', 'tx')
),
partitionq3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy'))
(subpartitionq3_1999_northwest values ('or', 'wa'),
subpartitionq3_1999_southwest values ('az', 'ut', 'nm'),
subpartitionq3_1999_northeast values ('ny', 'vm', 'nj'),
subpartitionq3_1999_southeast values ('fl', 'ga'),
subpartitionq3_1999_northcentral values ('sd', 'wi'),
subpartitionq3_1999_southcentral values ('ok', 'tx')
),
partitionq4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy'))
(subpartitionq4_1999_northwest values ('or', 'wa'),
subpartitionq4_1999_southwest values ('az', 'ut', 'nm'),
subpartitionq4_1999_northeast values ('ny', 'vm', 'nj'),
subpartitionq4_1999_southeast values ('fl', 'ga'),
subpartitionq4_1999_northcentral values ('sd', 'wi'),
subpartitionq4_1999_southcentral values ('ok', 'tx')
)
);

MySQL partition is commonly used: range, list, hash, key

RANGE partitions (portioning): Tuples are allocated to partitions based on the range to which column values belong.

 LIST partition: Similar to partition by RANGE, the difference is that LIST partition is selected based on column value matching a certain value in a discrete value set.

 HASH partition: A partition selected based on the return value of a user-defined function. This expression is evaluated using the column values of these rows to be inserted into the table. This function can contain any valid expression in MySQL that produces a non-negative integer value.

 KEY partition: Similar to partition by HASH, the difference is that KEY partition only supports calculating one column or more columns, and MySQL server provides its own hash function.