Execution plan using B+ tree index, but also sorts

Question

I'm using Oracle sqlplus. And I have the following query:

SELECT fooID from foo MINUS
SELECT fooID from bar;

I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo and one in the field fooID of the table bar. After that I analyze the statistics of both my tables: foo and bar and check the execution plan of my query using EXPLAIN PLAN .... But I get this:

  SELECT STATEMENT
      MINUS
        SORT UNIQUE
          INDEX FAST FULL SCAN FOO_INDEX
        SORT UNIQUE
          INDEX FAST FULL SCAN BAR_INDEX

How is that even possible? When doing INDEX FAST FULL SCAN, because the index is a B+ tree, doesn't the system gets back it's tuples sorted? Why does it need to do SORT UNIQUE (data already sorted)?

Answer 1

Oracle says about Indexes and Index-Organized Tables under Full Index Scan: In a full index scan, the database reads the entire index in order.

Yet, unter Fast Full Index Scan, it reads: A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index blocks in no particular order. (Emphasis mine)

Now, probably, the question should be: why did the optimzier choose INDEX FAST FULL SCAN over INDEX FULL SCAN.

A hint to the answer of the latter question is given in under 11.2.3.7 Fast Full Index Scans: A fast full scan is faster than a normal full index scan because it can use multiblock I/O and can run in parallel just like a table scan.

If you insist that Oracle use a full index scan, you might want to try the /*+ index() */ hint:

create table tq84_foo (
  fooID number not null
);

create table tq84_bar (
  fooID number not null
);

create unique index ix_foo on tq84_foo(fooID);
create unique index ix_bar on tq84_bar(fooID);

explain plan for
 select /*+ index(f ix_foo) */fooID from tq84_foo f
        MINUS
select /*+ index(b ix_bar) */ fooID from tq84_bar b;

select * from table(dbms_xplan.display);

resulting in

------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     1 |    26 |     4  (75)| 00:00:01 |
|   1 |  MINUS              |        |       |       |            |          |
|   2 |   SORT UNIQUE NOSORT|        |     1 |    13 |     2  (50)| 00:00:01 |
|   3 |    INDEX FULL SCAN  | IX_FOO |     1 |    13 |     1   (0)| 00:00:01 |
|   4 |   SORT UNIQUE NOSORT|        |     1 |    13 |     2  (50)| 00:00:01 |
|   5 |    INDEX FULL SCAN  | IX_BAR |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------