Does bitmap index full scan perform sort in Oracle?

Question

One of the differences between index fast full scan and index full scan should be that index fast full scan reads the leaves as how it is stored in the disc. So it needs to perform a sort operation if there is an order by clause. But the index full scan reads the leaves in sorted order. So there shouldn't be a sort after reading it, even if there is an order by with the indexed column. But on the execution below, it performs a sort operation after reading with index full scan. Could you please tell me the reason?

Answer 1

The reason for the sorting is due to the partitioning.

Bitmap indexes cannot be global indexes on partitioned tables. In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition.

The records can be distributed over several partitions. Without sorting they are returned partition by partition.

Example:

CREATE TABLE sales_part
( prod_id NUMBER(10,0),
  prod_date DATE
)
PARTITION BY RANGE (prod_date)
(
PARTITION sales_2020 VALUES LESS THAN (TO_DATE('31.12.2020', 'DD.MM.YYYY')),
PARTITION sales_2021 VALUES LESS THAN (TO_DATE('31.12.2021', 'DD.MM.YYYY'))
);

INSERT INTO sales_part VALUES (1, TO_DATE('31.01.2020', 'DD.MM.YYYY'));
INSERT INTO sales_part VALUES (2, TO_DATE('28.02.2021', 'DD.MM.YYYY'));
INSERT INTO sales_part VALUES (3, TO_DATE('31.03.2020', 'DD.MM.YYYY'));
INSERT INTO sales_part VALUES (4, TO_DATE('30.04.2021', 'DD.MM.YYYY'));

CREATE BITMAP INDEX sales_part_idx1 ON sales_part(prod_id) LOCAL;

EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=> user, tabname => 'SALES_PART');

Query unsorted:

SELECT prod_id FROM sales_part;

   PROD_ID
----------
         1
         3
         2
         4

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |       |       |     2 (100)|          |       |       |
|   1 |  PARTITION RANGE ALL          |                 |     3 |     9 |     2   (0)| 00:00:01 |     1 |     2 |
|   2 |   BITMAP CONVERSION TO ROWIDS |                 |     3 |     9 |     2   (0)| 00:00:01 |       |       |
|   3 |    BITMAP INDEX FAST FULL SCAN| SALES_PART_IDX1 |       |       |            |          |     1 |     2 |
-----------------------------------------------------------------------------------------------------------------

Query sorted:

SELECT prod_id FROM sales_part order by prod_id;

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |       |       |     3 (100)|          |       |       |
|   1 |  SORT ORDER BY                 |                 |     4 |    12 |     3  (34)| 00:00:01 |       |       |
|   2 |   PARTITION RANGE ALL          |                 |     4 |    12 |     2   (0)| 00:00:01 |     1 |     2 |
|   3 |    BITMAP CONVERSION TO ROWIDS |                 |     4 |    12 |     2   (0)| 00:00:01 |       |       |
|   4 |     BITMAP INDEX FAST FULL SCAN| SALES_PART_IDX1 |       |       |            |          |     1 |     2 |
------------------------------------------------------------------------------------------------------------------