Analysis of database table partition technology (horizontal split/vertical split/library table hash) _oracle

one. Horizontal split
What is horizontal segmentation? To make a comparison of the figurative, when eating in the cafeteria, there is only one window, queuing line to play the team too long, are lined up in S-type, then easy to let people in line to produce anxiety, easy to create confusion, when a manager stood out, add more dozen rice window, the long team down the way into several teams. A more image of the understanding, you take a "scalpel", a large table to cut a few knives, the result of this large table, into a few small tables.

A horizontal split puts data into two or more separate tables based on certain criteria. That is, the record is divided into separate records can be saved separately, each child table has the same number of columns. Horizontal cutting divides a table into multiple tables. Each table contains the same number of columns, but less data rows. For example, you can partition a table that contains 1 billion rows horizontally into 12 tables, each representing one months of data in a specific year. Any query that requires a specific month's data simply references the table of the corresponding month.

The conditions commonly used to horizontally split tables are: Date-time dimensions, regional dimensions, and, of course, more business dimensions. Let me give you a few examples to explain
Case 1: A company sales record data is too large, we can split it horizontally, each month's sales record as a separate table.
Case 2: A group of companies in various regions have branches, the group's order data sheet is too large, we can according to the area of the branch to cut horizontally.
Case 3: A telecom company's words by date, ground city level cut, found that the amount of data is too large, and then they are according to brand, number section for horizontal cutting
horizontal splits are usually used in the following situations：
(1) Table data volume is very large, after segmentation can reduce the query needs to read data and index of the number of pages, but also reduce the index layer, speed up the query speed.
(2) The data in the table is inherently independent, for example, the data in each region is recorded in the table, or data in different periods, especially when some data is commonly used, while some other data is not used.
(3) The data need to be stored on multiple media.
(4) need to separate historical data from the current data.
Advantages:
1: Reduce the number of data and indexes that need to be read in the query, while also reducing the level of the index, speeding up the query speed.
Disadvantages:
1: Horizontal segmentation will increase the complexity of the application, it usually requires multiple table names in the query, query all data need union operation. In many database applications, this complexity can outweigh the benefits, because as long as the index key is small, when the index is used for querying, the table adds two to three times times the amount of data, and the query increases the number of disks that read an index layer.

two. Vertical split
What is vertical segmentation? Make an image of the metaphor, a small company through a few years of development into a large multinational enterprises, the former department structure can not meet the current business development, the CEO crackling the company into the financial department, the Ministry of Personnel, production, sales department ..., all of a sudden set up a number of departments, their respective roles. This is a relatively image of it, there are wood? Oh
You divide the table vertically (without breaking the third paradigm), place the main code (primary key) and some columns in a table, and then place the main code (primary key) and some other columns into another table. Divide the original table into multiple tables that contain only a few columns. If some columns in a table are common and others are not, you can use vertical segmentation.
Advantages:
1: Vertical segmentation can make the row data smaller, a block of data can hold more data, in the query will reduce I/o times (each query read the block is less).
2: Vertical partition table can maximize the use of cache purposes.
Disadvantages:
1: After the vertical partition of the table, the main code (primary key) appears redundant, need to manage redundant columns
2: Table join JOIN operation (increased CPU overhead) needs to be circumvented from the business

three. Library Table Hash
Table hashing is similar to horizontal segmentation, but there is no obvious dividing boundary like horizontal segmentation, the hash algorithm is used to disperse the data into various tables, so IO is more balanced. In general, we will be in accordance with the business or functional modules to separate the database, different modules corresponding to different databases or tables, and then a certain strategy for a page or function of a smaller database hash, such as user table, according to the User ID table hash, hash 128 sheets, You should be able to improve the performance of the system at a low cost and have a good scalability