table—we are using the index instead of the
table.) We use multiblock I/O and read all
the leaf, branch, and root blocks. We ignore
the branch and root blocks when executing
the query and just process the (unordered)
data in the leaf blocks.
A full index scan reads the index a block
at a time, from start to finish. It reads the
root block, navigates down the left-hand
side of the index (or the right-hand side for
a descending full scan), and then when it
hits the leaf block, it reads across the entire
bottom of the index—a block at a time—in
sorted order. It uses single-block, not multiblock, I/O for this operation.
Now, let’s address the misconceptions
the questioner “read elsewhere.” It is true
that a fast full index scan is an alternative to
a full table scan when the index contains all
the columns referenced in the query, but “at
least one column in the index key has the
NO T NULL constraint” is not a requirement.
I can prove this by example. Suppose I have
the following table:
table (the index is acting as a skinny version
of the table), and the predicate, where owner
= ‘SCOT T’, makes it such that the OWNER
column must be NOT NULL to be in the
result set. And because any NOT NULL value
would appear in the index, we can use the
index. If the query were SELEC T COUNT(*)
FROM T, it is true that we would not be able
to use the index with a fast full scan instead
of the table, because the table could have
rows that are not in the index. Any row in
which S TATUS was null and O WNER was
null would not appear in the index, and we’d
miss counting it. But because the WHERE
clause explicitly says that we are looking for
a not-null value of O WNER, I know that every
row I need appears in the index.
SQL> create bitmap index
2 bm_idx on t(owner);
—and then run the simple query in Listing 3.
Code Listing 3: Simple query uses bitmap index for fast full index scan
SQL> set autotrace traceonly explain
SQL> select distinct owner from t;
Execution Plan
——————————————————————————————————————————————————————————————————————————
Plan hash value: 4145652518
SQL> create table t
2 as
3 select *
4 from all_objects;
Table created.
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————
| Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time |
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————
| 0 |SELECT STATEMENT | |82657| 1372K| 465 ( 1)|00:00:06|
| 1 | HASH UNIQUE | |82657| 1372K| 465 ( 1)|00:00:06|
| 2 | BITMAP INDEX FAST FULL SCAN|BM_IDX|82657| 1372K| 7 (0)|00:00:01|
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————
Code Listing 4: Choosing the right index scan for the right query
SQL> alter table t
2 modify owner null;
Table altered.
SQL> create index t_idx
2 on t(status,owner);
Index created.
SQL> desc t
Name Null? Type
——————————————— ——————————— ————————————————
OWNER VARCHAR2( 30)
OBJECT_NAME NOT NULL VARCHAR2( 30)
...
STATUS VARCHAR2( 7)
...
As you can see, neither S TATUS nor
OWNER is defined as NOT NULL. However,
if I execute the query in Listing 2, I will
observe a fast full index scan in action. That’s
because we can use the index in place of the
SQL> select status, owner from t
2 where owner = 'SYS' order by status, owner;
Execution Plan
——————————————————————————————————————————————————————————————————————————
Plan hash value: 2277287974
—————————————————————————————————————————————————————————————————————————————————————————————
| Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time |
—————————————————————————————————————————————————————————————————————————————————————————————
| 0 |SELECT STATEMENT| |34755| 746K| 247 ( 1)|00:00:03|
|* 1 | INDEX FULL SCAN|T_IDX|34755| 746K| 247 ( 1)|00:00:03|
—————————————————————————————————————————————————————————————————————————————————————————————
SQL> select status, owner from t
2 where owner = 'SCOTT' order by status, owner;
Execution Plan
——————————————————————————————————————————————————————————————————————————
Plan hash value: 3276893591
———————————————————————————————————————————————————————————————————————————————————————————————————————
| Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time |
———————————————————————————————————————————————————————————————————————————————————————————————————————
| 0 |SELECT STATEMENT | | 8| 176 | 70 ( 3)|00:00:01|
| 1 | SORT ORDER BY | | 8| 176 | 70 ( 3)|00:00:01|
|* 2 | INDEX FAST FULL SCAN|T_IDX| 8| 176 | 69 ( 2)|00:00:01|
———————————————————————————————————————————————————————————————————————————————————————————————————————
