union union all group by 优化一例
来源：互联网 发布：龙卷风网络收音机 mac 编辑：程序博客网 时间：2024/05/16 23:59
一. 原执行计划
本SQL是公司显示屏报表SQL，每2min更新一次
SQL> explain plan for select distinct id_card from (select t.id_card from clspuser.crf_p2p_account_info t where t.loan_amount <= 200000 and t.pay_date <= '2016-11-14' union select t.id_card from clspuser.zh_crf_p2p_account_info t where t.loan_amount <= 200000 and t.pay_date <= '2016-11-14');Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 398117206--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 168K| 2800K| | 4009 (2)| 00:00:49 || 1 | VIEW | | 168K| 2800K| | 4009 (2)| 00:00:49 || 2 | SORT UNIQUE | | 168K| 6093K| 15M| 4009 (2)| 00:00:49 || 3 | UNION-ALL | | | | | | ||* 4 | TABLE ACCESS FULL| CRF_P2P_ACCOUNT_INFO | 167K| 6067K| | 2357 (2)| 00:00:29 ||* 5 | TABLE ACCESS FULL| ZH_CRF_P2P_ACCOUNT_INFO | 759 | 27324 | | 12 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter("T"."LOAN_AMOUNT"<=200000 AND "T"."PAY_DATE"<='2016-11-14') 5 - filter("T"."LOAN_AMOUNT"<=200000 AND "T"."PAY_DATE"<='2016-11-14')18 rows selected.SQL> 二.去日期：
日期每次都是取当前日期，不起任何作用，完全可以去掉SQL> select distinct id_card from (select t.id_card from clspuser.crf_p2p_account_info t where t.loan_amount <= 200000 union select t.id_card from clspuser.zh_crf_p2p_account_info t where t.loan_amount <= 200000 );159471 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 398117206--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 168K| 2800K| | 3608 (1)| 00:00:44 || 1 | VIEW | | 168K| 2800K| | 3608 (1)| 00:00:44 || 2 | SORT UNIQUE | | 168K| 4281K| 11M| 3608 (2)| 00:00:44 || 3 | UNION-ALL | | | | | | ||* 4 | TABLE ACCESS FULL| CRF_P2P_ACCOUNT_INFO | 167K| 4263K| | 2339 (1)| 00:00:29 ||* 5 | TABLE ACCESS FULL| ZH_CRF_P2P_ACCOUNT_INFO | 759 | 18975 | | 12 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter("T"."LOAN_AMOUNT"<=200000) 5 - filter("T"."LOAN_AMOUNT"<=200000)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 10591 consistent gets 0 physical reads 0 redo size 5167400 bytes sent via SQL*Net to client 117410 bytes received via SQL*Net from client 10633 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 159471 rows processedSQL> 三．加索引：
看到执行计划全表扫描，常规想法加索引，但此处特殊在索引的选择性还是可以的，但因为数据原因大部分都是符合要求的，17w的数据有15w多都要读所以索引不走，Oracle如果按反常思维，比如说先找出不符合条件的排除再全读可能索引会快，本还想按此思维改写。
SQL> create index clspuser.crf_p2p_loan_amount_idx on crf_p2p_account_info(loan_amount);Index created.SQL> 索引没用，删了SQL> drop index crf_p2p_loan_amount_idx ;Index dropped.SQL>
四． 组合索引
这里添加联合索引，可见IFFS要比TAF快多了create index clspuser.crf_p2p_loan_amount_uidx on clspuser.crf_p2p_account_info (loan_amount, id_card) online;SQL> explain plan for select distinct id_card from (select t.id_card from clspuser.crf_p2p_account_info t where t.loan_amount <= 200000 union select t.id_card from clspuser.zh_crf_p2p_account_info t where t.loan_amount <= 200000 );Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1932924644------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 168K| 2800K| | 1459 (2)| 00:00:18 || 1 | VIEW | | 168K| 2800K| | 1459 (2)| 00:00:18 || 2 | SORT UNIQUE | | 168K| 4281K| 11M| 1459 (3)| 00:00:18 || 3 | UNION-ALL | | | | | | ||* 4 | INDEX FAST FULL SCAN| CRF_P2P_LOAN_AMOUNT_UIDX | 167K| 4263K| | 189 (3)| 00:00:03 ||* 5 | TABLE ACCESS FULL | ZH_CRF_P2P_ACCOUNT_INFO | 759 | 18975 | | 12 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter("T"."LOAN_AMOUNT"<=200000) 5 - filter("T"."LOAN_AMOUNT"<=200000)18 rows selected.SQL> 五. 去掉distinct
SQL> explain plan for select id_card from (select t.id_card from clspuser.crf_p2p_account_info t where t.loan_amount <= 200000 union select t.id_card from clspuser.zh_crf_p2p_account_info t where t.loan_amount <= 200000 );Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1932924644------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 168K| 2800K| | 1459 (2)| 00:00:18 || 1 | VIEW | | 168K| 2800K| | 1459 (2)| 00:00:18 || 2 | SORT UNIQUE | | 168K| 4281K| 11M| 1459 (3)| 00:00:18 || 3 | UNION-ALL | | | | | | ||* 4 | INDEX FAST FULL SCAN| CRF_P2P_LOAN_AMOUNT_UIDX | 167K| 4263K| | 189 (3)| 00:00:03 ||* 5 | TABLE ACCESS FULL | ZH_CRF_P2P_ACCOUNT_INFO | 759 | 18975 | | 12 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter("T"."LOAN_AMOUNT"<=200000) 5 - filter("T"."LOAN_AMOUNT"<=200000)18 rows selected.SQL> 六. 去掉select外围
这里就是刚才提到的多此一举了SQL> explain plan for select distinct t.id_card from clspuser.crf_p2p_account_info t where t.loan_amount <= 200000 union select t.id_card from clspuser.zh_crf_p2p_account_info t where t.loan_amount <= 200000;Explained.SQL> SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 2415738997-----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 159K| 4041K| | 1414 (3)| 00:00:17 || 1 | SORT UNIQUE | | 159K| 4041K| 11M| 1414 (3)| 00:00:17 || 2 | UNION-ALL | | | | | | ||* 3 | INDEX FAST FULL SCAN| CRF_P2P_LOAN_AMOUNT_UIDX | 167K| 4263K| | 189 (3)| 00:00:03 ||* 4 | TABLE ACCESS FULL | ZH_CRF_P2P_ACCOUNT_INFO | 759 | 18975 | | 12 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter("T"."LOAN_AMOUNT"<=200000) 4 - filter("T"."LOAN_AMOUNT"<=200000)17 rows selected.SQL> 七. 用union all
union all只是把2个结果拼接在一起，不会排序去重，这对于不用排序去重的性能提高尤为明显，但本SQL还是要去重操作的，这里把去重放到外面了效果比较明显了已经，因为这里省去了SORT但是distinct效果还是不尽如人意
SQL> explain plan for select distinct id_card from (select t.id_card from clspuser.crf_p2p_account_info t where t.loan_amount <= 200000 union all select t.id_card from clspuser.zh_crf_p2p_account_info t where t.loan_amount <= 200000 );Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1571881081------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 168K| 2800K| | 1144 (2)| 00:00:14 || 1 | HASH UNIQUE | | 168K| 2800K| 7960K| 1144 (2)| 00:00:14 || 2 | VIEW | | 168K| 2800K| | 201 (2)| 00:00:03 || 3 | UNION-ALL | | | | | | ||* 4 | INDEX FAST FULL SCAN| CRF_P2P_LOAN_AMOUNT_UIDX | 167K| 4263K| | 189 (3)| 00:00:03 ||* 5 | TABLE ACCESS FULL | ZH_CRF_P2P_ACCOUNT_INFO | 759 | 18975 | | 12 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter("T"."LOAN_AMOUNT"<=200000) 5 - filter("T"."LOAN_AMOUNT"<=200000)18 rows selected.SQL> 八. 用group by
这里用分组操作代替排序去重 ，效果已经很明显了，至此原本49S的SQL也已优化到3SSQL> explain plan for select id_card from (select t.id_card from clspuser.crf_p2p_account_info t where t.loan_amount <= 200000 union all select t.id_card from clspuser.zh_crf_p2p_account_info t where t.loan_amount <= 200000 ) a group by a.id_card; Explained.SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1930908435----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 168K| 2800K| 218 (10)| 00:00:03 || 1 | HASH GROUP BY | | 168K| 2800K| 218 (10)| 00:00:03 || 2 | VIEW | | 168K| 2800K| 201 (2)| 00:00:03 || 3 | UNION-ALL | | | | | ||* 4 | INDEX FAST FULL SCAN| CRF_P2P_LOAN_AMOUNT_UIDX | 167K| 4263K| 189 (3)| 00:00:03 ||* 5 | TABLE ACCESS FULL | ZH_CRF_P2P_ACCOUNT_INFO | 759 | 18975 | 12 (0)| 00:00:01 |----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter("T"."LOAN_AMOUNT"<=200000) 5 - filter("T"."LOAN_AMOUNT"<=200000)18 rows selected.SQL> 0 0
- union union all group by 优化一例
- join & union & group by
- union all 和 group by 的顺序问题
- 数据库查询常用关键字JOIN与LEFT JOIN/UNION与UNION ALL/GROUP BY
- union与union all与order by
- SQL优化及UNION ALL替代UNION
- 【sql 优化】union 、union all、or使用
- Sql性能优化之UNION、UNION ALL
- mysql union，union all的优化
- SQL优化及UNION ALL替代UNION
- SQL Union和SQL Union All两者用法区别效率以及与order by 和 group by配合问题
- 关于union all 的优化
- union,union all 等
- union all 和union
- UNION,UNION ALL,MINUS
- union union all
- UNION and UNION All
- union 和 union all
- c#编码解码---间断更新中
- Jersey复杂案例 完整的增删改查Restful服务
- python匿名函数
- 神的回复，让一道jvm的题目瞬间简单，在中国，评论永远是最精彩的
- android之shape的说明
- union union all group by 优化一例
- 整装待发，重新出发，不放弃，这就是生活
- iOS 10 消息推送（UserNotifications）秘籍总结（一，二）
- mac下使用ll等指令
- bson json区别
- 最理想的正方形
- 中文乱码原因解析
- ETL系统增量抽取方案
- 适合新手了解的GUN/Linux起源
