Detailed explanation of the difference between Oracle10 partitions and MySQL partitions

The commonly used Oracle10g partitions are: range (range partitioning), list (list partitioning), hash (hash partitioning), range-hash (range-hash partitioning), and range-list (list-composite partitioning).

Range partitioning: Range partitioning is a widely used table partitioning method. It uses the range of column values ​​as the partitioning condition and stores records in the range partition where the column value is located.

For example, if the partition is based on time, the data of January 2010 is placed in partition a, and the data of February is placed in partition b. When creating the partition, you need to specify the column to be based on and the range value of the partition.

When partitioning by time, if the range of some records cannot be predicted for the time being, you can create a maxvalue partition. All records that are not in the specified range will be stored in the partition where the maxvalue is located. like:

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 divided into ranges, hash partitioning can be used, which will still help improve performance. Hash partitioning will evenly distribute the data in the table to several partitions you specify. The partition where the column is located is automatically assigned based on the hash value of the partition column. Therefore, you cannot control or know which record will be placed in which partition. Hash partitioning can also support multiple dependent columns. like:

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 partitioning also requires specifying column values. Its partition value must be explicitly specified. There can only be one partition column. Unlike range or hash partitioning, multiple columns cannot be specified as partition dependent columns at the same time. However, a single partition can have multiple corresponding values.

When partitioning, you must determine the possible values ​​of the partition column. Once the inserted column value is not in the partition range, the insertion/update will fail. Therefore, it is usually recommended to create a default partition when using list partitioning to store records that are not in the specified range, similar to the maxvalue partition in the range partition.

When partitioning based on a certain field, such as city code, you can specify a default partition and put all data that does not conform to the partitioning rules into this default partition. like:

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

Combined partitions:

If a table is still large after being partitioned by a column, or there are some other requirements, the partition can be further partitioned by creating sub-partitions within the partition, that is, the combined partition method.

There are two types of composite partitions in 10g: range-hash and range-list. Note the order. The root partition can only be a range partition, and the subpartition can be a hash partition or a list partition.

like:

createtable test
(
transaction_idnumber primary key,
transaction_date
)
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,
subpartitioned 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 partitions are commonly used: range, list, hash, key

RANGE partitioning: Tuples are assigned to partitions based on the range of column values.

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

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

 KEY partitioning: Similar to HASH partitioning, the difference is that KEY partitioning only supports calculation of one or more columns, and the MySQL server provides its own hash function.

The above is a detailed explanation of the differences between Oracle 10 partitions and MySQL partitions. I hope it will be helpful to everyone. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!