索引优化min、max的聚合函数

客户提到最近系统比较慢，采样了一个awr报表，发现了下面的这个sql语句存执行时间较长 SELECT MAX (dmsample0_.ORD) AS x0_0_ FROM HF_DM_SAMPLE dmsample0_ WHERE (dmsample0_.PROJECT_ID = '000000000001') select * from table(dbms_xplan.display_cursor

客户提到最近系统比较慢，采样了一个awr报表，发现了下面的这个sql语句存执行时间较长
SELECT MAX (dmsample0_.ORD) AS x0_0_
FROM HF_DM_SAMPLE dmsample0_
WHERE (dmsample0_.PROJECT_ID = '000000000001')

select * from table(dbms_xplan.display_cursor('3js6cmjycbndh',null));

SQL_ID 3js6cmjycbndh, child number 0
-------------------------------------
select max(dmsample0_.ORD) as x0_0_ from HF_DM_SAMPLE dmsample0_ where
(dmsample0_.PROJECT_ID='000000000001' )

Plan hash value: 1698372702

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5595 (100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
|* 2 | TABLE ACCESS FULL| HF_DM_SAMPLE | 139K| 2456K| 5595 (1)| 00:01:08 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("DMSAMPLE0_"."PROJECT_ID"='000000000001')

SQL> select num_buckets,num_distinct,num_nulls,histogram from dba_tab_columns where table_name='HF_DM_SAMPLE' and (column_name='PROJECT_ID' or column_name='ORD');

NUM_BUCKETS NUM_DISTINCT NUM_NULLS HISTOGRAM
----------- ------------ ---------- ---------------
2 2 0 FREQUENCY
1 137792 0 NONE

看上去project_id的过滤性很差，这里选择全表是正常的，但是我们需要注意的是这里查询其实只需要两个列PROJECT_ID和ORD

由于索引是有序的，那么如果建立PROJECT_ID,ORD的联合索引，这个扫描的过程是从root节点到分支块节点再到叶块节点，但是到叶块节点时只是扫描project_id='000000000001'的对应叶块节点的第一个叶块或者最后一个叶块，而且只扫描第一行或者最后一行数据。，这就是我们常见的index range scan（min/max）而跟平时我们优化所不同的是这里是组合索引。

create index ind_multi on HF_DM_SAMPLE(PROJECT_ID,ORD);

SELECT MAX (dmsample0_.ORD) AS x0_0_
FROM HF_DM_SAMPLE dmsample0_
WHERE (dmsample0_.PROJECT_ID = '000000000001')

select * from table(dbms_xplan.display_cursor(null,null));

SQL_ID 1vjvuktzmxwm3, child number 0
-------------------------------------
SELECT MAX (dmsample0_.ORD) AS x0_0_ FROM HF_DM_SAMPLE dmsample0_ WHERE
(dmsample0_.PROJECT_ID = '000000000001')

Plan hash value: 4232005098

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| 2 | FIRST ROW | | 1 | 18 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| IND_MULTI | 1 | 18 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("DMSAMPLE0_"."PROJECT_ID"='000000000001')

这里我们再来看下另一种索引的创建办法：
Drop index ind_multi；
create index ind_multi on HF_DM_SAMPLE(ORD, PROJECT_ID);

这里我们以ORD为索引的前导列创建索引，然后看执行计划：
SQL_ID fbmhd0u1j3t3u, child number 0
-------------------------------------
select max(dmsample0_.ORD) as x0_0_ from HF_DM_SAMPLE dmsample0_ where
(dmsample0_.PROJECT_ID='000000000001' )

Plan hash value: 1607964330

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 18 | | |
| 2 | FIRST ROW | | 1 | 18 | 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| IND_MULTI | 1 | 18 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("DMSAMPLE0_"."PROJECT_ID"='000000000001')

这里从之前的INDEX RANGE SCAN （MIN/MAX）变为了INDEX FULL SCAN (MIN/MAX)，index full scan(MIN/MAX)也是全索引扫描，而跟index range scan（min/max）所不同的是index full scan（min/max）会直接扫描root、分支然后到最左或者最后叶块节点，当然扫描过程中还会对project_id进行过滤，如果不符合查询继续从右边往前边扫描叶块节点，直到找到符合条件的叶块，index range scan（min/max）会有个先通过索引前导列过滤的行为，然后去扫描对应的叶块节点的第一个或者最后一个叶块。

初始来看，这两种索引的消耗没有多大区别，而随着数据的增多两种索引扫描还是有一定的差异的，当PROJECT_ID不同值多后，以project_id为前导列的索引IO成本不会有太大的变化，而以ORD为索引的前导列IO成本会增加。

还有一个需要注意的是，过多的索引会造成更新较多，我们需要根据需求来做出进一步的选择以哪个为索引前导列来建立索引，就目前这个sql而言，由于project_id字段不同值只有2，而业务的查询也是使用ORD过滤的较多，这里还是以ORD为前导列建议索引更加合适。

原文地址：索引优化min、max的聚合函数, 感谢原作者分享。