在共享第3个表中按值过滤来自2个表的数据的最佳方法(Best way to filter union of data from 2 tables by value in shared 3rd tabl

在共享第3个表中按值过滤来自2个表的数据的最佳方法(Best way to filter union of data from 2 tables by value in shared 3rd table)

为了举例，我们假设有3个表：

体育项目

ID SELLER_ID NAME COST DIMENSIONS WEIGHT

DIGITAL_ITEM

ID SELLER_ID NAME COST DOWNLOAD_PATH

卖方

ID NAME

两个项目表中的项目ID保证唯一。 我想按顺序选择带有类型标签的给定卖家的所有商品ID。 我想出来了：

查询A.

SELECT PI.ID AS ID, 'PHYSICAL' AS TYPE FROM PHYSICAL_ITEM PI JOIN SELLER S ON PI.SELLER_ID = S.ID WHERE S.NAME = 'name' UNION SELECT DI.ID AS ID, 'DIGITAL' AS TYPE FROM DIGITAL_ITEM DI JOIN SELLER S ON DI.SELLER_ID = S.ID WHERE S.NAME = 'name' ORDER BY ID

查询B.

SELECT ITEM.ID, ITEM.TYPE FROM (SELECT ID, SELLER_ID, 'PHYSICAL' AS TYPE FROM PHYSICAL_ITEM UNION SELECT ID, SELLER_ID, 'DIGITAL' AS TYPE FROM DIGITAL_ITEM) AS ITEM JOIN SELLER ON ITEM.SELLER_ID = SELLER.ID WHERE SELLER.NAME = 'name' ORDER BY ITEM.ID

查询A似乎是最有效的，但它也看起来不必要地重复（对同一个表有2个表连接，在同一个表列上有2个子句）。 查询B对我来说看起来更干净（没有重复），但它看起来效率也低得多，因为它有一个子查询。 有没有办法让两全其美，可以这么说？

For sake of example, let's assume 3 tables:

PHYSICAL_ITEM

ID SELLER_ID NAME COST DIMENSIONS WEIGHT

DIGITAL_ITEM

ID SELLER_ID NAME COST DOWNLOAD_PATH

SELLER

ID NAME

Item IDs are guaranteed unique across both item tables. I want to select, in order, with a type label, all item IDs for a given seller. I've come up with:

Query A

SELECT PI.ID AS ID, 'PHYSICAL' AS TYPE FROM PHYSICAL_ITEM PI JOIN SELLER S ON PI.SELLER_ID = S.ID WHERE S.NAME = 'name' UNION SELECT DI.ID AS ID, 'DIGITAL' AS TYPE FROM DIGITAL_ITEM DI JOIN SELLER S ON DI.SELLER_ID = S.ID WHERE S.NAME = 'name' ORDER BY ID

Query B

SELECT ITEM.ID, ITEM.TYPE FROM (SELECT ID, SELLER_ID, 'PHYSICAL' AS TYPE FROM PHYSICAL_ITEM UNION SELECT ID, SELLER_ID, 'DIGITAL' AS TYPE FROM DIGITAL_ITEM) AS ITEM JOIN SELLER ON ITEM.SELLER_ID = SELLER.ID WHERE SELLER.NAME = 'name' ORDER BY ITEM.ID

Query A seems like it would be the most efficient, but it also looks unnecessarily duplicative (2 table joins to the same table, 2 where clauses on the same table column). Query B looks cleaner in a way to me (no duplication), but it also looks much less efficient, since it has a subquery. Is there a way to get the best of both worlds, so to speak?

最满意答案

在这两种情况下，用union all替换union all 。 Union不必要地删除重复项。

我希望Query A更有效率，因为优化器在进行连接时有更多的信息（尽管我认为Oracle甚至在联合后使用索引也相当不错）。 此外，第一个查询减少了联合之前的数据量。

然而，这只是一种意见。 真正的测试是对两个查询进行计时 - 多次以避免缓存填充延迟 - 以查看哪个更好。

In both cases, replace the union with union all. Union unnecessarily removes duplicates.

I would expect Query A to be more efficient, because the optimizer has more information when doing the join (although I think Oracle is pretty good with using indexes even after a union). In addition, the first query reduces the amount of data before the union.

This is, however, only an opinion. The real test is to time the two queries -- multiple times to avoid cache fill delays -- to see which is better.