索引優化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: 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成本會增加。