判断符合条件记录是否存在SQL若干

最新推荐文章于 2022-10-15 20:00:37 发布

ciqu9915

最新推荐文章于 2022-10-15 20:00:37 发布

阅读量305

点赞数

文章标签：数据库

在实际开发中，我们会遇到各种访问数据表的需求。简单、高效一直是我们编写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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

--------------------------------------------------------------

------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

----------------------------------------------------------------------------

| 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语句，更告诉我们：在不同的情况，包括业务、技术和优化环境，采用不同的语句，效果是有很大的差异的。作为优化人员的我们，要从业务特点出发，分析出数据表的主要访问方式和关键服务用例，制定最合适的优化方案。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-716191/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-716191/