Shulou(Shulou.com)06/01 Report--

The recent project needs to use the partition table, but the partition key value can not be determined, so can only use hash partition (range, list partition used to be simpler than hash partition), query the document, found that there is a gap between the above and the actual use, specifically do experiments to verify.

Official documentation (11g and 12c are all explained the same):

Docs.oracle.com/database/121/CNCPT/schemaob.htm

Hash Partitioning

In hash partitioning, the database maps rows to partitions based on a hashing algorithm that the database applies to the user-specified partitioning key. The destination of a row is determined by the internal hash function applied to the row by the database. The hashing algorithm is designed to distribute rows evenly across devices so that each partition contains about the same number of rows.

When Hash is partitioned, the database maps rows to the partition key specified by the user according to the hash algorithm. The destination of the row is determined by the internal hash function of the database. The goal of the hash algorithm is to distribute rows evenly across the device so that each partition contains the same number of rows.

The table is as follows:

Create table t_test_part (

Id number

Pro_num varchar2 (40)

App_type varchar2 (40)

.

)

Partition by hash (pro_num)

(

Partition part_01

Partition part_02

Partition part_03

Partition part_04

Partition part_05

Partition part_06

Partition part_07

Partition part_08

)

Tablespace DDS_DATA

Pctfree 10

Initrans 2

Maxtrans 255

Storage

(

Initial 64K

Next 64k

Minextents 1

Maxextents unlimited

)

Add 10 million data and perform various DML operations to verify the storage and performance of the data.

The actual conclusions are as follows:

1. Random data insertion

Hash partition is generally a partition strategy used when the partition key value cannot be determined. Oracle inserts the data into the user-specified partition key according to the hash algorithm, which is randomly inserted into a certain area without human intervention.

two。 Partition size

The initial size of the partition is affected by the initialization parameters of the creation table (that is, initial and next). When not specified, it is affected by the tablespace parameters when the database is built. The size after the actual allocation of data is affected by the partition field value. When the partition field value is the same, all data can only be inserted into one partition; when the partition field value is different, the data is randomly inserted into different partitions.

According to the results of the experiment, it is not evenly distributed, nor is it distributed in every area. As shown in the above table, the minimum area is only 0.1m, and the largest area is 33m, which is seriously uneven. Perhaps with the continuous increase of time and the amount of data, the data will tend to be balanced, and it is difficult to verify the estimated experiment.

3. Local and Global indexes

Partitioned index is divided into Local and Global index, Local index and table partition are one-to-one corresponding, Global index is divided into Global non-partitioned index and Global partitioned index. The Global non-partitioned index may or may not correspond to the table partition, but when the Global partitioned index corresponds to the table space of the table partition, the Global partitioned index is a Local index.

4. When partitioned and valued, the Local index is no different from the Global index, but when the range is queried, the partition start and partition stop of the Global index are the same; the partition start and partition stop of the Local index are different, and the table above is from 1 to 8 (depending on the size of the range). According to this situation, if there are many fixed-value queries in the actual application, the Local index is used, and vice versa, the Global index is used. If it is impossible to divide at all, it will have to be weighed.

5. The difference between zoning and non-zoning

As far as my test case is concerned, there is no essential difference between the two, and the time is the same. For 15G data, the partitioned SQL statement COST is thousands of times higher than the unpartitioned SQL statement COST, and the unpartitioned COST is even lower. But there is no difference in execution time between the two. In the actual use of partitions, performance is only one aspect to consider, manageability, usability, partitioning still has great advantages, can not just look at one side and ignore the whole.

Such as:

1)。 The loss or damage of one partition will not affect the rest of the partition, and the damaged partition can be recovered separately.

2)。 Partitions can reduce contention by dividing a large segment into more smaller segments.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.