• Ivanova - Monday, January 7, 2019 1:24 AM

    In a previous role, I used synonyms to allow us to keep a fraud detection application running 24x7 with regular updates of 3rd party supplied data (known dodgy IP addresses, etc). There were two identical sets of tables, call them setA and setB with each table being referenced by a synonym. The application used the synonym at all times. When it was time to load the data, we cleared down the tables not currently in use, populated them with the latest supplied data, then redefined the synonyms to point to the newly-loaded set of tables. A metadata table kept track of which version of each table was current at any time and supported a completely automated refresh and switch process.

    The main issue I've seen with synonyms is DBA suspicion; those who think synonyms are an unnecessary complication are not motivated to support a database which uses them, and they are apt to blame the synonyms as soon as anything goes wrong.

    Heh... no suspicion on the part of this DBA.  In fact, I won't allow for anything more than 2 part naming in any of the databases on boxes that I'm responsible for.

    And the use of "table flopping" as you've described is something I do all the time, as well, to keep tables online while a sister table is being loaded as a replacement.  In fact, I'm the one that finally got people to start doing that when large sets of tables needed to be copied from external sources (IBM Power Systems, in this case).  It's extremely effective and, if there's a failure during the load, you're still "in business" with the old data.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)