Oracle sql 性能优化（二）

theme: channing-cyan

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第16天，点击查看活动详情 >>

性能优化

2.11【推荐】用 CASE … WHEN … THEN … ELSE … END 格式减少表的扫描次数

说明：

在我们的开发维护工作中常常必须基于多组数据表计算不同的聚集，需要多次扫

描业务数据表，而 CASE 语句就可以把多次扫描合并成一次来完成。

举例：

Demo

Avoided

SELECT COUNT(0)

FROM BLACKLIST_SUBS

WHERE BLACKLIST_TYPE='A'

AND SUBS_ID=24147;

SELECT COUNT(0)

FROM BLACKLIST_SUBS

WHERE BLACKLIST_TYPE='B'

AND SUBS_ID=24147;

SELECT COUNT(0)

FROM BLACKLIST_SUBS

WHERE BLACKLIST_TYPE='C'

AND SUBS_ID=24147;

Preferred

SELECT COUNT(CASE BLACKLIST_TYPE WHEN 'A' THEN 1 ELSE NULL 

END) A,

COUNT(CASE BLACKLIST_TYPE WHEN 'B' THEN 1 ELSE NULL

END) B,

COUNT(CASE BLACKLIST_TYPE WHEN 'C' THEN 1 ELSE NULL

END) C

FROM BLACKLIST_SUBS

WHERE SUBS_ID = 24147;

\

2.12【推荐】用 WHERE 子句替换 HAVING 子句

说明：

避免使用 HAVING 子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤，

这个处理需要排序、统计等操作。如果能通过 WHERE 子句限制记录的数目，那就能

减少这方面的开销。

举例：

Demo

Avoided

SELECT REGION, AVG(LOG_SIZE)

FROM LOCATION

GROUP BY REGION

HAVING REGION != 'SYDNEY'

AND REGION != 'PERTH'

Preferred

SELECT REGION, AVG(LOG_SIZE)

FROM LOCATION

WHERE REGION != 'SYDNEY'

AND REGION != 'PERTH'

GROUP BY REGION

\

2.13【推荐】LIKE 子句尽量前端匹配

说明：

LIKE 参数使用得非常频繁，因此如果能够对于 LIKE 子句使用索引，将很好地提高

查询的效率。

举例：

Demo

Avoided

SELECT CUST_ID, CUST_NAME

FROM CUST

WHERE CUST_NAME LIKE '%Tom%'

Preferred

SELECT CUST_ID, CUST_NAME

FROM CUST

WHERE CUST_NAME LIKE 'Tom%'

\

2.14【强制】程序代码中禁止使用 DB_LINK

\

2.15【推荐】其他场景下尽量不使用 DB_LINK

说明：

在查询语句中使用 DB_LINK 时，有可能会带来性能问题，比如下面一条 SQL 语句：

SELECT * FROM EVENT_USAGE_1120 A, SUBS@link_cc B

WHERE A.SUBS_ID = B.SUBS_ID

AND B.ACC_NBR='186123456789';

有可能会把 SUBS@link_cc 的全表数据全部加载到当前实例中，导致网络传输负载剧

增，很大程度上造成当前主机压力过大，影响系统性能。

一般来说比较合适的做法是，通过代码直连到@link_cc 所在的主机，先把 SUBS_ID

查询出来，然后再把 SUBS_ID 作为条件查询 EVENT_USAGE_***表。这样虽然一条 SQL

拆成了两条，但实际效果在很大程度上要大大优于单条 SQL。

\

2.16【强制】避免超长 SQL，SQL 长度不可超过 4000 长度。

说明：

超长 SQL 往往导致难以理解，并可能伴随性能隐患。同时，应尽量减少 SQL 语句复

杂度，用简单的 SQL 完成任务，复杂的业务逻辑尽量由业务代码来实现。

\

2.17【推荐】SQL 子查询嵌套不宜超过 3 层

说明：

禁止使用多层的 SQL 嵌套，除了分页查询，一般 SQL 语句建议不超过 3 层嵌套，

过于复杂的 SQL 可根据业务逻辑拆分为多条 SQL 来实现。

\

2.18【推荐】避免不必要的排序

说明：

避免不必要的排序，对查询结果进行排序会大大降低系统性能。应将大多数的排序

工作交给应用层去完成。

\

2.19

【推荐】查询分页场景，建议如下分页格式，先查询过滤出一部分数据，再做下一层过滤查询

举例：

Demo

Avoided

SELECT *

FROM (SELECT A.*, ROWNUM RN

FROM (SELECT OWNER, CREATED, OBJECT_ID, OBJECT_TYPE

FROM T1

WHERE OWNER = 'SYS'

ORDER BY OBJECT_ID DESC) A)

WHERE RN > 10

and RN <= 20

Preferred

SELECT *

FROM (SELECT A.*, ROWNUM RN

FROM (SELECT OWNER, CREATED, OBJECT_ID, OBJECT_TYPE

FROM T1

WHERE OWNER = 'SYS'

ORDER BY OBJECT_ID DESC) A

WHERE ROWNUM <= 20)

WHERE RN > 10