oracle - 位图索引全扫描是否在Oracle中进行排序？

Question

index fast full scan 和index full scan 的区别之一应该是index fast full scan 读取叶子的方式与它在磁盘中的存储方式相同。所以如果有order by子句就需要进行排序操作。但是索引全扫描按排序顺序读取叶子。所以读完后不应该有排序，即使有索引列的order by。但是在下面的执行中，它在使用索引全扫描读取后执行排序操作。能告诉我原因吗？

Answer 1

排序的原因是因为分区。

位图索引不能是分区表的全局索引。在本地索引中，特定索引分区中的所有键仅引用存储在单个基础表分​​区中的行。

记录可以分布在多个分区中。如果不进行排序，它们将按分区返回。

例子:

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');

未排序的查询:

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 |
-----------------------------------------------------------------------------------------------------------------

查询排序:

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 |
------------------------------------------------------------------------------------------------------------------