在实际开发中，我们会遇到各种访问数据表的需求。简单、高效一直是我们编写SQL语句的一个重要标准。如何用最少的系统开销，实现功能需求是我们需要关注的一个重要方面。
一个朋友问笔者：判断符合条件记录是否存在？这样的SQL语句如何书写最好。笔者感觉很有意思，这里将思考分析过程加以记录，供有类似需要的朋友备查。
1、环境准备
实验在Oracle 10gR2上进行。为了更凸显出效果，我们构建一张20万记录的数据表作为实验对象。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
- Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0–Production
--构建实验数据表
SQL> create table t as select * from dba_objects
where 1=0;
Table created
SQL> insert into t select * from dba_objects;
53346 rows inserted
（多次重复insert过程，篇幅原因省略……）
SQL> commit;
Commit complete
SQL> select count(*) from t;
COUNT(*)
----------
213384
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade
=> true);
PL/SQL procedure successfully completed
--注意：本篇中涉及的所有select操作，之前都进行buffer cache清理工作，用于保证条件相同；
SQL> alter system flush buffer_cache;
System altered
要求构建SQL为：判断owner列是否存在取值为’SCOTT’的记录，如果有则返回‘Y’，否则返回空。
下面几种处理思路和场景，分别进行介绍。
2、无索引添加情况下，各种备选SQL分析
索引index是我们经常使用到的一种优化手段。但是，索引对应用系统优化而言，绝对不是万灵药。使用索引是需要付出时间和空间上的成本的，而能否取得预计的优化效果是需要评估的。所以，笔者认为，只有在明确收益大于支出的情况下，我们才会主动使用索引。
首先，我们看一下不使用索引的情况下，几条备选SQL的性能。
ü Count计数
Count计数应该是容易想到的一种直观解决。如果能判断出符合条件的记录数量，是否存在不久显而易见了吗？
SQL> select count(*) from t where wner='SCOTT';
已用时间: 00: 00:01.28
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 655 (2)| 00:00:08
|
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| T | 145 | 1015 | 655 (2)|
00:00:08 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SCOTT')
统计信息
----------------------------------------------------------
168 recursive calls
0 db block gets
2960 consistent gets
2946 physical reads
0 redo size
408 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
注意上面语句的几个细节：
首先，由于无索引可用，所以在访问数据表上，使用的是全表扫描（FTS）。应用owner=’scott’条件在FTS过程。
其次，将条件筛选过的结果进行aggregate操作，聚合成计数值count。这个过程要消耗pga乃至temp表空间的排序空间和cpu成本。
最后，在成本消耗上，合计执行计划成本为655，主要体现在大规模IO读取和sort排序操作上。
Count计数法从效果上，完全可以满足需求要求。但是，给我们的感觉总有些“大炮打蚊子”之感。我只需要SQL告诉我们是否存在这样的记录，而不是告诉有多少条符合条件记录。
那么，我们从存在exists角度进行优化。
ü Exists语句优化
借用SQL中的exists语句，我们可以构造SQL如下。
SQL> select 'Y' from dual where exists(select *
from t where wner='SCOTT');
已用时间: 00: 00:01.76
执行计划
----------------------------------------------------------
Plan hash value: 1060005908
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (0)|00:00:01|
|* 1 | FILTER | | | | | |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T | 1 | 7 | 6 (0)| 00:00:01
|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T"
"T" WHERE
"OWNER"='SCOTT'))
3 - filter("OWNER"='SCOTT')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
685 consistent gets
688 physical reads
0 redo size
402 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
注意，使用该种方法后，由于没有索引，所以不能解决FTS的问题。但是，使用exists子句，可以有效减少发生物理逻辑读块的数量，减少recursive
call的次数。更进一步，将原有的sort aggravate操作转化为了filter操作，消除了sort
area的使用。
从总成本上看，执行计划中也从原有的655下降到8。应该说，应用exists在这种场景上，效果是比较好的。
ü Rownum语句优化
我们还可以从rownum的角度进行优化。对SQL语句来说，其实只需要访问到一条符合条件的记录，就可以返回结果了，不需要进行额外的任何操作。此时，我们可以借助rownum来进行动作控制。
SQL> select 'Y' from t where wner='SCOTT' and rownum<2;
已用时间: 00: 00: 00.42
执行计划
----------------------------------------------------------
Plan hash value: 508354683
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 6 (0)| 00:00:01
|
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 7 | 6 (0)| 00:00:01
|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
2 - filter("OWNER"='SCOTT')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
685 consistent gets
688 physical reads
0 redo size
402 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
使用rownum在SQL执行计划中，对应的动作是count stopkey。该动作的含义是对返回的结果行数进行计数，数到指定的记录数目就返回。这个和我们的希望动作相似。
从执行计划和各种统计量来看，该语句是相对较好的一种。总成本下降到6左右。
3、有索引添加情况下，各种备选SQL分析
有索引情况下，我们的SQL语句如何呢？如果我们可以在owner列上添加索引，并且执行计划中出现index，那么在owner条件选取的问题上，索引叶子节点本身就可以提供有序的结构序列。
SQL> create index idx_t_owner on t(owner);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade
=> true);
PL/SQL procedure successfully completed
ü Count语句
当有索引的情况下，Oracle可以根据排序好的叶子节点，直接定位到符合条件的记录条目。
SQL> select count(*) from t where wner='SCOTT';
已用时间: 00: 00: 00.17
执行计划
----------------------------------------------------------
Plan hash value: 1232703844
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | INDEX RANGE SCAN| IDX_T_OWNER | 1 | 7 | 3 (0)|
00:00:01
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
统计信息
----------------------------------------------------------
138 recursive calls
0 db block gets
20 consistent gets
12 physical reads
0 redo size
408 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
在有索引的情况下，count语句效率提升是显著的。主要体现在成本下降（3）和IO访问量减少上。
从执行计划上，可以理解这种变化主要在于原来的FTS操作变化为Index Range Scan。减少了数据访问块读取操作。
那么，剩下的两种方案效率如何呢？
ü Exists方案
SQL> select 'Y' from dual where exists(select *
from t where wner='SCOTT');
已用时间: 00: 00:00.05
执行计划
----------------------------------------------------------
Plan hash value: 1016071138
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (0)| 00:00:01
|* 1 | FILTER | | | | |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01
|* 3 | INDEX RANGE SCAN| IDX_T_OWNER | 1 | 7 | 3 (0)|
00:00:01
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T"
"T" WHERE
"OWNER"='SCOTT'))
3 - access("OWNER"='SCOTT')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
3 physical reads
0 redo size
402 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在有索引的情况下，exists方案的IO量比count方案减少。成本有所上升。
ü Rownum方案
SQL> select 'Y' from t where wner='SCOTT' and rownum<2;
已用时间: 00: 00: 00.08
执行计划
----------------------------------------------------------
Plan hash value: 1415695426
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01
|* 1 | COUNT STOPKEY | | | | |
|* 2 | INDEX RANGE SCAN| IDX_T_OWNER | 1 | 7 | 3 (0)|
00:00:01
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
2 - access("OWNER"='SCOTT')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
3 physical reads
0 redo size
402 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在使用rownum的情况下，IO量较count有所下降，但是成本cost估算相似。
4、结论
“鱼有千种，网有万条”，对Oracle优化方案的制定来说，同样如此。本篇不仅仅是介绍了一个实现判断记录存在的SQL语句，更告诉我们：在不同的情况，包括业务、技术和优化环境，采用不同的语句，效果是有很大的差异的。作为优化人员的我们，要从业务特点出发，分析出数据表的主要访问方式和关键服务用例，制定最合适的优化方案。
|
