Database partition: oracle database partition

Database partition: oracle database partition
Database partition:
Concept:Take mysql as an example. The data in the mysql database isExist on disk as a fileBy default, it is placed under/mysql/data (you can use my. in cnf), a table corresponds to three files, one is the frm table structure, the other is the myd table data, and the other is the myi table index. If the data size of a table is too large, myd and myi will become very large, and the query data will become very slow.We can use the mysql Partition Function to physically split the three files corresponding to this table into many small pieces. In this way, when we look for a piece of data, you don't need to search all the data. You just need to know where the data is, and then you can find it.If the data in the table is too large, it may not be stored on a disk. In this case, we can allocate data to different disks.Partition Method:1. Horizontal partitioningWhat is a horizontal partition? Is to partition horizontally. For example,If there are million pieces of data, divide them into ten parts, put the first 10 million pieces of data in the first partition, and the second 10 million pieces of data in the second partition, and so on. That is, the table is divided into ten copies.The root database uses merge to split tables, which is a bit like that. When a piece of data is retrieved, the data contains all fields in the table structure, that is, the horizontal partition does not change the table structure.2. vertical partitioningWhat is vertical partitioning? The partition is vertical. For example, when designing a user table, we didn't consider it at the beginning, but put all the personal information in a table, in this way, there will be relatively large fields in this table, such as personal profiles, which may not be viewed by many people, so when someone looks at them, they will look for them, when you split a table, you can split such a large field. Partitions provided by mysql belong to the first type.

Partition advantages

1. partitions can be divided into multiple disks to store more disks.

2. Based on the search condition, that is, the condition after the where clause, you do not need to find all the corresponding partitions.

3. Parallel processing can be performed for big data search.

4. distribute data queries across multiple disks to achieve higher query Throughput

Database Table partition Optimization

The SQL Server data table partition process is divided into three steps:

1) create a partition function

2) create a partition scheme

3) partition the table

Step 1: Create a partition function

The partition function defines [u] how [/u], that is, how you want SQL Server to partition data. Here, we will not use a table as an example, but summarize the technology of data segmentation.

Partitions are implemented by specifying the dividing line of each partition. For example, assume that we have a mers MERs table that contains information about all the Customers of the enterprise. The customer information is identified by a unique customer number, ranging from 1 to 1000000. We can use the following Partition Function (customer_Partfunc) to divide the table into four partitions equally:

Create partition function customer_partfunc (int)
AS RANGE RIGHT
For values (250000,500 000, 750000)

The split boundary specifies four partitions. The first partition contains all records whose values are less than 250000. The second partition contains all records with values between 250000 and 499999. The third partition contains all records with values between 500000 and 749999. All other records greater than or equal to 750000 are included in the Fourth partition.

Note that the "range right" clause is used in this example. This indicates that the demarcation value is on the right side of the partition. Similarly, if the range left clause is used, the first partition contains all records whose values are less than or equal to 250000; the second partition will contain all records with values between 250001 and 500000, and so on.

Step 2: Create a partition scheme

Once you have created a partition function that defines how to partition data, the next step is to create a partition scheme, defining [u] where [/u], that is, where you want to partition the data. This is a straightforward process. For example, if I have four file groups named from "fg1" to "fg4", the following partition scheme can be used:

Create partition scheme customer_partscheme
As partition customer_partfunc
TO (fg1, fg2, fg3, fg4)

Note that we have connected a partition function to the partitioning scheme, but we have not connected the partitioning scheme to any specific database table. This is the time to reuse the function. We can use this function to apply the Partition Scheme (or just a partition function) to any data in the database table.

Step 3: partition the table

After creating the partition scheme, you can partition the table. This is the simplest step. You only need to add the "ON" clause in the table creation statement to specify the table partitioning scheme and the table columns to apply the partitioning scheme. You do not need to specify the partition function, because the partition function has been defined in the Partition Scheme.

For example, if you want to use the preceding partitioning scheme to create a customer table, you need to use the following Transact-SQL statement:

Create table MERs (FirstName nvarchar (40), LastName nvarchar (40), CustomerNumber int)
ON customer_partscheme (CustomerNumber)... the remaining full text>

Database Table partition Optimization

The SQL Server data table partition process is divided into three steps:

1) create a partition function

2) create a partition scheme

3) partition the table

Step 1: Create a partition function

The partition function defines [u] how [/u], that is, how you want SQL Server to partition data. Here, we will not use a table as an example, but summarize the technology of data segmentation.

Partitions are implemented by specifying the dividing line of each partition. For example, assume that we have a mers MERs table that contains information about all the Customers of the enterprise. The customer information is identified by a unique customer number, ranging from 1 to 1000000. We can use the following Partition Function (customer_Partfunc) to divide the table into four partitions equally:

Create partition function customer_partfunc (int)
AS RANGE RIGHT
For values (250000,500 000, 750000)

The split boundary specifies four partitions. The first partition contains all records whose values are less than 250000. The second partition contains all records with values between 250000 and 499999. The third partition contains all records with values between 500000 and 749999. All other records greater than or equal to 750000 are included in the Fourth partition.

Note that the "range right" clause is used in this example. This indicates that the demarcation value is on the right side of the partition. Similarly, if the range left clause is used, the first partition contains all records whose values are less than or equal to 250000; the second partition will contain all records with values between 250001 and 500000, and so on.

Step 2: Create a partition scheme

Once you have created a partition function that defines how to partition data, the next step is to create a partition scheme, defining [u] where [/u], that is, where you want to partition the data. This is a straightforward process. For example, if I have four file groups named from "fg1" to "fg4", the following partition scheme can be used:

Create partition scheme customer_partscheme
As partition customer_partfunc
TO (fg1, fg2, fg3, fg4)

Note that we have connected a partition function to the partitioning scheme, but we have not connected the partitioning scheme to any specific database table. This is the time to reuse the function. We can use this function to apply the Partition Scheme (or just a partition function) to any data in the database table.

Step 3: partition the table

After creating the partition scheme, you can partition the table. This is the simplest step. You only need to add the "ON" clause in the table creation statement to specify the table partitioning scheme and the table columns to apply the partitioning scheme. You do not need to specify the partition function, because the partition function has been defined in the Partition Scheme.

For example, if you want to use the preceding partitioning scheme to create a customer table, you need to use the following Transact-SQL statement:

Create table MERs (FirstName nvarchar (40), LastName nvarchar (40), CustomerNumber int)
ON customer_partscheme (CustomerNumber)... the remaining full text>