Shulou(Shulou.com)06/01 Report--

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 >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.