使用减号 oracle 优化查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28784960/
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
optimize query with minus oracle
提问by user2989745
Wanted to optimize a query with the minus that it takes too much time ... if they can give thanked help.
想要优化一个查询，减去它需要太多时间......如果他们能提供帮助的话。
I have two tables A and B,
我有两个表 A 和 B，
Table A: ID, value
表 A：ID，值
Table B: ID
表 B：身
I want all of Table A records that are not in Table B. Showing the value. For it was something like:
我想要所有不在表 B 中的表 A 记录。显示值。因为它是这样的：
Select ID, value
FROM A
WHERE value> 70
MINUS
Select ID
FROM B;
Only this query is taking too long ... any tips how best this simple query?
只有这个查询花费的时间太长......任何提示如何最好地使用这个简单的查询？
Thank you for attention
感谢您的关注
回答by ZeroBased_IX
Are ID and Value indexed?
ID 和 Value 是否编入索引？
The performance of Minus and Not Exists depend:
Minus 和 Not Exists 的性能取决于：
It really depends on a bunch of factors.
A MINUS will do a full table scan on both tables unless there is some criteria in the where clause of both queries that allows an index range scan. A MINUS also requires that both queries have the same number of columns, and that each column has the same data type as the corresponding column in the other query (or one convertible to the same type). A MINUS will return all rows from the first query where there is not an exact match column for column with the second query. A MINUS also requires an implicit sort of both queries
NOT EXISTS will read the sub-query once for each row in the outer query. If the correlation field (you are running a correlated sub-query?) is an indexed field, then only an index scan is done.
The choice of which construct to use depends on the type of data you want to return, and also the relative sizes of the two tables/queries. If the outer table is small relative to the inner one, and the inner table is indexed (preferrable a unique index but not required) on the correlation field, then NOT EXISTS will probably be faster since the index lookup will be pretty fast, and only executed a relatively few times. If both tables a roughly the same size, then MINUS might be faster, particularly if you can live with only seeing fields that you are comparing on.
这真的取决于很多因素。
MINUS 将对两个表进行全表扫描，除非两个查询的 where 子句中都有一些允许索引范围扫描的条件。MINUS 还要求两个查询具有相同的列数，并且每一列与另一个查询中的相应列具有相同的数据类型（或一个可转换为相同类型的列）。MINUS 将返回第一个查询中的所有行，其中没有与第二个查询的列完全匹配的列。MINUS 还需要两种查询的隐式排序
NOT EXISTS 将为外部查询中的每一行读取一次子查询。如果相关字段（您正在运行相关子查询？）是索引字段，则仅完成索引扫描。
选择使用哪种构造取决于您要返回的数据类型，以及两个表/查询的相对大小。如果外部表相对于内部表较小，并且内部表在相关字段上建立索引（最好是唯一索引但不是必需的），那么 NOT EXISTS 可能会更快，因为索引查找将非常快，并且只有执行次数相对较少。如果两个表的大小大致相同，那么 MINUS 可能会更快，特别是如果您只能看到正在比较的字段。
Minus operator versus 'not exists' for faster SQL query - Oracle Community Forums
减号运算符与“不存在”的对比，以实现更快的 SQL 查询 - Oracle Community Forums
You could use NOT EXISTSlike so:
你可以NOT EXISTS像这样使用：
SELECT a.ID, a.Value
From a
where a.value > 70
and not exists(
Select b.ID
From B
Where b.ID = a.ID)
EDIT: I've produced some dummy data and two datasets for testing to prove the performance increases of indexing. Note: I did this in MySQL since I don't have Oracle on my Macbook.
编辑：我已经生成了一些虚拟数据和两个数据集用于测试以证明索引的性能提高。注意：我在 MySQL 中执行此操作，因为我的 Macbook 上没有 Oracle。
Table Ahas 2600 records with 2 columns: ID, val. ID is an autoincrement integer Val varchar(255)
表 A有 2600 条记录，有 2 列：ID、val。ID 是一个自增整数 Val varchar(255)
Table bhas one column, but more records than Table A. Autoincrement (in gaps of 3)
表 b有一列，但记录比表 A 多。自动增量（间隔为 3）
You can reproduce this if you wish: Pastebin - SQL Dummy Data
如果你愿意，你可以重现这个：Pastebin - SQL Dummy Data
Here is the query I will be using:
这是我将使用的查询：
select a.id, a.val from tablea a
where length(a.val) > 3
and not exists(
select b.id from tableb b where b.id = a.id
);
Without Indexes, the runtime is 986mswith 1685 rows.
如果没有索引，运行时间为986 毫秒，有 1685 行。
Now we add the indexes:
现在我们添加索引：
ALTER TABLE `tablea` ADD INDEX `id` (`id`);
ALTER TABLE `tableb` ADD INDEX `id` (`id`);
With Indexes, the runtime is 14mswith 1685 rows. That's 1.42%the time it took without indexes!
使用索引，运行时间为14 毫秒，有 1685 行。这是没有索引的时间的1.42%！
