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

This article mainly shows you "how to modify the data of the partition table in the database", the content is easy to understand, clear, hope to help you solve the doubt, the following let the editor lead you to study and learn "how to modify the data of the partition table in the database" this article.

Demand case: the student's information is stored in the student table, partitioned according to the class, a student changes the class, and his information needs to be modified in the student table.

Student table structure statement

Create table student (id number (5), name varchar2 (10), class_id number (2)) partition by list (class_id) (partition values (1), partition values (2))

test data

Insert into student values (1 recorder xiaomingyin 1)

Insert into student values (2 recorder xiaohongqi 1)

Insert into student values (2 people Xiaohuaqi 2)

Insert into student values (2 recorder xiaoyueqian 2)

Commit

Modify the id=3 of xiaoming. After modification, the partition in which the data resides remains unchanged.

Update student n set n.id=3 where n.name = 'xiaoming'

Commit

Modify xiaoming to Class 2

Update student n set n.class_id=2 where n.name = 'xiaoming'

Execution will report an error.

Reason analysis: because the data of the partition table is stored in different partitions according to the partition rules, if the data is modified and is no longer in a partition, the data needs to be deleted before inserting a new partition, and the corresponding parameters in the database are not set, resulting in an error report.

Solution:

1. Export the data that needs to be modified to the temporary table, then modify the data in the temporary table, delete the data that needs to be modified in the original table, and insert the data of the temporary table into the partition table.

Advantages: there is no need to modify the parameters of the database, which can ensure little impact on the performance of the system.

Disadvantages: when there is a large amount of data, the problem of high water level will be more obvious.

Create table student_tmp as select * from student n where n.name = 'xiaoming'

Update student_tmp n set n.class_id=2 where n.name = 'xiaoming'

Commit

Delete from student n where n.name = 'xiaoming'

Commit

Insert into student select * from student_tmp

Commit

two。 Open the database row movement

Advantages: database can modify partitioned tables across partitions

Disadvantages: due to the modification of the parameters of the database, the impact is greater, all aspects of testing are needed, and the impact on the system has been considered to a minimum.

Alter table student enable row movement

After executing this statement, there will be no problem to modify the data across partitions in the partitioned table.

Update student n set n.class_id=2 where n.name = 'xiaoming'

Commit

The above is all the contents of the article "how to modify the data of partition tables in the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.