索引至少可以通过三种不同的方式来提高聚合查询性能：INDEX FULL SCAN (MIN/MAX)、INDEX FAST FULL SCAN和INDEX FULL SCAN / SORT GROUP BY NOSORT。

索引结构

在考虑不同的索引算法时，记住B树索引的内部结构可能会有所帮助：

（虽然它并不完全符合这个答案，因为它只显示了单列索引，您可以将多列索引视为B树中的B树。）

示例架构

drop table emps;
create table emps
(
    id            number not null,
    name          varchar2(100) not null,
    department_id number not null,
    salary        number not null
);
create index emps_sal_idx on emps(salary);
create index emps_dept_and_sal_idx on emps(department_id, salary);

insert into emps
select
    level id,
    'John Smith '||level name,
    mod(level, 100) department_id,
    50000 + dbms_random.value * 100000 salary
from dual
connect by level <= 100000;
begin
    dbms_stats.gather_table_stats(user, 'EMPS');
end;
/

关于此表、索引和数据，需要注意以下几点：

• 列设置为not null。否则Oracle可能无法使用某些索引，因为索引并不总是存储空值）
• 有一个只针对salary的索引，还有一个针对department_id和salary的多列索引
• 表中的数据有点不对称，薪资值相对唯一，但是只有100个department_id值。

步进全扫描（最小/最大）

当书中说索引支持MAX和MIN函数时，它可能指的是INDEX FULL SCAN (MIN/MAX)操作。看看上图中数据的排序和组织方式。找到第一个或最后一个值是一个非常简单的操作;沿着左手边多次，以找到最小值，或者沿着右手边多次，以找到最大值。

explain plan for
select min(salary)
from emps;

select * from table(dbms_xplan.display);

Plan hash value: 293008466

-------------------------------------------------------------------------------------------
| Id  | Operation                  | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |              |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |              |     1 |    22 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| EMPS_SAL_IDX |     1 |    22 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

但有时会出现问题。Oracle可以非常快地获得MIN或MAX，但由于某种原因，当它必须同时找到这两个值时，它会犯错误。看起来它应该是一个足够简单的特性，可以同时遍历左侧和右侧，但在这些情况下，Oracle必须读取整个索引。

explain plan for
select min(salary), max(salary)
from emps;

select * from table(dbms_xplan.display);

Plan hash value: 2011972768

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |    22 |   162   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |    22 |            |          |
|   2 |   INDEX FAST FULL SCAN| EMPS_SAL_IDX |   100K|  2148K|   162   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

步进快速全扫描

对于具有多列的第二个查询，多列索引会有所帮助。该索引包含所有数据，Oracle可以直接从较小的索引中读取，而不必阅读整个大表。这就是INDEX FAST FULL SCAN操作。

explain plan for
select department_id, min(salary)
from emps
group by department_id;

select * from table(dbms_xplan.display);

Plan hash value: 104291853

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |   100 |  2500 |   170   (3)| 00:00:01 |
|   1 |  HASH GROUP BY        |                       |   100 |  2500 |   170   (3)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| EMPS_DEPT_AND_SAL_IDX |   100K|  2441K|   166   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------

按NOSORT索引全扫描/分类组

对于第二个查询，我们不是要对每个部门多次运行MIN/MAX扫描吗？Oracle没有这种精确的操作，但它有一个类似于INDEX FULL SCAN后跟SORT GROUP BY NOSORT的组合。如果Oracle按顺序读取索引，它不需要排序任何东西，也不需要写任何临时空间来找到最小值和最大值。每个部门的最小值和最大值都很容易找到。

explain plan for
select /*+ index(emps) */ department_id, min(salary)
from emps
group by department_id;

select * from table(dbms_xplan.display);

Plan hash value: 445731427

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                       |   100 |  2500 |   607   (1)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|                       |   100 |  2500 |   607   (1)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | EMPS_DEPT_AND_SAL_IDX |   100K|  2441K|   607   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

（我需要一个提示来让上面的计划生效。我不太清楚为什么，出于某种原因，Oracle认为它不如其他方法好。而且它可能更慢。因为全表扫描和快速全索引扫描可以使用多块读取，而索引范围和全扫描使用单块读取，所以有时候困难的方法比聪明的方法更快。）

其他？

可能还有其他一些聪明的方法可以提高性能，例如使用内存（数据存储在列中）、使用本地索引列出间隔分区（也许可以对每个索引分区使用最小/最大扫描？）或其他一些我没有想到的特性。这里的教训是Oracle几乎总是有一种方法可以加快任何查询，尽管创建这些单独的对象总是会有成本。