干货分享丨高效SQL语句编写-Oracle Code 2019中云和恩墨CTO杨廷琨的分享

导读：Oracle Code 2019 开发者大会于4月16日在深圳举行，这是甲骨文公司在中国举办的第三届Oracle Code 开发者大会。大会主要面向IT架构师和开发人员，旨在帮助开发人员深入了解最新的开发技术、开发实践和未来发展的趋势。

云和恩墨CTO、Oracle ACE Director杨廷琨受邀参加本次大会，并于会上发表主题演讲《高效SQL语句编写》，与大家分享了高效SQL语句编写的种种技巧。没有机会亲临现场？跟随本文一起学习吧！（演讲PPT点击阅读原文获取）

在工作中高效的SQL是非常必要的。

SQL是世界上第二大的编程语言。超过50%的开发者使用SQL；80%的数据库问题是SQL引起的；80%的性能问题来自20%的SQL语句；高并发环境中，单条SQL语句可能导致整个数据库出现性能故障。

本次分享主要包括四个部分：

• 合理运用新特性

• 数据集整体处理 

• 设计SQL执行计划 

• 严格过滤数据

让我们逐一展开，一起编写高效的SQL。

合理运用新特性

比如我们考虑这种场景。

从一张表取数据插入到另一张表中，此外需要为插入的目标表做一个应用级的日志表，也就是说在插入目标表的同时，还需要将相同的数据插入到日志表中。

一般情况下，我们有以下几种选择：

• CREATE TRIGGER

• DOUBLE INSERT

• OPEN CURSOR

• BULK INTO VARIABLE

以上几种方式各有各的缺点。比如采用CREATE TRIGGER方式：

CREATE OR REPLACE TRIGGER T_INSERT_TAR 
AFTER INSERT ON T_TAR
FOR EACH ROW
BEGIN
INSERT INTO T_LOG (ID, NAME) 
VALUES (:NEW.ID, :NEW.NAME); 
END;

不仅太“重”，而且实现与需求有差异，还增加后续维护成本，触发器效率较低。

其它三种方式也逃不出太过复杂，或者效率低的弊端。

而当我们使用新特性：INSERT ALL 时：

INSERT ALL INTO T_TAR (ID, NAME) 
INTO T_LOG (ID, NAME)
SELECT ID, NAME FROM T_ORG;

这些问题就迎刃而解了。

合理地运用新特性，对高效SQL的编写大有裨益。

数据集整体处理

举个例子。

判断表空间是否自动扩展时，我们可以采用以下方式：

select distinct tablespace_name, autoextensible  
from DBA_DATA_FILES
where autoextensible = 'YES'
union
select distinct tablespace_name, autoextensible 
from DBA_DATA_FILES
where autoextensible = 'NO'
and tablespace_name not in
(select distinct tablespace_name 
from DBA_DATA_FILES
where autoextensible = 'YES');

得出结果：

TABLESPACE_NAME AUT 
--------------- --- 
SYSAUX          YES 
SYSTEM          YES 
TEST            NO 
UNDOTBS1        YES 
USERS           YES

优点：思路清晰

缺点：效率低效，冗余严重

• 三次扫描DBA_DATA_FILES视图

• 包含三次DISTINCT操作

• 包含UNION数据集操作

• 包含NOT IN子查询

我们可以得出正确的结论，然而这显然不是我们想要的高效SQL。如果采取“数据集整体处理“的思维来编写，我们可以写出以下SQL：

SELECT TABLESPACE_NAME, MAX(AUTOEXTENSIBLE) 
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME；

可以得出相似的结论。

TABLESPACE_NAME MAX 
--------------- --- 
SYSAUX          YES 
SYSTEM          YES 
TEST            NO 
UNDOTBS1        YES 
USERS           YES

再举个例子。

问题：在5X5的方格棋盘中（如图），每行、列、斜线(斜线不仅仅包 括对角线)最多可以放两个球，如何摆放才能放置最多的球，这样的摆 法总共有几种?输出所有的摆法。

要求：用一句SQL实现。

输出格式：从方格棋盘第一行至第5行，每行从第一列到第5列依次输出， 0表示不放球，1表示放球。

