union union all group by 优化一例

最新推荐文章于 2023-09-04 15:21:17 发布
最新推荐文章于 2023-09-04 15:21:17 发布
阅读量5.2k 收藏 5
点赞数
分类专栏： 优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/EVISWANG/article/details/53159212
版权

一. 原执行计划

     本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 processed

SQL>

三．加索引：

看到执行计划全表扫描，常规想法加索引，但此处特殊在索引的选择性还是可以的，但因为数据原因大部分都是符合要求的，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也已优化到3S 

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 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>










EVISWANG
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL性能优化
12-20
说明：对查询结果进行排序会大大的降低系统的性能，group与union都会对数据作排序，要耗费较多的内存，视状况用union all既可，不然有时数据太大又要进行union的排序，会导致Oracle数据库SORT_AREA_SIZE不足发生系统...
group By 和 Union 、 Union all的用法
dibengling8442的博客
08-17 681
我学习的是MySQL，学习写sql语句过程中遇到Group By 和 Union。 大家乐意看这两个链接，写的很好 Group By： www.cnblogs.com/rainman/archive/2013/05/01/3053703.html Union : http://www.jb51.net/article/48933.htm 博主只是为了加强记忆才写博客的，基本上是盗...
union和group by使用日记
Lanyangyangb的专栏
06-05 9430
1、union和union all的用法  union all是将连接的两个查询结果表连接起来； union是将连接的两个查询结果表连接起来并做去重处理； 注意：如果有大量的查询结果表需要连接起来，可以先用union all，这样除去了去重的时间效率会更高，外部再使用distinct来去重 SELECT DISTINCT name from ( SELECT
两表union 如何保证group by 字段唯一
锵锵锵锵蒋的博客
09-04 1104
当要计算的指标可能来源多个表时，可能会使用到union all把不同的表中计算的指标合起来。写法一：group by之后union all ，之后再次group by保证分组字段的唯一。Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；写法二：开始不分组，将查到的分组字段union all 之后group by。Union All：对两个结果集进行并集操作，包括重复行，不进行排序；另外，回顾union和union all的区别：union会自动。
MySQl语句中的union ,order by 、group by 、having、where等的用法
acwing的博客
12-02 1933
union union 和 union all 联合 union 保证两张表的列 相同 union 可以连接两张表的查询结果 去掉重复的数据 union all 不能去掉重复的数据 having 如果对新列进行条件查询时 不能使用where 因为where只对原表发挥作用 对新列进行条件查询时 必须使用having where 是在分组之前使用，表示从所有数据中筛选出部分数据，以完成分组的要求， 在where子句中不允许使用统计函数，没有group by子句也可以使用。 having 是在分组
SQL性能区别（1）——union(all)、in/exists、join on/where、row_number/rank、group by/distinct
xiaozhou的博客
04-10 1792
1. union 和 union all的区别 参考来源： SQL语句中：UNION与UNION ALL的区别 数据库中UNION和UNION ALL的区别以及并集怎么取得 关于Union和Union All的区别以及用法 先说说这两个用法的要求： 必须选择相同数量的列，每条select语句中的列的顺序也必须相同，这些列也必须拥有相似的数据类型。 也就是说，要达到六耳猕猴和通背猿猴的那种匹配度...
union和union all的区别,union 和 union all + group by 去重效果
chw65897的博客
08-08 4648
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。 请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。 UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。 sql语法: select id,`name` FROM stu union select id,`name` from stu2 在此建了两张表来比较union和union all的区别: stu
union all 和 group by 的顺序问题
热门推荐
Sarah Cla的专栏
08-27 1万+
通过三个实验，来看 union all 及 group by 不同的使用顺序对CPU时间及耗时的影响 1. 先Union All， 再Group By select inctmid ctmid,cnlid,inwhsid whsid,sum(qty) qty,goodsid  from (     select inctmid,cnlid,inwhsid,qty,goodsid   
MySQL高级SQL语句（二）
在熙丶的博客
06-18 240
二阶段的高阶语句
grouping sets操作符简化group by+union all操作
u012955829的博客
05-08 1706
在一个GROUP BY 查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果进行UNION ALL操作。GROUPING SETS就是一种将多个GROUP BY逻辑UNION写在一个HIVE SQL语句中的便利写法。GROUPING SETS会把在单个GROUP BY逻辑中没有参与GROUP BY的那一列置为NULL值，这样聚合出来的结果，未被GROUP BY的列将显示为NULL。 如果说聚合函数（Simple UDAF / Generic UDAF）是HQL聚合数据查询或分析的中枢处
sql经典语句一部分
07-31
当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并...
Java开发实战1200例(第1卷).(清华出版.李钟尉.陈丹丹).part3
06-12
书名：《Java开发实战1200例(第I卷)》(清华大学出版社.李钟尉，陈丹丹) PDF格式扫描版，全书分为24章，共817页。2011年1月出版。 全书压缩打包成4部分，这是第3部分 注：本系列图书的第I、II卷再版时均相应改名为...
oracle 失效对象
EVISWANG的博客
12-14 5650
SQL> select count(*) from dba_objects where status='INVALID';   COUNT(*) ---------- 19 SQL> SQL> col owner for a10 SQL> select owner, object_name, object_type from dba_objects where status='
ORA-16957: SQL Analyze time limit interrupt
EVISWANG的博客
12-21 4862
巡查alert发现如下报错： Wed Dec 09 22:16:37 2015 Errors in file /u01/PROD/oracle/diag/rdbms/prod/PROD1/trace/PROD1_j000_75313.trc: ORA-16957: SQL Analyze time limit interrupt Wed Dec 09 23:00:08 2015 End
orabbix 启动报错 WARNING: Error while registering Oracle JDBC Diagnosability MBean.
EVISWANG的博客
01-22 1948
一. 报错信息如下： [root@adminm conf]# /etc/init.d/orabbix start Starting Orabbix service: [root@adminm conf]# Jan 22, 2018 2:37:06 p.m. oracle.jdbc.driver.OracleDriver registerMBeans WARNING: Error while
mysql union all和group by一块用时
最新发布
09-09
因此，在使用 UNION ALL 和 GROUP BY 一块使用时，首先执行 UNION ALL 操作以获取合并的结果集，然后再应用 GROUP BY 对结果集进行分组和聚合操作。 请注意，使用 UNION ALL 和 GROUP BY 可能会导致较大的查询开销...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值