英文:
how to uniquely identify rows in two table copies
问题
我有两个本质上是彼此的副本的表格。其中一个是动态的，一些 DML 语句经常发生，所以这个表格用作阶段表，另一个用作同步来自这个阶段表的更改的方法。因此，这些表格在不同的时间可以有不同的数据，我使用合并语句来同步这些表格。大致如下：
MERGE INTO source s
USING (
SELECT
*
FROM
stage st
) se ON ( s.eim_product_id = st.eim_product_id )
...
问题在于 eim_product_id 既不是主键也不是唯一的。因此，我的合并语句实际上会引发以下错误：
错误报告 -
ORA-30926: 无法从源表中获取一组稳定的行
我能想到的伪列之一是像 id_seq INTEGER GENERATED ALWAYS AS IDENTITY 这样的标识列，或者 rowid。然而，问题在于这种方法无法在两个表格之间一致地唯一标识行，对吗？我相信我需要某种哈希来完成这项工作，但不确定在这种情况下什么是最好和最简单的方法。
英文:
I have essentially two tables that are copies of each other. One is dynamic and some DML statements happen quite constantly, so this table serve as a stage table, the other is used as a way to synchronize the changes form this stage table. So the tables can have different data at different times, and I use a merge statement to sync the tables. Something along these lines:
MERGE INTO source s
USING (
SELECT
*
FROM
stage st
) se ON ( s.eim_product_id = st.eim_product_id )
...
The problem is that eim_product_id is neither a primary key, nor unique. So my merge statement essentially throws this error:
Error report -
ORA-30926: unable to get a stable set of rows in the source tables
And the only pseudo-columns I can think of to use is something like an identity column id_seq INTEGER GENERATED ALWAYS AS IDENTITY or a rowid. However, the problem is that it will not be consistent this approach to uniquely identify the row across both tables, right ? I believe I need some kind of hash that does the job , but unsure what would be the best and simplest approach in this case.
答案1
得分: 1
The rowid pseudo-column won't match between the tables, and isn't necessarily constant. Creating a hash could get expensive in terms of CPU; an updated row in the first table wouldn't have a matching hash in the second table for the merge to find. If you only generate the hash at insert and never update then it's just a more expensive, complicated sequence.
Your best bet is an identity column with a unique constraint on the first table, copied to the second table by the merge: it is unique, only calculated very efficiently once at insert, will always identify the same row in both tables, and need never change.
英文:
The rowid pseudo-column won't match between the tables, and isn't necessarily constant. Creating a hash could get expensive in terms of CPU; an updated row in the first table wouldn't have a matching hash in the second table for the merge to find. If you only generate the hash at insert and never update then it's just a more expensive, complicated sequence.
Your best bet is an identity column with a unique constraint on the first table, copied to the second table by the merge: it is unique, only calculated very efficiently once at insert, will always identify the same row in both tables, and need never change.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论