oracle - 为什么 oracle 选择索引范围扫描而不是快速完整索引扫描

Question

我已经阅读了一些关于索引的文档，我做了一些例子，现在我有一些疑问。

我创建一个表并插入随机值，(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
...

文档说当所有查询值都在索引中时，这些值是从索引(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      |       |       |       |
--------------------------------------------------------------------

我必须指定优化器选择 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 |
--------------------------------------------------------------------

另一方面，这个例子展示了 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                |       |       |
--------------------------------------------------------------------------------

我的问题是，在第一个示例中，为什么 Oracle 选择索引范围扫描而不是快速完整索引扫描。

Answer 1

由于 SQL 语句的 WHERE 子句，您正在执行 INDEX RANGE SCAN:

select a,b,c from demo_full_index_scan where a = 1;

我在这里假设尽管列是唯一的，但您在 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);

由于您没有 UNIQUE 索引，因此 Oracle 无法确定 A 中的值始终是唯一的；但是，Oracle 确实知道 A 是索引中的第一列，并且可以在索引中可用的值范围内找到该值。

如果您的 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 |
-------------------------------------------------------------------------------------------