记录一次生产环境SQL优化

  针对多张表的关联统计的一次SQL优化,主表(M表,500万左右数据),关联表1(C表,客户表,千万级数据),关联表2(CER表,客户员工关系表,千万级数据),关联表3(E表,员工表,数据量忽略不计),做了一次统计的SQL开发,主要就是一个优化的方法问题,以后写SQL需要注意一下,使用的是Oracle的数据库

一、第一版SQL编写

1 SELECT COUNT(CASE WHEN M.INDI_ID = '0001' TEHN 1 END) COUNT1 FROM (
2 SELECT M.*, E.* FROM M 
3 LEFT JOIN C
4 ON M.CUST_NO = C.CUST_NO
5 LEFT JOIN CER 
6 ON C.CUST_NO = CER.CUST_NO
7 LEFT JOIN E
8 ON CER.EMP_NO = E.EMP_NO
9 )

这种sql 是第一次写的,本来是这样看起来清晰一些,放到数据量大的情况下,直接查询不出来了,好几分钟也出不来,只能进一步优化了,以后写SQL避免这种从查询出来的数据中在去嵌套查询的方法,切记,万不得已不要加这种嵌套查询

二、第二版SQL优化

 Oracle parallel() 并行优化

SELECT /*+parallel(M, 4)*/ COUNT(CASE WHEN M.INDI_ID = '0001' TEHN 1 END) COUNT1 FROM M 
LEFT JOIN C
ON M.CUST_NO = C.CUST_NO
LEFT JOIN CER 
ON C.CUST_NO = CER.CUST_NO
LEFT JOIN E
ON CER.EMP_NO = E.EMP_NO

去掉外面的嵌套from,直接加上并行优化,但是还是执行时间有些长,继续优化,加上日期查询条件字段的索引,但是效果不是很明显,跟搞数据开发的人讨论,于是有了最终的优化方案

三、最终版SQL

 Oracle use_hash(M, C, CER, E) 优化

SELECT /*+use_hash(M, C, CER, E)*/ COUNT(CASE WHEN M.INDI_ID = '0001' TEHN 1 END) COUNT1 FROM M 
LEFT JOIN C
ON M.CUST_NO = C.CUST_NO
LEFT JOIN CER 
ON C.CUST_NO = CER.CUST_NO
LEFT JOIN E
ON CER.EMP_NO = E.EMP_NO

use_hash 官方说明

The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join.

翻译过来就是:

USE_HASH 提示使 Oracle 通过散列连接将每个指定的表与另一个行源连接起来。 USE_HASH 提示的语法是 USE_HASH(table table),其中 table 是要连接到行源的表,该表是通过使用哈希连接以连接顺序连接先前的表而产生的。

 

posted @ 2022-03-03 16:14  ssc在路上  阅读(64)  评论(0编辑  收藏  举报