在优化如下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');