SQL - Savepoints

Overview

Savepoints are a very handy feature in Oracle especially during development or data manipulation. The basic idea is that as a set of transactions are being accomplished; you can create a point in time to return the data without having to completely redo all work done. That is a tremendously valuable feature, let’s say you moved through 6 of 10 steps into your development process and decided you needed to only back track 1 step. With an intermittent savepoint in place, you can go backwards to that point into your development instead of rolling back all of your work in that session saving you valuable redevelopment time.

Here is How it Works

            I am going to setup some holiday pay bonuses now while I have plenty of time to consider how they hit our budget. For this discussion, I am going to limit the increases to department #80. The bonus is a function of a percentage of the employee’s salary and commission with the commission being used to decide the bonus multiple. The script used to populate the initial bonus data in the rec_merit_incr field of the employee table is shown in Figure 1.

Figure 1. Initial bonus data calculations.

            The commission breakpoint is .25 which decides whether an employee receives the .1 or the .075 bonus calculation. I would like to consider bumping the .075 up to .095 to get an updated sum for the holiday budget. Up to this point I have created new columns and populated data in support of the project. For the work so far set in place, I will create a SAVEPOINT named merit1 as shown in Figure 2. I name it in a way that another colleague will not likely create a similar SAVEPOINT name during this development session. Then I run the SAVEPOINT command.

Figure 2. Created SAVEPOINT.

      I will now continue the work by updating the calculation to .095 and running the SQL script for that subset of employees under consideration. You can easily see how employees like Christopher have a new merit increase test value. In aggregate, I decide that the increase for the entire employee subset is not yet budget supported so I return the data back to the original values in the system when SAVEPOINT merit1 was created.

Figure 3. Updating script and subsequent output.

Rolling back the data to the SAVEPOINT

            All I need to do to return the data to the pre-merit1 SAVEPOINT values is to enter the command into the system to rollback the data up to the merit1 SAVEPOINT. The system will then respond with "Rollback complete". See Figure 4.

Figure 4. Rolling back the data to the specified SAVEPOINT.

            We can then verify the data is in the correct state by verifying it using the script I used earlier before the .075 was changed to .095.

Figure 5. Data after the "ROLLBACK TO SAVEPOINT merit1" command.

Keep in mind that another scenario we could play out is to reduce other employees with higher bonus values or blend the subset with a budget compromising overall average. We might also consider budget-rebalancing ideas. Whatever the approach to be taken, we can use the SAVEPOINT feature in any transaction analysis to lockstep our way backwards as redevelopment dictates.

Video Highlights

Have an insightful day!
Robert Majdak, Co-Founder
Crystal Majdak, Co-Founder
Management Insights Team