为了举例,我们假设有3个表:
体育项目
ID SELLER_ID NAME COST DIMENSIONS WEIGHTDIGITAL_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 WEIGHTDIGITAL_ITEM
ID SELLER_ID NAME COST DOWNLOAD_PATHSELLER
ID NAMEItem 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 IDQuery 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.IDQuery 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.
更多推荐
发布评论