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

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

-----------------------------------------------------------------------------------------
| 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成本会增加。