导读：Oracle Code 2019 开发者大会于4月16日在深圳举行，这是甲骨文公司在中国举办的第三届Oracle Code 开发者大会。大会主要面向IT架构师和开发人员，旨在帮助开发人员深入了解最新的开发技术、开发实践和未来发展的趋势。
云和恩墨CTO、Oracle ACE Director杨廷琨受邀参加本次大会，并于会上发表主题演讲《高效SQL语句编写》，与大家分享了高效SQL语句编写的种种技巧。没有机会亲临现场？跟随本文一起学习吧！
在工作中高效的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和应用开发人员能够较早的介入，将性能隐患扼杀于萌芽阶段，确保线上应用的稳定、高效运行。
专家指导+软件支持，云和恩墨愿为您的数据库保驾护航！
