l*b
2 楼
Hi, friends,
I will make some table replications between two DBs with same structure. The
main problem so far is how to prevent the PK conflict when replicating as
the current PKs are auto increased by sequence. My first thinking is the
have composite PK keys: instance id + auto increased, e.g. 01 and 1234 as a
PK pair, 01 is instance id and 1234 is current increased PK. I heard there
are some drawback in composite PK key, e.g. in ORM, etc. However, I am not
using ORM so far. Is my solution ok?
I will make some table replications between two DBs with same structure. The
main problem so far is how to prevent the PK conflict when replicating as
the current PKs are auto increased by sequence. My first thinking is the
have composite PK keys: instance id + auto increased, e.g. 01 and 1234 as a
PK pair, 01 is instance id and 1234 is current increased PK. I heard there
are some drawback in composite PK key, e.g. in ORM, etc. However, I am not
using ORM so far. Is my solution ok?
i*a
4 楼
Ms sql server? Replication takes care of pk automatically. In fact it
requires a pk for repl to work
requires a pk for repl to work
v*r
6 楼
I guess your concerns is that your target db is inserted not only by the
replication process, so the the target db sequence might clash with the
source db sequence.
The workaround is simple, modify your target db sequence to start with a
much higher value to ensure the source/target db sequences are using
different range.
Your thought will work but the problem is you are making your application un
-scalable by "instance id + auto increased".
replication process, so the the target db sequence might clash with the
source db sequence.
The workaround is simple, modify your target db sequence to start with a
much higher value to ensure the source/target db sequences are using
different range.
Your thought will work but the problem is you are making your application un
-scalable by "instance id + auto increased".
l*b
8 楼
Thank you, vbitter.
Could you advise me some examples of making your application unscalable by "
instance id + auto increased"?
un
【在 v*****r 的大作中提到】
: I guess your concerns is that your target db is inserted not only by the
: replication process, so the the target db sequence might clash with the
: source db sequence.
: The workaround is simple, modify your target db sequence to start with a
: much higher value to ensure the source/target db sequences are using
: different range.
: Your thought will work but the problem is you are making your application un
: -scalable by "instance id + auto increased".
Could you advise me some examples of making your application unscalable by "
instance id + auto increased"?
un
【在 v*****r 的大作中提到】
: I guess your concerns is that your target db is inserted not only by the
: replication process, so the the target db sequence might clash with the
: source db sequence.
: The workaround is simple, modify your target db sequence to start with a
: much higher value to ensure the source/target db sequences are using
: different range.
: Your thought will work but the problem is you are making your application un
: -scalable by "instance id + auto increased".
v*r
10 楼
Luckybb,
On second thought, I was wrong. Sequence helps you Apps scale better mainly
because it can be cached in memory. So as long as your sequence is defined
with CACHE option and you are inserting right after append "instance_id"
with "sequence number" on preparing PK value, there will be a little bit
more overhead in preparing PK value, but it won't affect your application's
scalability.
"
【在 l*****b 的大作中提到】
: Thank you, vbitter.
: Could you advise me some examples of making your application unscalable by "
: instance id + auto increased"?
:
: un
On second thought, I was wrong. Sequence helps you Apps scale better mainly
because it can be cached in memory. So as long as your sequence is defined
with CACHE option and you are inserting right after append "instance_id"
with "sequence number" on preparing PK value, there will be a little bit
more overhead in preparing PK value, but it won't affect your application's
scalability.
"
【在 l*****b 的大作中提到】
: Thank you, vbitter.
: Could you advise me some examples of making your application unscalable by "
: instance id + auto increased"?
:
: un
l*b
12 楼
thanks for quick and deep answer. Besides this generated PK, I am also
thinking one more solution to have composite primary key. I will have two
columns - instance_id and big int sequence number, then define them as a
composite primary key.
mainly
s
【在 v*****r 的大作中提到】
: Luckybb,
: On second thought, I was wrong. Sequence helps you Apps scale better mainly
: because it can be cached in memory. So as long as your sequence is defined
: with CACHE option and you are inserting right after append "instance_id"
: with "sequence number" on preparing PK value, there will be a little bit
: more overhead in preparing PK value, but it won't affect your application's
: scalability.
:
: "
thinking one more solution to have composite primary key. I will have two
columns - instance_id and big int sequence number, then define them as a
composite primary key.
mainly
s
【在 v*****r 的大作中提到】
: Luckybb,
: On second thought, I was wrong. Sequence helps you Apps scale better mainly
: because it can be cached in memory. So as long as your sequence is defined
: with CACHE option and you are inserting right after append "instance_id"
: with "sequence number" on preparing PK value, there will be a little bit
: more overhead in preparing PK value, but it won't affect your application's
: scalability.
:
: "
v*r
14 楼
Not a good idea. Having a separate instance_id column, you are adding
something that is totally irrelevant and redundant to your table entity.
something that is totally irrelevant and redundant to your table entity.
相关阅读
请教一个sql问题 MySQL Quietly Drops Support For Debian Linux(ZZ) (转载)心急如焚,升级MT后MySQL的数据全变乱码了新手土问题:现在还用Pro* C 调用,访问SQL数据库吗?MySQL的InnoDB表不支持full text index 怎么办?Oracle 看来还有很长的路请教一个JDBC的问题 getTableName()help on this query怎样快速得到两个表的交集Data warehouse textbookcount distinct在Access不能用?如何在SYBASE里面show all tables?请问关于tpc-H生成的data读取问题,数据库的大侠们帮帮忙啊!请各位帮我看看这个最简单的Stored Procedure (转载)SQL问题请教借人气一问ACCESS的测试题新手求救:如何给我sort完的表填加一列自然数显示order?菜鸟一问!-望解答!MySQL和C的接口?