例如：10010。一行输出一个行号和一个解，按解所在的列字符串顺序从大到小排序。

假设每行放两个球，首先构造出行的10种情况：

SQL> with c as
(select rownum - 1 c from dual connect by rownum <= 2), 
line_seed as
(select c1.c || c2.c || c3.c || c4.c || c5.c line,
c1.c c1, c2.c c2, c3.c c3, c4.c c4, c5.c c5
from c c1,c c2,c c3,c c4,c c5
where c1.c+c2.c+c3.c+c4.c+c5.c=2
order by 1 desc)
select * from line_seed;

LINE     C1    C2    C3    C4    C5 
----- ----- ----- ----- ----- ----- 
11000     1     1     0     0     0 
10100     1     0     1     0     0 
10010     1     0     0     1     0 
10001     1     0     0     0     1  
01100     0     1     1     0     0 
01010     0     1     0     1     0  
01001     0     1     0     0     1  
00110     0     0     1     1     0  
00101     0     0     1     0     1 
00011     0     0     0     1     1

如果按照每个条件分开处理的SQL如下：

with c as
(select rownum - 1 c from dual connect by rownum <= 2),
line_seed as
(select c1.c || c2.c || c3.c || c4.c || c5.c line, c1.c c1, c2.c c2, c3.c c3, c4.c c4, c5.c c5 from c c1, c c2, c c3, c c4, c c5
where c1.c + c2.c + c3.c + c4.c + c5.c = 2
order by 1 desc)
select rownum, line1.line || line2.line || line3.line || line4.line || line5.line result
from line_seed line1, line_seed line2, line_seed line3, line_seed line4, line_seed line5 where line1.c1 + line2.c1 + line3.c1 + line4.c1 + line5.c1 = 2
and line1.c2 + line2.c2 + line3.c2 + line4.c2 + line5.c2 = 2
and line1.c3 + line2.c3 + line3.c3 + line4.c3 + line5.c3 = 2
and line1.c4 + line2.c4 + line3.c4 + line4.c4 + line5.c4 = 2
and line1.c5 + line2.c5 + line3.c5 + line4.c5 + line5.c5 = 2
and line3.c1 + line4.c2 + line5.c3 <= 2
and line2.c1 + line3.c2 + line4.c3 + line5.c4 <= 2
and line1.c1 + line2.c2 + line3.c3 + line4.c4 + line5.c5 <= 2
and line1.c2 + line2.c3 + line3.c4 + line4.c5 <= 2
and line1.c3 + line2.c4 + line3.c5 <= 2
and line1.c3 + line2.c2 + line3.c1 <= 2
and line1.c4 + line2.c3 + line3.c2 + line4.c1 <= 2
and line1.c5 + line2.c4 + line3.c3 + line4.c2 + line5.c1 <= 2
and line2.c5 + line3.c4 + line4.c3 + line5.c2 <= 2
and line3.c5 + line4.c4 + line5.c3 <= 2;

怎么样根据条件对数据集整体处理呢？

with c as
(select rownum - 1 c from dual connect by rownum <= 2), 
lines as
(select to_number(c1.c || c2.c || c3.c || c4.c || c5.c) line 
from c c1, c c2, c c3, c c4, c c5 
where c1.c + c2.c + c3.c + c4.c + c5.c
order by 1 desc)
select ltrim(to_char(line1.line, '09999')) || chr(10)
 || ltrim(to_char(line2.line, '09999')) || chr(10)
 || ltrim(to_char(line3.line, '09999')) || chr(10) 
 || ltrim(to_char(line4.line, '09999')) || chr(10) 
 || ltrim(to_char(line5.line, '00009')) result
from lines line1, lines line2, lines line3, lines line4, lines line5
where line1.line + line2.line + line3.line + line4.line + line5.line = 22222
and ltrim(to_char(line1.line + 10*line2.line + 100*line3.line + 1000*line4.line + 10000*line5.line), '012') is null 
and ltrim(to_char(10000*line1.line + 1000*line2.line + 100*line3.line + 10*line4.line + line5.line), '012') is null 
and rownum= 1；

大家可以思考一下。点击“阅读原文”，PPT中将给出详细解析。

