Performance comparison of Oracle 11g IFS VS IFFS
I. explanation
1. IFS (index full scan) single-block read, IFFS (index fast full scan) multi-block read.
2. When a column in the table is fully scanned at the same time, the speed of multi-block reading is obviously faster and the performance is better than that of single-block reading.
3. Both FTS (full table scan) and IFFS (index fast full scan) are multi-block reads.
4. IFFS (index fast full scan) is multi-block read, which can be read in parallel and not sorted.
5. IFS (index full scan) is read in a single block and ordered.
Second, the testing process
SQL > alter system flush buffer_cache
System altered.
Elapsed: 00:00:00.17
SQL > alter system flush shared_pool
System altered.
Elapsed: 00:00:00.30
SQL > select / * + index (tt idx_object_id) * / count (object_id) from tt
COUNT (OBJECT_ID)
-
5524288
Elapsed: 00:00:05.72
SQL > alter system flush buffer_cache
System altered.
Elapsed: 00:00:00.17
SQL > alter system flush shared_pool
System altered.
Elapsed: 00:00:00.07
SQL > select count (object_id) from tt
COUNT (OBJECT_ID)
-
5524288
Elapsed: 00:00:01.35
SQL > explain plan for select / * + index (tt idx_object_id) * / count (object_id) from tt
Explained.
Elapsed: 00:00:00.07
SQL > select * from table (dbms_xplan.display ())
PLAN_TABLE_OUTPUT
-
Plan hash value: 3277332215
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
| | 0 | SELECT STATEMENT | | 1 | 5 | 12269 (1) | 00:02:28 |
| | 1 | SORT AGGREGATE | | 1 | 5 |
| | 2 | INDEX FULL SCAN | IDX_OBJECT_ID | 2762K | 13m | 12269 (1) | 00:02:28 |
9 rows selected.
Elapsed: 00:00:00.33
SQL > explain plan for select count (object_id) from tt
Explained.
Elapsed: 00:00:00.01
SQL > select * from table (dbms_xplan.display ())
PLAN_TABLE_OUTPUT
-
Plan hash value: 1131838604
-
| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | | 1 | 5 | 3335 (1) | 00:00:41 |
| | 1 | SORT AGGREGATE | | 1 | 5 |
| | 2 | INDEX FAST FULL SCAN | IDX_OBJECT_ID | 2762K | 13m | 3335 (1) | 00:00:41 |
-
9 rows selected.
Elapsed: 00:00:00.01
SQL >
