# 标量子查询优化（用group by 代替distinct）

804人阅读 评论(0)

标量子查询优化

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
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
WHERE START_DT <= TO_DATE('2012-09-30', 'YYYY-MM-DD')
AND END_DT > TO_DATE('2012-09-30', 'YYYY-MM-DD')

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

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



0
0

* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
个人资料
• 访问：1183763次
• 积分：38721
• 等级：
• 排名：第101名
• 原创：2823篇
• 转载：14篇
• 译文：0篇
• 评论：46条
文章分类
阅读排行
评论排行
最新评论