Oracle SQL 查询性能改进：减去与不存在的地方
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12706207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL query performance improvement: minus vs where not exists
提问by Ion C
I need to improve the performance of this query:
我需要提高这个查询的性能：
Select
t1.column1, t1.column2
From
table1 t1, table2 t2
Where
t1.id = t2.id and
t1.some_column = t2.some_column
--------
minus
--------
Select
t1.column1, t1.column2
From
table1 t1, table2 t2, table3 t3
Where
t1.id = t2.id and
t1.some_column = t2.some_column and
t1.id = t3.id
I'm trying to rewrite this query using "not exists" instead of "minus". Can someone give me an advice?
我正在尝试使用“不存在”而不是“减号”来重写此查询。有人可以给我建议吗？
回答by bpgergo
how about this?
这个怎么样？
Select
t1.column1, t1.column2
From
table1 t1, table2 t2
Where
t1.id = t2.id
and t1.some_column = t2.some_column
and not exists (select 1 from table3 t3 where t1.id = t3.id)
回答by Gerardo Lima
It depends on the proportional size/#-of-rows between the first query and table3:
这取决于第一个查询和 table3 之间的比例大小/#-of-rows：
- If table3 has manyrows, I'd probably use @bpgergo solution, because it only needs to run the exceptquery a fewtimes and it should be fast (I assume there is an index over id);
- If table3 has fewrows, I'd use the following solution, because the subquery could be run all at once and do the match in a memory cache:
- 如果表3有许多行，我可能会使用@bpgergo的解决方案，因为它仅需要运行除了查询少数几次，它应该是快的（我假设有在ID索引）;
- 如果 table3 有几行，我会使用以下解决方案，因为子查询可以一次运行并在内存缓存中进行匹配：
SELECT t1.column1, t1.column2
FROM table1 t1, table2 t2
WHERE t1.id = t2.id
AND t1.some_column = t2.some_column
AND t1.id NOT IN (SELECT t3.id FROM table3 t3);
Either solution benefit from indexes on each table idcolumn and, if it is the case, additional indexes (or compound indexes containing the id) on the columns used to filter the first query.
两种解决方案都受益于每个表id列上的索引，如果是这种情况，还可以受益于id用于过滤第一个查询的列上的附加索引（或包含 的复合索引）。
