在优化如下sql的时候，并查看其执行计划，发现整体sql的性能主要卡在group by，应该如何优化？
具体执行计划如下:
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 3136M(100)| | | |
| 1 | HASH GROUP BY | | 188K| 6802K| 10M| 3136M (1)|999:59:59 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | FILTER | | | | | | | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| mpos_aaa_his | 197K| 7119K| | 5 (0)| 00:00:01 | 18 | 18 |
|* 4 | INDEX RANGE SCAN | ID_POSXXXX_ZHSY | 1 | | | 4 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 21 | | 16710 (1)| 00:03:21 | | |
|* 6 | TABLE ACCESS FULL | aas_agent_ccc_info | 1 | 11 | | 16704 (1)| 00:03:21 | | |
| 7 | INLIST ITERATOR | | | | | | | | |
|* 8 | INDEX RANGE SCAN | IDX1_XXXX_AGENT_INFO | 1 | 10 | | 7 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 - filter( IS NULL)
4 - access("T"."ORG_ID"=195116 AND "T"."ROUTE_FLAG"='1' AND "T"."POS_CND"='XXX' AND "T"."TRADE_STATUS"='00' AND
"T"."TRADE_DATE">=TIMESTAMP' 2021-08-01 00:00:00.000000000' AND "T"."TRADE_DATE"<TIMESTAMP' 2021-09-01 00:00:00.000000000')
6 - filter(LNNVL("AI"."SHOP_ID"<>:B1))
8 - access((("A"."TOP_ORG_ID"=49694 OR "A"."TOP_ORG_ID"=52359 OR "A"."TOP_ORG_ID"=53858 OR "A"."TOP_ORG_ID"=56983 OR
"A"."TOP_ORG_ID"=56986 OR "A"."TOP_ORG_ID"=57067 OR "A"."TOP_ORG_ID"=57290)) AND "AI"."ORG_ID"="A"."ORG_ID")
sql如下：
select
count(*) 交易笔数,to_char(t.trade_date ,'yyyy-mm-dd') as 交易日期,sum(t.trade_amount) 交易金额
from mpos_aaa_his t where 1=1
and t.trade_status = '00'
and t.org_id in 195116
and t.POS_CND = 'XXXX'
and t.route_flag = '1'
and t.shop_id not in
(
select ai.shop_id from aas_agent_ccc_info ai, aas_bbb_info a where ai.org_id = a.org_id and a.top_org_id in
(
49694,
52359,
53858,
56983,
57067,
56986,
57290
)
)
and (t.trade_date >= to_timestamp('2021-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and t.trade_date < to_timestamp('2021-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
group by to_char(t.trade_date ,'yyyy-mm-dd');
你这个问题我认为不是出在group by上。问题大概也有两点
1.大数据量group by走了嵌套循环我认为不太不合适
2.看上去你使用了分区表，并且使用了全局索引，这不太合适，试试看能不能改成本地索引
针对以上两点，我建议你做以下修改。and t.shop_id not in 后面的sql提取出来形成一个内联视图，然后把这个内联视图和mpos_aaa_his进行关联之后做left join或right join 替代not in 记得加提示/+ use_hash(内联视图的名称或别名 t) no_merge(内联视图名)/
想了解更多的数据库知识可以关注我的公众号：唯一的小彬哥
大致的代码就像下面这样，你自己按上面思路调整一下。
select /*+ use_hash(t b) no_merge(b)*/ --前面/*+ use_hash(t b) no_merge(b)*/是提示，不是注释，不能去掉
count(*) 交易笔数,to_char(t.trade_date ,'yyyy-mm-dd') as 交易日期,sum(t.trade_amount) 交易金额
from mpos_aaa_his t left join (
select distinct ai.shop_id from aas_agent_ccc_info ai, aas_bbb_info a where ai.org_id = a.org_id and a.top_org_id in
(
49694,
52359,
53858,
56983,
57067,
56986,
57290
)
) b on and t.shop_id = b.shop_id
where 1=1
and t.trade_status = '00'
and t.org_id in 195116
and t.POS_CND = 'XXXX'
and t.route_flag = '1'
and b.shop_id is null--这里新加的
and (t.trade_date >= to_timestamp('2021-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and t.trade_date < to_timestamp('2021-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
group by to_char(t.trade_date ,'yyyy-mm-dd');
不知道 行不行，可以执行以下 看看
如果不使用sql中的not in语句进行排序group，大约30左右
select ai.shop_id from aas_agent_ccc_info ai
至少这里可以去重
select distinct ai.shop_id from aas_agent_ccc_info ai
单纯group 那里，没啥可弄得。