| Swap Partition or Recreate Synonym [message #448392] |
Tue, 23 March 2010 02:36  |
beetel
Messages: 96
Registered: April 2007
|
Member |
|
|
Hi,
We have a datamart fact table which contains 5000 on the average. The ETL currently does truncate-then-insert into this table. This is not safe since if our reports run during the ETL window, it is possible that the fact table is empty (due to truncate) or not yet completely loaded.
The approaches we have in mind is:
1) Use a synonym pointing to the fact table - let's call this fact1. There will be another fact table, fact2. The ETL will populate fact2 and after population completes, the synonym will have to point to fact2. With this, the reports can read data from fact1 while fact2 gets populated. In the next cycle, vice-versa happens. Fact1 gets populated while the synonym points to fact2. Once fact1 gets refreshed, we again make the synonym point to it. So we are recreating the synonym to change the table it is pointing to.
2) Use swap partition. We make the fact table as one partition, possibly by choosing one date column and use date range less than year 9999. We also have a temp table which has the same structure as the fact table. During the ETL, we populate the temp table (while the fact table is accessible by the reports). Once the load to the temp table is complete, we swap its partition with that of the fact.
Please let me know which approach is better.
Thanks!
|
|
|
|
| Re: Swap Partition or Recreate Synonym [message #448405 is a reply to message #448392] |
Tue, 23 March 2010 04:13  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - as I understand it, the issue is that if you were to use truncate and insert, because these are separate transactions there would be a short time (after the truncation and before committing the insert) when queries would see no rows.
Your partition exchange solution does look rather elegant, but is it actually necessary? 5000 rows is not very many, so why not simply delete-insert-commit? I usually try to avoid DDL on production systems.
[Updated on: Tue, 23 March 2010 04:17] Report message to a moderator |
|
|
|
|
|
| Re: Swap Partition or Recreate Synonym [message #448417 is a reply to message #448392] |
Tue, 23 March 2010 05:06 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'd go with John's suggestion, it's probably safest.
Your two suggestions are only ever going to be needed when your doing a load process that's going to take a very long time and 5000 rows should take seconds.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
