This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Sep 10, 2013 2:16 AM by BluShadow RSS

Sync between two tables.

995371 Newbie
Currently Being Moderated

Hi Experts,

 

I have small requirement as below,can you people suggest me?

 

Table A(X schema)   Table B(Y schema)

 

How to get sync between these tables...these tables should have same data and i have already tried triggers but its impacting another process..

so i'm unable to use triggers here.....

And people may say that why can't use single table for both the schemas,but there will be performance issue because there are  many jobs will access the tables.

 

Please let me know if any other solutions are there?

 

 

Thanks in advance...!!!

 

 

 

 

 

 


  • 1. Re: Sync between two tables.
    Chris Hunt Journeyer
    Currently Being Moderated
    How to get sync between these tables...these tables should have same data and i have already tried triggers but its impacting another process..

     

    That depends on how they are updated, by whom, and how often. How do you propose to handle the case when users X and Y simultaneously change their copies of the table to different values? Life will be much easier if one schema holds a read/write master copy and the other has a read-only copy of it. It'll be easier still if you just have a single table to which both schemas have access...

     

    And people may say that why can't use single table for both the schemas,but there will be performance issue because there are  many jobs will access the tables.

     

    A bigger performance issue than writing some constantly-running  complex replication routine to keep two separate tables in sync? No, not at all. Use a single table. Being accessed by "many jobs" is what they're for.

  • 2. Re: Sync between two tables.
    995371 Newbie
    Currently Being Moderated

    But its an existing process,i can't modify the design of the actual process....The only thing i can suggest..but i hopes it wont much worthfull in my case...

     

    The tables being used by the Business.so different people may update the tables.

  • 3. Re: Sync between two tables.
    Chris Hunt Journeyer
    Currently Being Moderated

    So have the table in schema X, and make a synonym in schema Y that points to it. Anybody that currently accesses SchemaY.TableB will now actually be accessing SchemaX.TableA. No changes required in your existing code.

  • 4. Re: Sync between two tables.
    EdStevens Guru
    Currently Being Moderated

    995371 wrote:

     

    Hi Experts,

     

    I have small requirement as below,can you people suggest me?

     

    Table A(X schema)   Table B(Y schema)

     

    How to get sync between these tables...these tables should have same data and i have already tried triggers but its impacting another process..

    so i'm unable to use triggers here.....

    And people may say that why can't use single table for both the schemas,but there will be performance issue because there are  many jobs will access the tables.

     

    Please let me know if any other solutions are there?

     

     

    Thanks in advance...!!!

     

     

     

     

     

     

     

    The best way to keep two tables 'in synch' is to eliminate one of them.  ALL attempts to keep two different tables in synch at all times are futile.   This is fundamental RDBMS design ... Do Not Duplicate Data (tm).  That applies at all levels .. not only within a schema but across schemas as well.

  • 5. Re: Sync between two tables.
    995371 Newbie
    Currently Being Moderated

    But there is small problem that...people are inserting records with same details in different ways...

    so its taking as new one and its missing consistency.

  • 6. Re: Sync between two tables.
    BluShadow Guru Moderator
    Currently Being Moderated

    995371 wrote:

     

    But there is small problem that...people are inserting records with same details in different ways...

    so its taking as new one and its missing consistency.

     

    What does that mean?

     

    You do have primary keys and necessary constraints in place to prevent duplication of data don't you?

    How are they inserting "in different ways"... and why are people inserting the same data as each other?

     

    I agree with the other experts, by trying to keep a copy of the data and keep that in sync, you're just creating more of a problem... and talking of problems, what is the actual problem you are trying to solve by doing this?

  • 7. Re: Sync between two tables.
    Priyasagi Pro
    Currently Being Moderated

    Hi,

     

    You can create snapshots

     

    Example

    CREATE SNAPSHOT sales.customers AS SELECT * FROM sales.customers@hq.acme.com

     

    Read the following link it will help you:

    Database Replication

  • 8. Re: Sync between two tables.
    BluShadow Guru Moderator
    Currently Being Moderated

    Priyasagi wrote:

     

    Hi,

     

    You can create snapshots

     

    Example

    CREATE SNAPSHOT sales.customers AS SELECT * FROM sales.customers@hq.acme.com

     

    Read the following link it will help you:

    Database Replication

     

    Jeez... what version are you on?

    version 8 has been dead for longer than I can remember (not true, I can remember, but it's a long time ago).

    They've not been called snapshots in a long time.... they're now materialized views.

     

    But why duplicate the data anyway... unless a materialzed view of the data is actually required, which the OP hasn't indicated as necessary.

     

    Less overhead is done if everyone just accesses the same table.

  • 9. Re: Sync between two tables.
    TanzaniteInfotech Newbie
    Currently Being Moderated

    Yo can do one thing that is:

     

    Just Transfer Transfer the entire table like this:


    mysqldump [options] sourcedatabase tablename \
     
    | mysql [options] --host remoteserver --user username ...


    And then try to transfer the table with MySQL:


    # same as above, mysql has the "-C" flag


    Transfer by using SSH encryption & compression; mysql is executed remotely


    mysqldump [options] sourcedatabase tablename \
     
    | ssh -C user@remoteserver 'mysql [options]'


    And then transfer by using intermediate SQL file & rsync to transfer only modifications


    mysqldump [options] sourcedb tbl > dump.sql
    rsync
    [-z] dump.sql user@remoteserver:/path/to/remote/dump.sql
    ssh
    user@remoteserver "mysql [options] < /path/to/remote/dump.sql"


    All are simple table overwrites, remote data is LOST and replaced by the master copy.

    To optimize the insert stage too, either you go for full MySQL replication, This may require alterations to the table structure, e.g. adding "last-synced-on" and "needs-deleting" columns, or even introduction of ancillary tables.

    I hope, now you can fix ur questions.

    Tanzanite Infotech


  • 10. Re: Sync between two tables.
    BluShadow Guru Moderator
    Currently Being Moderated

    Since when did this become a MySQL forum?

     

    ORACLE != MySQL (even if the Oracle company does own MySQL)

  • 11. Re: Sync between two tables.
    TanzaniteInfotech Newbie
    Currently Being Moderated

    Right ! but you didn't mentioned here and your was looked like this ...

  • 12. Re: Sync between two tables.
    BluShadow Guru Moderator
    Currently Being Moderated

    Not sure what planet you're on, but these are the Oracle forums for Oracle products and this is the Oracle SQL and PL/SQL forum..... absolutely nothing to do with MySQL... never has been.

    ... and it's not up to me to mention it.

  • 13. Re: Sync between two tables.
    Pablolee Journeyer
    Currently Being Moderated

    TanzaniteInfotech wrote:

     

    Right ! but you didn't mentioned here

    Not clear what you're trying to say here. Are you implying that it is not clear that this is an Oracle forum? I'd have to disagree.

     

    TanzaniteInfotech wrote:

    and your was looked like this ...

    Nope, no clue what this means. Can you clarify please?

  • 14. Re: Sync between two tables.
    TanzaniteInfotech Newbie
    Currently Being Moderated

    I know this oracle forum. thanx to make understand

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points