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

MySQL Commonly used partitions are: range, list, hash, key. Commonly used Oracle10g partitions are: range (range partition), list (list partition), hash (hash partition), range- hash (range-hash partition), range-list (list-composite partition). The following is a detailed introduction to the differences between Oracle10 partitions and Mysql partitions through this article. Let’s take a look at

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

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

If divided according to time, the data in January 2010 is placed in partition a, and the data in February is placed in partition b. When creating, you need to specify the based column and the range value of the partition.

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

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 those tables that cannot be effectively divided into ranges, hash partitioning can be used, which will still be helpful to 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 allocated based on the hash value of the partition column, so you have no control or knowledge about which record will be placed in which partition. , hash partitioning 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 table space for each partition.

List partition:

List partition also needs to specify the value of the column. The partition value must be clearly specified. There can only be one partition column, and it cannot be like range or hash. Partitioning specifies multiple columns as partition dependent columns at the same time, but its corresponding value for a single partition can be multiple.

When partitioning, you must determine the possible values ​​of the partition column. Once the inserted column value is not within the partition range, the insertion/update will fail. Therefore, it is usually recommended to use list partitioning. Create a default partition to store records that are not within 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 default and put all non-partitioning rule data into this default partition. For example:

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 partitioning:

If a table is still large after being partitioned according to a certain column, or there are some other requirements, Partitions can also be subdivided by creating subpartitions within the partition, that is, combining partitions.

There are two types of combined partitions in 10g: range-hash and range-list. Pay attention to the order. The root partition can only be a range partition, and the sub-partition can be a hash partition or a list partition.

For example:

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')
)
);

Commonly used MySQL partitions are: range, list, hash, key

RANGE partitioning (portioning): According to the range interval to which the column value belongs, the elements are divided into Groups are assigned to partitions.

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

 HASH partitioning: Partitioning selected based on the return value of a user-defined function that expression uses the columns of the rows to be inserted into the table value is calculated. This function can contain any expression that is valid in MySQL and 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 the detailed content of Detailed explanation of the difference between Mysql partition and Oracle 10 partitions. For more information, please follow other related articles on the PHP Chinese website!