Oracle 基于索引插入的索引组织表功能的快速全扫描

我注意到，当它是INSERT的数据源时，我似乎无法强制对IOT上基于功能的索引进行快速全扫描。如果它只是一个选择，或者如果它是一个基于标准堆组织表的函数索引，则可以正常工作。有没有办法解决这个问题，或者只是一个已知的限制？

SQL> CREATE TABLE source_heap (A NUMBER(10), B CHAR(5));

Table created.

SQL> CREATE INDEX IX_source_heap ON source_heap (UPPER(B));

Index created.

SQL> CREATE TABLE source_iot (A NUMBER(10), B CHAR(5), CONSTRAINT PK_source_iotPRIMARY KEY (A)) ORGANIZATION INDEX;

Table created.

SQL> CREATE INDEX IX_source_iot ON source_iot (UPPER(B));

Index created.

SQL> CREATE TABLE dest (B CHAR(5));

Table created.

SQL> SET AUTOTRACE ON;
SQL> SELECT /*+ INDEX_FFS(source_heap IX_source_heap) */ UPPER(B) FROM source_heap WHERE UPPER(B) = 'hi';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2515621119

---------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                |     1 |     7 |     2   (0)| 00:00:01 |

|*  1 |  INDEX FAST FULL SCAN| IX_SOURCE_HEAP |     1 |     7 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("B")='hi')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

SQL> SELECT /*+ INDEX_FFS(source_iot IX_source_iot) */ UPPER(B) FROM source_iot WHERE UPPER(B) = 'hi';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2973202493

--------------------------------------------------------------------------------------

| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |               |     1 |     7 |     2   (0)| 00:00:01 |

|*  1 |  INDEX FAST FULL SCAN| IX_SOURCE_IOT |     1 |     7 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("B")='hi')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


SQL> INSERT INTO dest (B)SELECT /*+ INDEX_FFS(source_heap IX_source_heap) */ UPPER(B) FROM source_heap WHERE UPPER(B) = 'hi';

0 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 2515621119

-------------------------------------------------------------------------------------------

| Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT         |                |     1 |     7 |     2   (0)| 00:00:01 |

|   1 |  LOAD TABLE CONVENTIONAL | DEST           |       |       |            |          |

|*  2 |   INDEX FAST FULL SCAN   | IX_SOURCE_HEAP |     1 |     7 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(UPPER("B")='hi')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

SQL> INSERT INTO dest (B)SELECT /*+ INDEX_FFS(source_iot IX_source_iot) */ UPPER(B) FROM source_iot WHERE UPPER(B) = 'hi';

0 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 2629113429

------------------------------------------------------------------------------------------

| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT         |               |     1 |     7 |     1   (0)|00:00:01 |

|   1 |  LOAD TABLE CONVENTIONAL | DEST          |       |       |            |         |

|*  2 |   INDEX RANGE SCAN       | IX_SOURCE_IOT |     1 |     7 |     1   (0)|00:00:01 |

------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("B")='hi')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


SQL> DROP TABLE source_heap;

Table dropped.

SQL> DROP TABLE source_iot;

Table dropped.

SQL> DROP TABLE dest;

Table dropped.

由于对错误4198156的修复，如果我们正在执行DML操作，则不允许对IOT上基于辅助功能的索引进行索引快速全扫描。

从10053跟踪中，您可以看到在DML语句中未考虑索引fast full scan (以下每个片段的一些片段)

仅选择
===========

  Access Path: index (index (FFS))
    Index: IX_SOURCE_IOT
    resc_io: 870.000000  resc_cpu: 492817094
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  893.832687  Resp: 893.832687  Degree: 1
      Cost_io: 870.000000  Cost_cpu: 492817094
      Resp_io: 870.000000  Resp_cpu: 492817094
  Best:: AccessPath: IndexFFS
  Index: IX_SOURCE_IOT
         Cost: 893.832687  Degree: 1  Resp: 893.832687  Card: 1000000.000000  Bytes: 0.000000

插入-选择
==============

  Access Path: index (index (FFS))
    Index: PK_SOURCE_IOT
    resc_io: 2495.000000  resc_cpu: 535552855
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  2520.899393  Resp: 2520.899393  Degree: 1
      Cost_io: 2495.000000  Cost_cpu: 535552855
      Resp_io: 2495.000000  Resp_cpu: 535552855
  Access Path: index (AllEqRange)
    Index: IX_SOURCE_IOT
    resc_io: 3206.000000  resc_cpu: 222831337
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 3216.776147  Resp: 3216.776147  Degree: 1