我们建议:

• 能够用单条SQL处理的，就不要使用多条；

• 能够只扫描一次的，就不要扫描多次；

• 从整体上处理数据，避免单条处理逻辑。

设计SQL执行计划

客户某SQL语句执行缓慢，消耗大量逻辑读：

SQL> EXPLAIN PLAN FOR
SELECT VCM.POL_ID, TC.MOBILE, TC.FIRST_NAME PH_NAME,
PKG_UTILS.F_GET_DESC('T_TITLE', DECODE(TC.GENDER,'M', '1', 'F', '2'), '211'), 
PKG_UTILS.F_GET_DESC('V_PRO_LIFE_3',
(SELECT T.PROD_ID FROM T_CON_PROD T WHERE T.POL_ID = VCM.POL_ID AND T.MASTER_ID IS NULL), '211'), 
SUM(TPA.FEE_AMOUNT), VCM.POLICY_CODE, PKG_UTILS.F_GET_DESC('T_LIA_STAT',VCM.LIA_STATE, '211'), 
PKG_UTILS.F_GET_DESC('T_SAL_CHAN', VCM.CHANN_TYPE, '211'), VCM.CHANN_TYPE,TC.CUSTOMER_ID, TCO.ORGAN_CODE
FROM V_CON_MAS VCM, T_CON_MAS TCM, T_AGENT TA, T_CUSTOMER TC, 
T_POH VPH,T_COM_ORG TCO, T_P_A TPA
WHERE VCM.POL_ID = VPH.POL_ID
AND VCM.SERVICE_AGENT = TA.AGENT_ID 
AND VCM.POL_ID = TCM.POL_ID
AND VPH.PARTY_ID = TC.CUSTOMER_ID 
AND VCM.ORGAN_ID = TCO.ORGAN_ID
AND VCM.POL_ID = TPA.POL_ID
AND VCM.LIABILITY_STATE = 1
AND TPA.FEE_TYPE IN (41, 569)
AND TPA.FEE_STATUS <> 2
AND (EXISTS (
SELECT 1 FROM T_PO_CH T, T_CH TC WHERE T.MAS_C_ID = TC.CH_ID AND TC.CH_ID = TPA.CH_ID AND T.SERV_ID = 3 ))
AND TPA.DUE_TIME >= (TRUNC(:B1 ) + 7)
AND TPA.DUE_TIME < (TRUNC(:B1 ) + 8)
AND REGEXP_LIKE(TRIM(TC.MOBILE), '^\d{11}$')
AND NOT EXISTS (
SELECT 1 FROM T_DATA_EXT SDE, T_DATA TSD
WHERE SDE.DATA_ID = TSD.DATA_ID AND SDE.RELAT_VALUE_1 = VCM.POL_ID AND TSD.SMS_ID = 12 AND SDE.RELAT_VALUE_2 = TO_CHAR(:B1 , 'yyyy-MM-dd')) 
GROUP BY VCM.POL_ID, TC.MOBILE, TC.FIRST_NAME, TC.GENDER, VCM.POLICY_CODE, VCM.LIABILITY_STATE, VCM.CHANNEL_TYPE, TC.CUSTOMER_ID, TCO.ORGAN_CODE;

执行计划如下：

让我们来重现这个问题：

SQL> create table t_objects as select * from dba_objects; 表已创建。
SQL> create index ind_obj_id on t_objects(object_id); 索引已创建。
SQL> create table t_tab as select * from dba_tables; 表已创建。
SQL> create table t_ind as select * from dba_indexes; 表已创建。
SQL> create index ind_tab_name on t_tab(table_name); 索引已创建。
SQL> create index ind_ind_name on t_ind(index_name); 索引已创建。
SQL> create view v_seg as
select owner, table_name, tablespace_name, blocks from t_tab where temporary = 'N'
union all
select owner, index_name, tablespace_name, num_rows from t_ind where status = 'N/A';
视图已创建。

通过hint走不同的执行计划，资源消耗大相径庭。

select /*+ index(t_tab ind_tab_name) */ obj.owner, obj.object_name, created, v.blocks 
from t_objects obj, v_seg v
where obj.object_id = 12345
and obj.object_name = v.table_name;

