Oracle SQL优化过程一则以及group by少见用法报错点
版本：11.2.0.4.0
业务让帮忙优化一条sql，sql文本如下(脱敏)：
select to_char(t.create_time, 'yyyy-mm') 月份,
count(*) 总数,
(select count(v.seq_no)
from zkm.test v
where to_char(v.create_time, 'yyyy-mm') = to_char(t.create_time, 'yyyy-mm')
and v.result_flag = 11) 通过数
from zkm.test t
where t.create_time >= to_date('20240101', 'yyyymmdd')
group by to_char(t.create_time, 'yyyy-mm')
order by to_char(t.create_time, 'yyyy-mm') desc;
这条SQL每次执行需要10s左右，让帮忙看能不能优化。
首先看了下，表就一个主键带的索引，create_time字段是没有索引的，从表t的条件看，查询了从20240101以来将近1年的数据，数据量太大占比接近表40%的数据量(验证过程略)，并不太适合使用索引。
但是！！！！因为select、group by、order by中并没有使用其他字段，那么建索引是有用的，因为不需要回表。
另外，不需要看执行计划，从标量子查询的自关联(表v和表t是同一张表)可以看出，由于无索引可用导致表v会被多次全表扫描，有点类似filter的方式。
考虑创建索引的话，排除数据量因素的影响的话，另外还要看看这个字段作为谓词条件被使用的多不多。
PS：dba_tab_modifications视图本身是没有的，文档 ID 400214.1提供了创建的脚本。
可以参考我另外的博文杂谈：Oracle问题：一张表推荐创建多少索引合适，里边有提供创建脚本。
select * from dba_column_usage where owner=upper('zkm') and name=upper('test') order by 4;
OWNER NAME COLUMN_NAME EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
------- ------ ------------------------------ -------------- -------------- ----------------- ----------- ---------- ---------- -------------------
ZKM TEST COL1 0 0 0 2 0 0 2025-01-03 12:05:41
ZKM TEST CREATE_TIME 1 0 0 2341 0 0 2025-01-08 17:03:27
ZKM TEST COL2 2 0 0 0 0 0 2023-02-08 15:27:40
ZKM TEST COL3 5 0 0 0 0 0 2023-11-02 12:52:29
ZKM TEST COL4 12 0 0 0 0 0 2024-01-09 08:58:50
ZKM TEST COL5 26 0 0 0 3 3 2025-01-07 09:09:33
ZKM TEST COL6 105 0 0 0 0 0 2024-10-31 15:15:20
ZKM TEST COL7 4987 0 0 0 0 0 2025-01-08 17:03:27
ZKM TEST RESULT_FLAG 5094 0 0 0 0 0 2025-01-08 17:03:27
ZKM TEST SEQ_NO 14767 105 0 0 0 0 2025-01-08 16:55:57
综合整体看使用频率，2341次也还行，另外业务人员也提到了以后随着业务这个字段也用的会比较多，Good！
后续看了数据选择性，也比较合适创建索引。
到这里都是为表t做考虑的。
我们先给create_time加上索引，注意加online。
create index zkm.idx_z_create_time on zkm.test(create_time) online;
由于表v在标量子查询做了自关联，关联条件正好也是create_time，但此处对create_time做了to_char处理会导致无法使用索引。
那么还要考虑对关联条件进行改写，其中关联条件是to_char(v.create_time, 'yyyy-mm') = to_char(t.create_time, 'yyyy-mm')，要使用索引取访问表v，可以将条件改为如下：
v.create_time >= trunc(t.create_time, 'mm') and v.create_time < add_months(trunc(sysdate, 'mm'), 1)
最终如下：
select to_char(t.create_time, 'yyyy-mm') 月份
,count(*) 总数
,(select count(*)
from zkm.test v
where 1 = 1
and v.create_time >= trunc(t.create_time, 'mm')
and v.create_time < add_months(trunc(sysdate, 'mm'), 1)
and v.result_flag = '11'
) 通过数
from zkm.test t
where 1 = 1
and t.create_time >= to_date('20240101', 'yyyymmdd')
group by to_char(t.create_time, 'yyyy-mm')
order by to_char(t.create_time, 'yyyy-mm') desc;
这里直接跑这条SQL会遇到另外一个问题，而且是语法问题：ERROR at line 6:ORA-00979: not a GROUP BY expression
到这里就纳闷了，不应该了，上边的条件改写应该是等价的没错啊。
后边又换成另外一种写法：
select to_char(t.create_time, 'yyyy-mm') 月份
,count(*) 总数
,(select count(*)
from zkm.test v
where 1 = 1
and v.create_time >= to_date(to_char(t.create_time, 'yyyy-mm'), 'yyyy-mm')
and v.create_time < add_months(to_date(to_char(t.create_time, 'yyyy-mm'), 'yyyy-mm'), 1)
and v.result_flag = '11'
) 通过数
from zkm.test t
where 1 = 1
and t.create_time >= to_date('20240101', 'yyyymmdd')
group by to_char(t.create_time, 'yyyy-mm')
order by to_char(t.create_time, 'yyyy-mm') desc;
这次又能正常跑了而且飞快。。。为啥？
到这里，这条SQL就是最终改写的SQL了，0.2s左右可以跑完，执行计划如下：
Plan hash value: 1567763871
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 |00:00:00.17 | 1069 | | | |
| 1 | SORT AGGREGATE | | 13 | 1 | 13 |00:00:00.21 | 28508 | | | |
|* 2 | FILTER | | 13 | | 171K|00:00:00.20 | 28508 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| TEST | 13 | 995 | 171K|00:00:00.18 | 28508 | | | |
|* 4 | INDEX RANGE SCAN | IDX_Z_CREATE_TIME | 13 | 1794 | 171K|00:00:00.03 | 483 | | | |
| 5 | SORT GROUP BY | | 1 | 132K| 13 |00:00:00.17 | 1069 | 2048 | 2048 | 2048 (0)|
|* 6 | INDEX FAST FULL SCAN | IDX_Z_CREATE_TIME | 1 | 133K| 171K|00:00:00.05 | 1069 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ADD_MONTHS(TO_DATE(:B1,'yyyy-mm'),1)>TO_DATE(:B2,'yyyy-mm'))
3 - filter("V"."RESULT_FLAG"='11')
4 - access("V"."CREATE_TIME">=TO_DATE(:B1,'yyyy-mm') AND "V"."CREATE_TIME"<ADD_MONTHS(TO_DATE(:B2,'yyyy-mm'),1))
6 - filter("T"."CREATE_TIME">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
回到还没解决的ORA-00979，观察两次改写的条件的其中一部分，trunc(t.create_time, 'mm')和to_date(to_char(t.create_time, 'yyyy-mm'), 'yyyy-mm')不就是同一个结果么...怎么会一个可以一个不行。
select trunc(sysdate, 'mm') trunc_result,to_date(to_char(sysdate, 'yyyy-mm'), 'yyyy-mm') other_result from dual where trunc(sysdate, 'mm') = to_date(to_char(sysdate, 'yyyy-mm'), 'yyyy-mm');
TRUNC_RESULT OTHER_RESULT
------------------- -------------------
2025-01-01 00:00:00 2025-01-01 00:00:00
估计会有人看蒙蔽了。
不过到这里结合正常跑完SQL的执行计划的谓词信息，我大概能猜测到，原因是因为select部分的标量子查询里边的trunc(t.create_time, 'mm')并不存在于group by子句中，因此报语法错误。
想要验证很简单，只要下边的SQL能正常跑就是了。
select to_char(t.create_time, 'yyyy-mm') 月份
,count(*) 总数
,(select count(*)
from zkm.test v
where 1 = 1
and v.create_time >= trunc(t.create_time, 'mm')
and v.create_time < add_months(trunc(sysdate, 'mm'), 1)
and v.result_flag = '11'
) 通过数
from zkm.test t
where 1 = 1
and t.create_time >= to_date('20240101', 'yyyymmdd')
group by to_char(t.create_time, 'yyyy-mm'),trunc(t.create_time, 'mm')
order by to_char(t.create_time, 'yyyy-mm') desc;
测试了下，确实可以正常跑了。
看执行计划id为4的谓词信息，to_char(t.create_time, 'yyyy-mm')是作为一个整体的值:B1代入做判断了，并没有去看t.create_time的值，那么除了to_char(t.create_time, 'yyyy-mm')以外都应该进行分组。
而trunc(t.create_time, 'mm')尽管从结果上等价于to_date(to_char(t.create_time, 'yyyy-mm'), 'yyyy-mm')，但形式上还是不同的。
至此。