标量子查询优化(用group by 代替distinct)

原创 2013年12月06日 09:55:23
标量子查询优化 

当使用另外一个SELECT 语句来产生结果中的一列的值的时候,这个查询必须只能返回一行一列的值。这种类型的子查询被称为标量子查询

在某些情况下可以进行优化以减少标量子查询的重复执行,但更糟糕的场景是每一行都需要标量子查询的执行。


explain plan for SELECT B.EMP_NO,
       B.CUST_NO,
       B.CUST_NAME,
       A.CARD_NO,
       A.TRANS_AMT,
       A.TRANS_ATTR,
       /*(0 ?? 1 鲁盲 2 鲁盲?禄?4 ??)*/
       A.TRANS_TIME,
       A.SEQNO,
       A.OLD_TRANSDATE
  FROM (SELECT * FROM DWF.F_EVT_REAL_JOURLIST WHERE TRANS_TYPE = '00') A
  LEFT JOIN (SELECT AGMT_ID,
                    CUST_MAGR EMP_NO,
                    CUST_NO,
                    (SELECT DISTINCT PTY_NAME
                       FROM DWF.F_PTY_TABLE
                      WHERE PTY_ID = A.CUST_NO
                        AND START_DT <= TO_DATE('2012-09-30', 'YYYY-MM-DD')
                        AND END_DT > TO_DATE('2012-09-30', 'YYYY-MM-DD')) CUST_NAME
               FROM DWF.F_AGT_CADB_BOOK_H A
              WHERE START_DT <= TO_DATE('2012-09-30', 'YYYY-MM-DD')
                AND END_DT > TO_DATE('2012-09-30', 'YYYY-MM-DD')) B ON A.CARD_NO =
                                                                       B.AGMT_ID;
--220109

SELECT AGMT_ID,
                    CUST_MAGR EMP_NO,
                    CUST_NO,
                    (SELECT DISTINCT PTY_NAME
                       FROM DWF.F_PTY_TABLE
                      WHERE PTY_ID = A.CUST_NO
                        AND START_DT <= TO_DATE('2012-09-30', 'YYYY-MM-DD')
                        AND END_DT > TO_DATE('2012-09-30', 'YYYY-MM-DD')) CUST_NAME
               FROM DWF.F_AGT_CADB_BOOK_H A
              WHERE START_DT <= TO_DATE('2012-09-30', 'YYYY-MM-DD')
                AND END_DT > TO_DATE('2012-09-30', 'YYYY-MM-DD')
查询返回了204947条记录
 

