Index index
I would like to see all values of N, but I would like to see them in order.
So, Oracle can not use the Index Fast Full Scan, since that operator doesn’t read the index in order. We need the Index Full Scan. The whole index must be read, in the right order, so that will be block by block (more expensive then a multiblock read).
select n from index order by n Explained. PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- Plan hash value: 1711001241 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 488K| 210 (1)| 00:00:01 | | 1 | INDEX FULL SCAN | INDEX_DEMO_U_N | 100K| 488K| 210 (1)| 00:00:01 | ----------------------------------------------------------------------------------- 8 rows selected.
Yes …there it is: an Index Full Scan. Cost is 210. That is about the number of blocks in the index. (208 leaf blocks + 1 root block=209). The last 1 (to get to 210) is for another post.
Let’s also notice that (again) the table is not needed for the result, all information can be retrieved from the index.