select /*+ index(v.t_tab ind_tab_name) */ obj.owner, obj.object_name, created, v.blocks 
from t_objects obj, v_seg v
where obj.object_id = 12345
and obj.object_name = v.table_name;

select /*+ push_pred(v) */ obj.owner, obj.object_name, created, v.blocks 
from t_objects obj, v_seg v
where obj.object_id = 12345
and obj.object_name = v.table_name;

由此可见，好的性能是设计出来的，因此如果想写出高效的SQL语句，要从一开始就考虑好这个SQL的执行计划，驱动表是谁，采用何种JOIN方式连接到被驱动表。设计思路是一方面，另一方面是保证执行计划符合设计思路，这时候就需要用到提示的功能。熟悉提示的功能，可以更好的控制SQL的执行路径，绕过bug或性能问题，强制SQL按照设计思路去执行。

严格过滤数据

先来看一个简单的问题。找出所有100以内的质数。

SQL> WITH T
AS
(SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL < 100) 
SELECT RN FROM T
WHERE RN>1
MINUS
SELECT A.RN*B.RN FROM T A,T B 
WHERE A.RN <= B.RN
AND A.RN>1
AND B.RN>1;




    RN 
------
     2 
     3 
     5
. 
. 
.
    97
已用时间: 00: 00: 00.07

执行时间并不长。

那么10000以内的质数呢？

SQL> WITH T 
AS
(SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL < 10000) 
SELECT RN FROM T
WHERERN>1
MINUS
SELECT A.RN*B.RN FROM T A,T B 
WHERE A.RN <= B.RN
AND A.RN>1
AND B.RN>1;


已选择 1229 行。
已用时间: 00: 01: 56.88


统计信息 
----------------------------------------------------------
     521  recursive calls 
    1702  db block gets
  180013  consistent gets
   65297  physical reads 964 redo size
   22473  bytes sent via SQL*Net to client
    1442  bytes received via SQL*Net from client
      83  SQL*Net roundtrips to/from client 
       2  sorts (memory)
       1  sorts (disk) 
    1229  rows processed

近两分钟的执行时间。我们能否用数据过滤的思维来优化呢？

SQL> WITH T
AS
(SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL < 10000) 
SELECT RN FROM T
WHERE RN > 1
MINUS
SELECT A.RN * B.RN FROM T A, T B 
WHERE A.RN <= B.RN
AND A.RN > 1
AND A.RN <= 100
AND B.RN > 1
AND B.RN <= 5000;


已选择 1229 行。
已用时间: 00: 00: 00.62


统计信息 
----------------------------------------------------------
    10  recursive calls
    23  db block gets
  1831  consistent gets
    16  physical reads
   624  redo size
 22473  bytes sent via SQL*Net to client
  1442  bytes received via SQL*Net from client
    83  SQL*Net roundtrips to/from client 3 sorts (memory)
     0  sorts (disk)
  1229  rows processed

优化效果十分显著。通过更严格的筛选，可以对这个SQL进一步进行优化。大家可以仔细思考，再参考PPT中的内容。

留下一道思考题：

总结

高效SQL的编写有许多技巧，如果能够遵循以下原则，定能起到事半功倍的效果：

• 处理问题从整体上考虑，避免单条操作

• 第一步结果集最小原则，合理选择驱动表

• 利用新特性、分析函数避免重复多次的扫描，减少自关联

• 在每一个步骤上尽可能过滤掉无用数据

• 多写SQL：熟能生巧

• 多思考：算法为王

• 持之以恒：优化无止境

整理丨陈一宁 （阿文、haha、坤楠对此文亦有贡献）

---

云和恩墨自主研发的SQL审核软件SQM，能自动抓取开发、测试与生产环境数据库中的对象设计与SQL信息，并依据既定的审核规则，对这些信息进行分析。

分析对象设计与SQL中的潜在性能⻛险，使得DBA和应用开发人员能够较早的介入，将性能隐患扼杀于萌芽阶段，确保线上应用的稳定、高效运行。

专家指导+软件支持，云和恩墨愿为您的数据库保驾护航！

产品咨询：marketing@enmotech.com