###########################################################################################################################
Plan hash value: 579615344

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		         | Name		         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	         |			 |	1 |	     |	  220K|00:00:12.12 |	 781K|	  280K|       |       |       |
|   1 |  SORT UNIQUE		         |			 |155K    |	   1 |	  155K|00:00:06.52 |	 501K|	 2882 |  2048 |  2048 | 2048  (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID    | F_PTY_TABLE	         |155K    |	   1 |	  155K|00:00:05.41 |	 501K|	 2882 |       |       |       |
|*  3 |    INDEX RANGE SCAN	         | SYS_C0052731	         |155K    |	   1 |	  188K|00:00:04.17 |	 313K|	  448 |       |       |       |
|*  4 |  HASH JOIN OUTER	         |			 |      1 |	1399K|	  220K|00:00:12.12 |	 781K|	  280K|    20M|  2674K|   25M (0)|
|*  5 |   TABLE ACCESS FULL	         | F_EVT_REAL_JOURLIST   |  1     |	 145K|	  220K|00:00:00.08 |	7904 |	 7900 |       |       |       |
|   6 |   VIEW			         |			 |      1 |	3929K|	  204K|00:00:11.54 |	 773K|	  272K|       |       |       |
|*  7 |    TABLE ACCESS FULL	         | F_AGT_CADB_BOOK_H     |      1 |	3929K|	  204K|00:00:04.68 |	 272K|	  270K|       |       |       |
--------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("END_DT">TO_DATE(' 2012-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("PTY_ID"=:B1 AND "START_DT"<=TO_DATE(' 2012-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("START_DT"<=TO_DATE(' 2012-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   4 - access("F_EVT_REAL_JOURLIST"."CARD_NO"="B"."AGMT_ID")
   5 - filter("TRANS_TYPE"='00')
   7 - filter(("START_DT"<=TO_DATE(' 2012-09-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DT">TO_DATE(' 2012-09-30 00:00:00',
	      'syyyy-mm-dd hh24:mi:ss')))


43 rows selected.
此时对F_PTY_TABLE索引扫描了155K次,回表了155K次 这种效率能高吗?
改写为关联
###########################################################################################
SELECT b.emp_no,
       b.cust_no,
       b.cust_name,
       a.card_no,
       a.trans_amt,
       a.trans_attr,
       a.trans_time,
       a.seqno,
       a.old_transdate
  FROM (SELECT * FROM dwf.f_evt_real_jourlist WHERE trans_type = '00') a
  LEFT JOIN (SELECT agmt_id,
                    cust_magr    emp_no,
                    cust_no,
                    c_n.pty_name AS cust_name
               FROM dwf.f_agt_cadb_book_h a
               LEFT JOIN (SELECT pty_name, pty_id
                           FROM dwf.f_pty_table
                          WHERE start_dt <=
                                to_date('2012-09-30', 'YYYY-MM-DD')
                            AND end_dt > to_date('2012-09-30', 'YYYY-MM-DD')
                          GROUP BY pty_name, pty_id) c_n
                 ON c_n.pty_id = a.cust_no
              WHERE start_dt <= to_date('2012-09-30', 'YYYY-MM-DD')
                AND end_dt > to_date('2012-09-30', 'YYYY-MM-DD')) b
    ON a.card_no = b.agmt_id;
 

这里巧妙的运用了group by 来去除重复数据,像伟大的教主致敬.


 

MySQL查询子句(group by,limit,union,order by等)

Order by按照字段值进行排序,默认升序(asc)。校对规则决定排序关系。 order by 字段 升序|降序(asc|desc),Order by允许多字段排序。 指的是,先按第一个字段排序...

MySQL子查询优化-子语句含有group by时

当我使用Mysql进行下列语句的查询时: select count(1) from jy_info_user  where user_card != ''  and id IN (select ...

QGIS中的金字塔机制优化

QGIS中快速建立金字塔

QGis的源代码下载以及windows平台使用脚本自动化编译

环境介绍 QGis 2.7+ 当前2.8win7 从GitHub下载代码 代码仓库 官方的安装文档 windows平台安装稍微看下1、2节,主要看4.1就OK 编译流程总述 ...

mysql GROUP BY 代替DISTINCT 遇到的问题及解决

mysql上group by 代替distinct 分析及实现
  • Veebo
  • Veebo
  • 2016年07月09日 19:22
  • 608

解析mysql中:单表distinct、多表group by查询去除重复记录

本篇文章是对mysql中的单表distinct、多表group by查询去除重复记录进行了详细的分析介绍,需要的朋友参考下 单表的唯一查询用:distinct 多表的唯一查询用:group by di...
  • Hollboy
  • Hollboy
  • 2014年02月24日 10:45
  • 862

通过转成SQL来进行查询,解决hql不能在distinct,group by结果集上使用count的问题

public Long getDataTotal(String hql,Map alias,Object[] args) { QueryTranslatorImpl queryTranslato...

数据库查询去重group by和distinct的理解

前言在使用mysql时,有时需要查询出某个字段不重复的记录,虽然mysql提供 有distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用它来返回不重复记录的条数,而不是用它来返回不重记...

distinct 和 group by 在查询多列数据去重复时的区别在哪?

distinct 和 group by 在查询多列数据去重复时的区别在哪? [问题点数:40分,结帖人luxer_1985]             不显示删除回复           显示所...

group by与distinct效率分析及优化措施

如何使用group by进行去重 因为mysql的distinct在结果集中,全部不同,才可以去重。 所以,当我们进行去重处理的时候,需要单独对某列进行去重,可以使用group by子句进行分组去...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:标量子查询优化(用group by 代替distinct)
举报原因:
原因补充:

(最多只允许输入30个字)