【SQL优化案例】使用 SQL*Plus 的 Autotrace 功能辅助 SQL 优化

曾经遇到这样一个案例，有朋友在论坛中提出帮助请求，问以下这样一条 SQL 是否可以
优化：

SELECT * FROM sys_user
WHERE user_code = 'zhangyong'
 OR user_code IN (SELECT grp_code FROM sys_grp WHERE sys_grp.user_code = 'zhangyong')
复制

首先可以通过 SQL*Plus 的 Autotrace 功能查看该 SQL 的执行计划：

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'SYS_USER'
3 1 INDEX (UNIQUE SCAN) OF 'PK_SYS_GRP' (UNIQUE)
Statistics
----------------------------------------------------------
14 recursive calls
4 db block gets
30590 consistent gets
0 physical reads
。。。。。。。
0 sorts (memory)
0 sorts (disk)
3 rows processed
复制

注意到该 SQL 的逻辑读高达 30590，优化该 SQL 在根本上需要降低逻辑读。而相关数据
表的记录情况如下：

SQL> select count(distinct user_code) from sys_grp;
COUNT(DISTINCTUSER_CODE)
------------------------
14580
SQL> select count(distinct grp_code) from sys_grp;
COUNT(DISTINCTGRP_CODE)
-----------------------
300
SQL> select count(distinct user_code) from sys_user;
COUNT(DISTINCTUSER_CODE)
------------------------
15190
复制

通过执行计划可以知道，该 SQL 通过全表扫᧿访问记录数为 15190 的 SYS_USER 表，通
过索引唯一键扫᧿访问 PK_SYS_GRP，两者过滤（FILTER）返回结果集，全表扫描及 FILTER操作导致了大量的逻辑读。

可以尝试通过 OR 展开、索引访问避免全表扫᧿和 FILTER 操作，改写后的 SQL 如下所
示：

SELECT * FROM sys_user WHERE user_code = 'zhangyong'
UNION ALL
SELECT * FROM sys_user WHERE user_code <> 'zhangyong'
 AND user_code IN (SELECT grp_code FROM sys_grp WHERE sys_grp.user_code = 'zhangyong')
复制

通过 UNION ALL 将 SQL 展开，从而避免了 FILTER 操作，表联合部分通过 NESTED
LOOPS 实现。改写后的 SQL 执行计划如下所示：

Statistics
----------------------------------------------------------
 0 recursive calls
 0 db block gets
 130 consistent gets
 0 physical reads
。。。。。。。
 1 sorts (memory)
 0 sorts (disk)
 3 rows processed
Execution Plan
----------------------------------------------------------
 0 SELECT STATEMENT Optimizer=RULE
 1 0 UNION-ALL
 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_USER'
 3 2 INDEX (UNIQUE SCAN) OF 'PK_SYS_USER' (UNIQUE)
 4 1 NESTED LOOPS
 5 4 VIEW OF 'VW_NSO_1'
 6 5 SORT (UNIQUE)
 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_GRP'
 8 7 INDEX (RANGE SCAN) OF 'FK_SYS_USER_CODE' (NON-UNIQUE)
 9 4 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_
10 9 INDEX (UNIQUE SCAN) OF 'PK_SYS_USER' (UNIQUE)
复制

通过统计信息输出可以注意到，SQL 的逻辑读从原来的 30590 降低到 130，性能得到了极
大提高。同时改写后的 SQL 引入了一个排序，排序来自于这一步：

6 5 SORT (UNIQUE)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'SYS_GRP'
8 7 INDEX (RANGE SCAN) OF 'FK_SYS_USER_CODE' (NON-UNIQUE)
复制

在 SYS_GRP 表中，user_code 是非唯一键值，在 in 值判断里，要做 sort unique 排序，去除重复值，这里的 union all 是不需要排序的。