I've got a number of clients in the superannuation (aka retirement fund) industry. At many of these sites, there is a need to be able to do this:
- Using a web site, members must be able to see their own data pretty much 24×7
- Some latency is ok i.e. it might be ok for a member to see data up to yesterday, or up a point in time a few hours ago
- Refreshing the website data is not incremental, truncating the tables and reloading them is required
The challenge is: what happens if a member looks at the data during the time it's being refreshed?
I find that many customers don't realize that table partitioning is often the easiest answer for this. DBAs are often a bit scared of partitioning but this situation is very easy.
Take a look at the main image above.
There are two identical tables Live Data and Staged Data. Each only has one partition. The web site is being provided data by the Live Data table on the left. When a refresh needs to happen, they just need to do this:
- Truncate the Staged Data table.
- Refresh the Staged Data table with the new incoming data.
- Switch partition 1 between the two tables.
A partition switch is a metadata operation and often takes only milliseconds. The data is not being moved. The website will then immediately see the new data.
Note: If the website is insanely busy, you might have to kill the website traffic to make the switch happen. There are options that can help you do to this.