索引在NOT IN或<>条款？ [英] Do indexes work in NOT IN or <> clause?
问题描述
我已经读过（atleast Oracle）数据库中的正常索引基本上是B树结构，因此存储处理适当根节点的记录。小于根的记录被迭代地存储在树的左部分中，而大于根的记录被存储到右部分。
I have read that normal indexes in (atleast Oracle) database are basically B- tree structures, and hence store the records treating appropriate root nodes. Records 'lesser than' the root are iteratively stored in the left portion of the tree, while records 'greater than' the root are stored to the right portion. It is this storage approach that helps in a faster scan, through tree traversal since depth and breadth is reduced.
但是，在创建索引或调整<$的性能时，可以通过树遍历来更快地扫描c $ c>其中子句，大多数指南首先优先考虑（IN或=子句）移动到具有不等式子句的列。 （NOT IN，<>）。这个建议的原因是什么？如果使用树遍历来预测给定值不像预测给定值那样容易存在是不可行的？
However,while creating indexes or for performance tuning of a where clause, most guides speak about first prioritize the columns where equality is to be considered (IN or = clause) and then alone move to the columns with inequality clauses. (NOT IN, <>). What is the cause of this advise? Should it not be feasible to predict that a given value does not exist as easily as it is to predict a given value exists, using tree traversal?
索引不工作否定？
推荐答案
问题是索引中的位置。如果你有两列，col1中的字母和col2中的数字，那么索引可能如下：
The issue is locality within the index. If you have two columns with letters in col1 and numbers in col 2, then an index might look like:
Ind col1 col2
1 A 1
2 A 1
3 A 1
4 A 2
5 B 1
6 B 1
7 B 2
8 B 3
9 B 3
10 C 2
11 C 3
（ ind 是索引中的位置，记录定位器被省略。）
(ind is the position in the index. The record locator is left out.)
如果你正在寻找 col1 ='B'，那么你可以找到位置5，然后扫描索引直到位置9.如果你正在寻找 col1<> 'B'，那么您需要找到不是'B'的第一条记录，然后重复第一条记录。 IN 和 NOT IN 会变得更糟。
If you are looking for col1 = 'B', then you can find position 5 and then scan the index until position 9. If you are looking for col1 <> 'B', then you need to find the first record that is not 'B' scan and repeat for the first record after. This becomes worse with IN and NOT IN.
另一个因素是，如果相对少数的记录满足等式条件，则几乎所有记录都将失败 - 并且当几乎所有记录都需要读取时，索引通常是无用的。有时候例外是聚集索引。
An additional factor is that if a relative handful of records satisfy the equality condition, then almost all records will fail -- and often indexes are not useful when almost all records need to be read. One sometimes-exception to this are clustered indexes.
Oracle比大多数数据库具有更好的索引优化 - 它将从不同位置开始执行多次扫描。即使如此，不平等对指数的影响也不大。
Oracle has better index optimizations than most databases -- it will do multiple scans starting in different locations. Even so, an inequality is often much less useful for an index.
这篇关于索引在NOT IN或<>条款？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
