I've noticed I can't seem to force an index fast full scan of a function-based index on an IOT, when it's the source of data for an INSERT. Works fine if it's just a select, or if it's a function based index on a standard heap organized table. Is there any way around this or just a known limitation?
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.
Due to the fix for Bug 4198156, an index fast full scan of a secondary function-based index on an IOT is not allowed if we are doing a DML operation.
From the 10053 traces, you can see that the index fast full scan is not considered in the DML statement (some snippets of each below)
SELECT only
===========
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
INSERT-SELECT
==============
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