如何优化oracle查询？

Question

以下查询花费大约45秒的Oracle 11g

select count(cap.ISHIGH),ms.SID,ms.NUM from CDetail cap,MData ms 
where cap.MDataID_FK=ms.MDataID_PK and trunc(cap.CREATEDTIME) between trunc(sysdate-10) and trunc(sysdate) 
group by ms.SID,ms.NUM ; 

解释计划：

------------------------------------------------------------------------------------------------------------------- 
    | Id | Operation      | Name     | Rows | Bytes |TempSpc| Cost (%CPU)| Time  | 
    ------------------------------------------------------------------------------------------------------------------- 
    | 0 | SELECT STATEMENT     |      | 766K| 32M|  | 94421 (1)| 00:18:54 | 
    | 1 | HASH GROUP BY     |      | 766K| 32M| 41M| 94421 (1)| 00:18:54 | 
    |* 2 | HASH JOIN      |      | 766K| 32M| 21M| 85716 (1)| 00:17:09 | 
    | 3 | VIEW       | VW_GBC_5    | 766K| 13M|  | 73348 (1)| 00:14:41 | 
    | 4 |  HASH GROUP BY    |      | 766K| 13M| 98M| 73348 (1)| 00:14:41 | 
    |* 5 |  FILTER      |      |  |  |  |   |   | 
    | 6 |  TABLE ACCESS BY INDEX ROWID| CDetail   | 3217K| 58M|  | 63738 (1)| 00:12:45 | 
    |* 7 |  INDEX RANGE SCAN   | IDX_CPCTYDTLTRNCCRTDTM | 3365K|  |  | 14679 (1)| 00:02:57 | 
    | 8 | TABLE ACCESS FULL    | MData  | 871K| 22M|  | 9665 (1)| 00:01:56 | 
    ------------------------------------------------------------------------------------------------------------------- 

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

     2 - access("ITEM_1"="MS"."MDataID_PK") 
     5 - filter(TRUNC([email protected]!-10)<=TRUNC([email protected]!)) 
     7 - access(TRUNC(INTERNAL_FUNCTION("CREATEDTIME"))>=TRUNC([email protected]!-10) AND 
        TRUNC(INTERNAL_FUNCTION("CREATEDTIME"))<=TRUNC([email protected]!)) 

table MData contains around 900,000 rows and table CDetail contains 23,000,000 rows. 

Should I introduce any new index or any other way to optimize the above query. 

编辑3. IDX_CPCTYDTLTRNCCRTDTM是上一个TRUNC函数索引（CREATEDTIME） 编辑： 1

解释计划：用于全表扫描usi NG提示/ +满（Cdetail）/

--------------------------------------------------------------------------------------------------- 
| Id | Operation    | Name    | Rows | Bytes |TempSpc| Cost (%CPU)| Time  | 
--------------------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT  |     | 780K| 33M|  | 160K (2)| 00:32:01 | 
| 1 | HASH GROUP BY   |     | 780K| 33M| 42M| 160K (2)| 00:32:01 | 
|* 2 | HASH JOIN   |     | 780K| 33M| 22M| 151K (2)| 00:30:15 | 
| 3 | VIEW    | VW_GBC_5   | 780K| 13M|  | 138K (2)| 00:27:46 | 
| 4 |  HASH GROUP BY  |     | 780K| 14M| 230M| 138K (2)| 00:27:46 | 
|* 5 |  FILTER   |     |  |  |  |   |   | 
|* 6 |  TABLE ACCESS FULL| CDetail | 7521K| 136M|  | 120K (2)| 00:24:02 | 
| 7 | TABLE ACCESS FULL | MData | 890K| 22M|  | 9666 (1)| 00:01:56 | 
--------------------------------------------------------------------------------------------------- 

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

    2 - access("ITEM_1"="MS"."MDataID_PK") 
    5 - filter(TRUNC([email protected]!-10)<=TRUNC([email protected]!)) 
    6 - filter(TRUNC(INTERNAL_FUNCTION("CREATEDTIME"))>=TRUNC([email protected]!-10) AND 
       TRUNC(INTERNAL_FUNCTION("CREATEDTIME"))<=TRUNC([email protected]!)) 

Answer 1

1. 感谢您分享一个解释计划;这是一个好的开始。然而，解释计划的事情是它给你的估计，而不是实际。如果可以，你可以得到一个SQL Monitor报告吗？这将向您显示实际的基数并向您显示在查询中花费的时间。

2. 日期过滤器预计约3M行（ID 6和7）？这是否准确？

3. 什么是IDX_CPCTYDTLTRNCCRTDTM索引的定义？它碰巧是基于功能的吗？

4. 为了验证我的想法，您可以添加以下提示，运行查询并再次获取解释计划。

   选择/ * +满（帽）*/...