多个表上的 SQL 索引，可以做到吗？

SQL Index on Multiple tables, can it be done?

提问by Christopher Bonitz

Been searching for a solution for a while now,

找了好久的解决办法，

go to (1) or (2) to skip description, first i will explain the situation.

转到（1）或（2）跳过描述，首先我将解释情况。

My firm have upgraded our erp. system, my primary work is to create lists used by others in the firm, i take all my data from this systems database during upgrade we got some data converted to match the new version, some of it was left behind, some of it was not tampered with and just directly exported to the new database, its on a separate server, basically its a success, the new ERP. system works as supposed, however a lot of my lists have been broken, the data my lists use is missing/partly_missing/all_there

我公司已经升级了我们的 erp。系统，我的主要工作是创建公司其他人使用的列表，我在升级过程中从这个系统数据库中取出我的所有数据，我们转换了一些数据以匹配新版本，其中一些被留下了，一些没有篡改直接导出到新的数据库，在单独的服务器上，基本上就成功了，新的ERP。系统按预期工作，但是我的很多列表都被破坏了，我的列表使用的数据丢失/partly_missing/all_there

okay so the problem is missing data i need from the old database, okay a union on new and old database should be able to do that, however i do not want duplicate records, "data that was converted to the new database also exist in the old database" therefore two fields could exist "they do, i tried it"

好的，所以问题是缺少旧数据库中我需要的数据，好的，新旧数据库上的联合应该能够做到这一点，但是我不想重复记录，“转换到新数据库的数据也存在于旧数据库”因此可能存在两个字段“它们确实存在，我试过了”

so 2. version of my solution

所以 2. 我的解决方案的版本

i am lacking primary keys "iseries database" so i went concatting a combination of feilds making a uniqe key, "takes too long to explain how i did that" however it ends up in me making a view with a union on two databases, making sure no records exist two times,

我缺少主键“iseries 数据库”，所以我将字段的组合连接起来制作一个唯一键，“解释我是如何做到的时间太长了”，但最终我在两个数据库上创建了一个联合视图，使得确定没有记录存在两次，

(1) so this is what i got now, a view of the combination of old and new table data all built with checks on a "uniqe" key.... every time i need data that has been effected of the upgrade i must run a expensive query on each table, "some using these views more than 40 times" (Question1) how can i "cost effective" take data from two different schemas/databases and bind together?

(1) 所以这就是我现在得到的，旧表和新表数据的组合视图，所有表数据都通过对“uniqe”键的检查构建......每次我需要升级影响的数据时，我必须在每个表上运行一个昂贵的查询，“有些人使用这些视图超过 40 次”（问题 1）我如何“具有成本效益”从两个不同的模式/数据库中获取数据并将其绑定在一起？

(2) the only thing i can think of giving me this performance is to make indexes instead of these views that i built, however until now i haven't been able to find any information on how to, (Question2) Can i create a index over two tables,

(2) 我唯一能想到给我这种性能的是制作索引而不是我构建的这些视图，但是直到现在我还没有找到任何关于如何做的信息，(问题 2) 我可以创建一个索引两个表，

my database is as/400 - iseries however i am interested in a solution up against any database type, i am very flexible with resources

我的数据库是 as/400 - iseries 但是我对针对任何数据库类型的解决方案感兴趣，我对资源非常灵活

:EDIT: code that is used to create view with slight modification,

:EDIT: 用于创建略有修改的视图的代码，

SELECT

CTCONO,
CTDIVI,
CTSTCO,
CTSTKY,
CTLNCD,
CTTX40,
CTTX15,
CTPARM,
CTTXID,
CTRGDT,
CTRGTM,
CTLMDT,
CTCHNO,
CTCHID

FROM NEWDB.CSYTAB
UNION
SELECT * FROM OLDDB.CSYTAB

WHERE ( CTCONO,CTDIVI,CTSTCO,CTSTKY,CTLNCD ) NOT IN
(
SELECT A.CTCONO,A.CTDIVI,A.CTSTCO,A.CTSTKY,A.CTLNCD FROM NEWDB.CSYTAB A, OLDDB.CSYTAB B
WHERE A.CTCONO = B.CTCONO
AND A.CTDIVI = B.CTDIVI
AND A.CTSTCO = B.CTSTCO
AND A.CTSTKY = B.CTSTKY
AND A.CTLNCD = B.CTLNCD

)

采纳答案by Seph

Make a new table that stores the value of your expensive query, then if you're going to always ignore the older data in general if there's a record in the new DB for it. Then just add in some triggers to update this new table when the other tables get updated.

制作一个新表来存储昂贵查询的值，然后如果新数据库中有记录，您将始终忽略旧数据。然后只需添加一些触发器以在其他表更新时更新此新表。

Perhaps, a better question would be to provide your schema, and current expensive query then ask for people to help make it faster.

也许，更好的问题是提供您的架构和当前昂贵的查询，然后请求人们帮助使其更快。

Edit: now you have posted your table I see one thing you could improve, make the second part of your query this:

编辑：现在您已经发布了您的表格，我看到您可以改进的一件事，将查询的第二部分设为：

...
UNION
SELECT * FROM OLDDB.CSYTAB B
WHERE NOT EXISTS(
    SELECT TOP 1 1 
    FROM NEWDB.CSYTAB A
    WHERE A.CTCONO = B.CTCONO
        AND A.CTDIVI = B.CTDIVI
        AND A.CTSTCO = B.CTSTCO
        AND A.CTSTKY = B.CTSTKY
        AND A.CTLNCD = B.CTLNCD
    )

Then provided you have a single index that spans { CTCONO,CTDIVI,CTSTCO,CTSTKY,CTLNCD } in the NEWDB.CSYTAB then it should be much better performance than what you're getting currently.

然后，如果您在 NEWDB.CSYTAB 中有一个跨越 { CTCONO,CTDIVI,CTSTCO,CTSTKY,CTLNCD } 的单个索引，那么它的性能应该比您目前获得的要好得多。

回答by Christopher Bonitz

The answer is no.

答案是不。

I can't create a index on multiple tables.

我无法在多个表上创建索引。

My solution would be to make a table instead, and in my case a view also, or I could just optimize the SQL code.

我的解决方案是创建一个表，在我的情况下也是一个视图，或者我可以优化 SQL 代码。

EDIT

编辑

I just learned that in MS (at least the 2012 version) you can create indexed views. So in my case I would turn my views into indexed views, and get a huge performance upgrade.

我刚刚了解到，在 MS（至少是 2012 版）中，您可以创建索引视图。所以就我而言，我会将我的视图转换为索引视图，并获得巨大的性能提升。