如何（快速）整理各种表中的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.

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

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.

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

Thoughts?

2 个解决方案

#1

Create a master table that contains only the ID:

创建仅包含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.

并使所有三个表都使用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.

此触发器应尝试将新ID插入master,并在PRIMARY KEY违规时静默失败。

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:

要删除,请使用:

DELETE
FROM    master
WHERE   id = @id

This will fire ON DELETE CASCADE and delete records from all three tables if any.

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

#2

Why not just do an outer join and then coalesce the columns from the component tables?

为什么不只是进行外连接,然后合并组件表中的列?

#1