Today I want to query our table again. We alter the table so column N is not null.
alter table index_demo modify n not null Table altered. desc index_demo Name Null? Type ------------------ -------- --------------- N NOT NULL NUMBER D NUMBER M NUMBER S VARCHAR2(4000)
What I would like to see is all values of N. Nothing more, nothing less:
explain plan for select n from index_demo; Explained. select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 1646280665 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 488K| 58 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| INDEX_DEMO_U_N | 100K| 488K| 58 (0)| 00:00:01 | --------------------------------------------------------------------------------------- 8 rows selected.
We see the Index Fast Full Scan (Index FFS) appear as operation. The Index FSS reads all data blocks of the index. The order of reading the blocks doesn’t matter, we just want to see all values in column N.
The nice thing about the Index FFS is that it read multiple blocks at once (as opposed to reading single bocks). Reading multiple blocks at once is much quicker then reading the same number of blocks one at a time!.
Nice thing to notice: The table itself is not touched at all. All we need is in the index. SO a very expensive Full Table Scan is not necessary.
So….if Oracle just wants to read all data, and that data is also in the index AND the column is not NULL, it can use the Index FFS.