ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 判断符合条件记录是否存在SQL若干

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

原创 Linux操作系统 作者:realkid4 时间:2012-02-13 22:56:01 0 删除 编辑

 

在实际开发中,我们会遇到各种访问数据表的需求。简单、高效一直是我们编写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语句,更告诉我们:在不同的情况,包括业务、技术和优化环境,采用不同的语句,效果是有很大的差异的。作为优化人员的我们,要从业务特点出发,分析出数据表的主要访问方式和关键服务用例,制定最合适的优化方案。

 

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

下一篇: 从Dump数据块看ITL
请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7569727