为什么 oracle 选择索引范围扫描而不是快速完整索引扫描
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26306684/
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
Why oracle CHOOSE INDEX RANGE SCAN over FAST FULL INDEX SCAN
提问by Samardj
I have read some documentation about indexes, I did some examples and now I have some doubts.
我已经阅读了一些关于索引的文档，我做了一些例子，现在我有一些疑问。
I create a table and insert random values, (A column has unique values) column A NOT NULL I create an index on A, B, C. (B-TREE)
我创建一个表并插入随机值，（A 列具有唯一值）列 A NOT NULL 我在 A、B、C 上创建索引。（B-TREE）
SELECT COUNT(*) FROM DEMO_FULL_INDEX_SCAN;
=1000
SELECT * FROM DEMO_FULL_INDEX_SCAN;
A B C D E F
---------- ---------- ---------- ---------- ---------- ----------
1 7 109 1 1 1
2 12 83 2 2 2
3 21 120 3 3 3
4 13 74 4 4 4
5 2 1 5 5 5
...
Documentation says when all query values are in the index, the values are gathered from index (INDEX FAST FULL SCAN), but here optimizer is choosing another operation.
文档说当所有查询值都在索引中时，这些值是从索引（INDEX FAST FULL SCAN）收集的，但这里优化器正在选择另一个操作。
EXPLAIN PLAN FOR
SELECT A,B,C FROM DEMO_FULL_INDEX_SCAN WHERE A = 1;
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
|* 1 | INDEX RANGE SCAN | FIS_01 | | | |
--------------------------------------------------------------------
I have to specify a hint to optimizer choose INDEX FAST FULL SCAN (but i dont know why i have to specify it)
我必须指定优化器选择 INDEX FAST FULL SCAN 的提示（但我不知道为什么我必须指定它）
EXPLAIN PLAN FOR
SELECT /*+ INDEX_FFS(DEMO_FULL_INDEX_SCAN FIS_01) */A,B,C FROM DEMO_FULL_INDEX_SCAN WHERE A = 1;
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 |
|* 1 | INDEX FAST FULL SCAN| FIS_01 | 1 | 11 | 2 |
--------------------------------------------------------------------
By the other hand ,this examples shows what oracle documentation says. When there is a value in the query that is not in the index, this value is accessed by TABLE ACCESS BY INDEX ROWID
另一方面，这个例子展示了 oracle 文档所说的内容。当查询中存在索引中没有的值时，通过 TABLE ACCESS BY INDEX ROWID 访问该值
EXPLAIN PLAN FOR
SELECT D FROM DEMO_FULL_INDEX_SCAN WHERE A = 800;
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Co
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO_FULL_INDEX_SCAN | | |
|* 2 | INDEX RANGE SCAN | FIS_01 | | |
--------------------------------------------------------------------------------
My question is ,in the first example why Oracle CHOOSE INDEX RANGE SCAN over FAST FULL INDEX SCAN.
我的问题是，在第一个示例中，为什么 Oracle 选择索引范围扫描而不是快速完整索引扫描。
回答by Ben
You're performing an INDEX RANGE SCAN because of the WHERE clause of your SQL statement:
由于 SQL 语句的 WHERE 子句，您正在执行 INDEX RANGE SCAN：
select a,b,c from demo_full_index_scan where a = 1;
I'm assuming here that you don't have a unique index on A despite the uniqueness of the column, i.e. your table DDL is something like this:
我在这里假设尽管列是唯一的，但您在 A 上没有唯一索引，即您的表 DDL 是这样的：
create table demo_full_index_scan (
a number
, b number
, c number
, d number
);
create index i_demo_full_index_scan on demo_full_index_scan (a, b, c);
As you don't have a UNIQUE index Oracle can't know with certainty that the values in A will always be unique; however, Oracle does know that A is the first column in the index and can find this value in the range of values available in the index.
由于您没有 UNIQUE 索引，因此 Oracle 无法确定 A 中的值始终是唯一的；但是，Oracle 确实知道 A 是索引中的第一列，并且可以在索引中可用的值范围内找到该值。
If your WHERE clause were to attempt to filter based on the column C you would perform an INDEX FULL SCAN as C exists in the index, so you don't need to access the table, but it is not the first column in the index:
如果您的 WHERE 子句尝试根据 C 列进行过滤，您将执行 INDEX FULL SCAN 因为 C 存在于索引中，因此您不需要访问该表，但它不是索引中的第一列：
explain plan for select a,b,c from demo_full_index_scan where c = 1;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | I_DEMO_FULL_INDEX_SCAN | 1 | 39 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
