Home » RDBMS Server » Performance Tuning » Swap Partition or Recreate Synonym (10g )
Swap Partition or Recreate Synonym [message #448392] Tue, 23 March 2010 02:36 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 6396
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 #448416 is a reply to message #448405] Tue, 23 March 2010 04:59 Go to previous messageGo to next message
beetel
Messages: 96
Registered: April 2007
Member
Thanks, John. It really has nothing to do much with performance, but rather with maintenance and which approach is 'safer' than the other.
Re: Swap Partition or Recreate Synonym [message #448417 is a reply to message #448392] Tue, 23 March 2010 05:06 Go to previous message
cookiemonster
Messages: 12320
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.
Previous Topic: most accessed
Next Topic: Index Hit Retio
Goto Forum:
  


Current Time: Mon Sep 26 19:40:26 CDT 2016

Total time taken to generate the page: 0.21312 seconds