多个表上的SQL索引可以完成吗？ [英] SQL Index on Multiple tables, can it be done?

问题描述

现在一直在寻找解决方案，

Been searching for a solution for a while now,

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

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

我的公司升级了我们的erp。系统，我的主要工作是创建公司其他人使用的列表，我在升级过程中从这个系统数据库获取所有数据
我们得到了一些转换为与新版本相匹配的数据，其中一些被遗忘，一些它没有被篡改，只是直接导出到新的数据库，
它在一个单独的服务器上，
基本上是它的成功，新的ERP。系统按照假设工作，
然而我的很多列表都被打破了，
我的列表使用的数据缺失/ partial_missing / all_there

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

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

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"

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

so 2. version of my solution

我缺少主键iseries数据库，所以我去了一个组合使用uniqe键的组合，花了太长时间来解释我是怎么做的
然而它最终在我用一个视图在两个数据库上联合，确保没有记录存在两次，

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,

（1）
所以这就是我现在得到的，
a查看旧表和新表数据的组合
全部使用uniqe键检查构建....
每次我需要的数据都是有效的升级的时候我必须在每个表上运行一个昂贵的查询，有些人使用这些视图超过40次
（问题1）我怎样才能节省成本从两个不同的模式/数据库中获取数据并绑定在一起？

(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?

（2）
我唯一可以想到给我这个表现的是制作索引而不是我建造的这些视图，但是直到现在我还没有我能找到任何关于如何获得的信息，
（问题2）我可以在两个表上创建一个索引，

(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,

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

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

：编辑：
代码用于创建略微修改的视图，

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

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

)

推荐答案

创建一个存储昂贵查询价值的新表，如果你要经常如果新数据库中有记录，则忽略旧数据。然后在其他表更新时添加一些触发器来更新这个新表。

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.

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

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
    )

如果您在NEWDB.CSYTAB中有一个跨越{CTCONO，CTDIVI，CTSTCO，CTSTKY，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.

这篇关于多个表上的SQL索引可以完成吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！