如何（快速）整理各个表中的 ID？

发布于 2024-07-15 17:39:46 字数 518 浏览 5 评论 0原文

我有三个非规范化表，我必须从表面上看它们（数据来自某些外部资源）。这三个表有不同的定义，但它们各自从不同的角度描述同一个对象。

   object1  A  B
   object2  A
   object3     B  C
   object4        C

这些表之间的唯一共同点是它们的主键。我可以使用 SELECT UNION SELECT 将 ID 聚集在一起，但查询似乎相对较慢，即使每个表都有其 PK 字段索引。我可以创建一个视图来抽象此查询 vw_object_ids，但它以相同的速度执行。我以为我可以添加一个索引来具体化视图，但是在 SQL Server 2005 中，您无法使用 UNION 为视图建立索引。

我想要的是让 ID 的主索引与底层数据同步，这些数据可能随时更新或删除。我想我可以通过一组疯狂的触发器无限期地完成此任务，或者只是满足于未索引视图的速度。但我只是想确保我没有错过任何选项，或者这个场景是否有名称或表示某种模式。

想法？

原文

I have three denormalized tables that I have to take at face value (data comes from some external resource). The three tables have different definitions, but they each describe the same object from different perspectives.

   object1  A  B
   object2  A
   object3     B  C
   object4        C

The only commonality between these tables is their primary key. I can corral the IDs together using SELECT UNION SELECT, but the query seems relatively slow, even when each table has its PK field indexed. I could create a view to abstract this query, vw_object_ids, but it performs at the same speed. I thought I could add an index to materialize the view, but in SQL Server 2005, you can't index views with UNIONs.

What I want is to have a master index of IDs be in sync with with the underlying data, which may get updated or deleted whenever. I guess I could accomplish this indefinitely with a crazy set of triggers or just settle for the speed of the unindexed view. But I just wanted to make sure I'm not missing any options or if this scenario has a name or is indicative of a pattern.

Thoughts?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泼猴你往哪里跑 2024-07-22 17:39:46

创建一个仅包含 ID: 的主表，

CREATE TABLE master (ID INT NOT NULL PRIMARY KEY)

并使用 ON DELETE CASCADE 使所有三个表都引用该主表。

要首次填充该表，请执行以下操作：

INSERT
INTO    master
SELECT  id
FROM    a
UNION
SELECT  id
FROM    b
UNION
SELECT  id
FROM    c

要定期填充该表，请在三个表中的每一个上创建一个触发器。

此触发器应尝试将新的 ID 插入到 master 中，并在发生 PRIMARY KEY 违规时默默失败。

要查询，请使用：

SELECT  *
FROM    master m
LEFT OUTER JOIN
        a
ON      a.id = m.id
LEFT OUTER JOIN
        b
ON      b.id = m.id
LEFT OUTER JOIN
        c
ON      c.id = m.id

这将有效地使用索引。

要删除，请使用：

DELETE
FROM    master
WHERE   id = @id

这将触发 ON DELETE CASCADE 并删除所有三个表中的记录（如果有）。

Create a master table that contains only the ID:

CREATE TABLE master (ID INT NOT NULL PRIMARY KEY)

and make all three tables to refer to that master table with ON DELETE CASCADE.

To populate the table for the first time, issue

INSERT
INTO    master
SELECT  id
FROM    a
UNION
SELECT  id
FROM    b
UNION
SELECT  id
FROM    c

To populate the table on a regular basis, create a trigger on each of three tables.

This trigger should try to insert the new ID to master and silently fail on PRIMARY KEY violation.

To query, use:

SELECT  *
FROM    master m
LEFT OUTER JOIN
        a
ON      a.id = m.id
LEFT OUTER JOIN
        b
ON      b.id = m.id
LEFT OUTER JOIN
        c
ON      c.id = m.id

This will use indexes efficienty.

To delete, use: