Difference between Full Index Scans and Fast Full Index Scans

An index fast full scan reads the ENTIRE index, unsorted, as it exists on disk. It is basically using the index as a "skinny" version of the table. The query in question would only be accessing attributes in the index (we are not using the index as a way to get to the table, we are using the index INSTEAD of the table) We use multiblock IO and read all of the leaf, branch and the root block. We ignore the branch and root blocks and just process the (unordered) data on the leaf blocks.  An index full scan is when we read the index a block at a time - from start to finish. We'll read the root block, navigate down the left hand side of the index (or right if we are doing a descending full scan) and then when we hit the leaf block - we'll read across the entire bottom of the index - a block at a time - in sorted order. We use single block IO, not multiblock IO for this operation.  and at least one column in the  index key has the NOT NULL constraint.  not true. In the following, neither owner nor status is NOT NULL, yet an index fast full scan is performed on an index on status, owner  ops$tkyte%ORA11GR2> create table t as select * from all_objects; Table created. ops$tkyte%ORA11GR2> alter table t modify owner null; Table altered. ops$tkyte%ORA11GR2> create index t_idx on t(status,owner); Index created. ops$tkyte%ORA11GR2> desc t Name Null? Type ----------------------------------- -------- ------------------------ OWNER VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NOT NULL NUMBER EDITION_NAME VARCHAR2(30) ops$tkyte%ORA11GR2> set linesize 1000 ops$tkyte%ORA11GR2> set autotrace traceonly explain ops$tkyte%ORA11GR2> select count(*) from t where owner = 'SCOTT'; Execution Plan ---------------------------------------------------------- Plan hash value: 1058879072 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 17 | 69 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 17 | | | |* 2 | INDEX FAST FULL SCAN| T_IDX | 12 | 204 | 69 (2)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OWNER"='SCOTT') Note ----- - dynamic sampling used for this statement (level=2) Fast full index scans cannot be performed against bitmap indexes.  also - not true. In the follow, a bitmap index is fast full scanned.  ops$tkyte%ORA11GR2> create table t as select * from all_objects; Table created. ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> create bitmap index t_idx on t(owner); Index created. ops$tkyte%ORA11GR2> ops$tkyte%ORA11GR2> set autotrace traceonly explain ops$tkyte%ORA11GR2> select distinct owner from t; Execution Plan ---------------------------------------------------------- Plan hash value: 1011853946 ------------------------------------------------------------------------------------------ ---- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ ---- | 0 | SELECT STATEMENT | | 71491 | 1186K| | 405 (1)| 00:00:05 | | 1 | HASH UNIQUE | | 71491 | 1186K| 1696K| 405 (1)| 00:00:05 | | 2 | BITMAP INDEX FAST FULL SCAN| T_IDX | 71491 | 1186K| | 7 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ ---- Note ----- - dynamic sampling used for this statement (level=2) ops$tkyte%ORA11GR2> set autotrace off .A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan "  That is something that  o maybe be true  o may not be true  "It depends"