Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the mistakes encountered in alter-switch in the partition table". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Create a table that is defined as the target table

Add constraint to the table to ensure that the data of the table is in the target partition

Alter table source table switch to target table partition partition number

The partition number can be obtained from $partition.partion_func (column value)

This process is much faster than insert select, because it doesn't involve io, it just needs to modify the metadata, just the onwer of the partition.

However, the above process only applies to cases where the target partition already exists in the partitioning function of the target table. For example, if you define a partition function with a boundary of 1 and 5, then 4 and 5 will be inserted into the fourth partition.

So I recommend using this method of defining all partitions in advance. If you want to partition by day, you have to define 1000 partitions first. This method is relatively simple and does not need to be checked when adding data, resulting in an error.

If you must use dynamic partitioning, when you add a source table as a new partition, you need to do the following:

First get the value of the newly added partition column, set to X

Find all the boundary values of the target partition function through sys.Partition_range_values and sys.partition_functions

Make the following checks:

Here, there is a difference between whether the demarcation value belongs to the left partition or the right partition, we assume that it belongs to the left partition.

If X exists in the 2-step boundary value set, congratulations, you don't need to do anything extra

If X is greater than the maximum boundary in step 2, you not only need to split a partition for X, but also make sure that data smaller than X has its own partition.

Check the existing boundary values each time you join the partition, and if it is not equal to any of them, take X as the parameter split, and delete the data in the target table where the partition column is the same as X.

Switch the source table to the target partition as described earlier.

If data already exists in the target partition at step 2, the alter... switch statement fails because the target partition is not empty.

It can be understood that the switch operation is to modify the metadata of the table and replace the original allocation unit with a new allocation unit. If the original allocation unit is not empty, then this replacement will result in the loss of some data, thus destroying the integrity of the data, so it is not allowed.

This is the end of the content of "what are the errors encountered in alter-switch in the partition table?" Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.