联合索引使用了聚合函数

newkid

mo_yin 发表于 2016-11-7 15:53
经过咨询和查找，总结一下原因，如果有误请指正。谢谢。

联合索引（（clu_date，clu_no））其中clu_date ...

你只列出了一种计划。针对1楼说的现象，把两种计划都列出来看看。

kelis2004

以上解答都没有解答你所说的“如果我使用的cluNo是test表中存在的，那么这个查询会很快；
      但是如果我使用的cluNo是test表中不存在的，那么这个查询就非常慢;
”
最好把后一种执行计划贴出（如newkid大神所说），索引是有序的，看你第一种执行计划用的是 index max/min的方式，当clu_no有值时只需要在date索引的最右端块查找到clu_date就可以，当clu_no没有对应值时会遍历索引块逐一查找，但最后没有找到，这过程会比较耗时

jrtongxin5266

如果查询条件中没有clu_date，只有clu_no应该是不会走这个索引了吧，oracle不太了解，但是mysql一定不走。

〇〇

create table test（id_test int,clu_no int,clu_date int,clu_other_date int）;
insert into test values(2,2,2,2);
create index aaa on test(clu_date,clu_no);
set autot on
select max(clu_other_date) from test t where t.clu_no =1;
select max(clu_other_date) from test t where t.clu_no =2;
select max(clu_date) from test t where t.clu_no =1;
select max(clu_date) from test t where t.clu_no =2;

SQL> set autot on
--求max的列不在索引中,fts
SQL> select max(clu_other_date) from test t where t.clu_no =1;

MAX(CLU_OTHER_DATE)
-------------------


已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    26 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("T"."CLU_NO"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        439  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--求max的列不在索引中,fts
SQL> select max(clu_other_date) from test t where t.clu_no =2;

MAX(CLU_OTHER_DATE)
-------------------
                  2

已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    26 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("T"."CLU_NO"=2)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        441  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--求max的列在索引中,fis
SQL> select max(clu_date) from test t where t.clu_no =1;

MAX(CLU_DATE)
-------------


已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 2812518244

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

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

   3 - filter("T"."CLU_NO"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        433  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--求max的列在索引中,fis
SQL> select max(clu_date) from test t where t.clu_no =2;

MAX(CLU_DATE)
-------------
            2

已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 2812518244

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

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

   3 - filter("T"."CLU_NO"=2)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        435  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

〇〇

如果用hint,那么--求max的列不在索引中,也可以its

SQL> select /*+index(t aaa)*/ max(clu_other_date) from test t where t.clu_no =2;

MAX(CLU_OTHER_DATE)
-------------------
                  2

已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 674707125

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |      |     1 |    26 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST |     1 |    26 |     2   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN           | AAA  |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   3 - access("T"."CLU_NO"=2)
       filter("T"."CLU_NO"=2)

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        441  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed