一个改善含有ROWNUM条件语句性能的案例

数据库版本: 10.2.0.3；

案例背景：我们有一个Java程序会从一个配置文件中读取并执行以下一条SQL语句，由查询结果确认是否存在满足条件的数据记录，然后据此再执行后续代码。
最初的语句为：

SQL代码
  1. Select count(1) as rowcount   
  2. from B2B_BIZ_KEY B  
  3. WHERE EXISTS  
  4. (SELECT ID FROM B2B_TRANSACTION T  
  5.   WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')));  

程序的中判断条件为rowcount是否大于0.

表B2B_BIZ_KEY是表B2B_TRANSACTION子表，它有一个外键（已建索引）在字段(TRANSACTION_ID)上，指向B2B_TRANSACTION的主键 (ID)。尽管在B2B_TRANSACTION上还存在一个索引(MSG_REQ_ID, ID)，但由于这两张表中的数据量很大，因此语句的性能很差。以下是其执行后的性能统计数据。

SQL代码
  1. Elapsed: 00:02:12.48  
  2.   
  3. Execution Plan  
  4. ----------------------------------------------------------  
  5.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=165253 Card=1 Bytes=71)  
  6.    1    0   SORT (AGGREGATE)  
  7.    2    1     HASH JOIN (RIGHT SEMI) (Cost=165253 Card=35644057 Bytes=2530728047)  
  8.    3    2       INDEX (FAST FULL SCAN) OF 'B2B_TRANSACTION_UN' (INDEX(UNIQUE)) (Cost=15035 Card=8184629 Bytes=384677563)  
  9.    4    2       INDEX (FAST FULL SCAN) OF 'B2B_BIZ_KEY_UN' (INDEX (UNIQUE)) (Cost=65567 Card=36056379 Bytes=865353096)  
  10.   
  11. Statistics  
  12. ----------------------------------------------------------  
  13.         943  recursive calls  
  14.           0  db block gets  
  15.      363128  consistent gets  
  16.      416008  physical reads  
  17.           0  redo size  
  18.         353  bytes sent via SQL*Net to client  
  19.         435  bytes received via SQL*Net from client  
  20.           2  SQL*Net roundtrips to/from client  
  21.           0  sorts (memory)  
  22.           0  sorts (disk)  
  23.           1  rows processed  

通过逻辑分析，我们知道实际上根本需要获取满足条件的所有记录数。因此，我们可以给语句加上“ROWNUM<=1”条件。
修改后的语句是这样的：

SQL代码
  1. Select count(1) as rowcount   
  2. from B2B_BIZ_KEY B  
  3. WHERE EXISTS  
  4. (SELECT ID FROM B2B_TRANSACTION T  
  5.   WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')))  
  6. AND ROWNUM <= 1;  

但是我们发现，尽管语句的性能确实得到很大地提高，但并未达到我们期望的效果。运行结果如下：

SQL代码
  1. Elapsed: 00:00:19.76  
  2.   
  3. Execution Plan  
  4. ----------------------------------------------------------  
  5.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=165253 Card=1 Bytes=71)  
  6.    1    0   SORT (AGGREGATE)  
  7.    2    1     COUNT (STOPKEY)  
  8.    3    2       HASH JOIN (RIGHT SEMI) (Cost=165253 Card=35644057 Bytes=2530728047)  
  9.    4    3         INDEX (FAST FULL SCAN) OF 'B2B_TRANSACTION_UN' (INDEX (UNIQUE)) (Cost=15035 Card=8184629 Bytes=384677563)  
  10.    5    3         INDEX (FAST FULL SCAN) OF 'B2B_BIZ_KEY_UN' (INDEX (UNIQUE)) (Cost=65567 Card=36056379 Bytes=865353096)  
  11.   
  12. Statistics  
  13. ----------------------------------------------------------  
  14.         249  recursive calls  
  15.           0  db block gets  
  16.       66098  consistent gets  
  17.       13694  physical reads  
  18.           0  redo size  
  19.         350  bytes sent via SQL*Net to client  
  20.         435  bytes received via SQL*Net from client  
  21.           2  SQL*Net roundtrips to/from client  
  22.           0  sorts (memory)  
  23.           0  sorts (disk)  
  24.           1  rows processed  

我们假设"ROWNUM <= 1"的目的是为了使语句避免扫描没必要的记录，同时让优化器能基于这样的目的对执行计划进行优化————这种优化模式也就是我们所知的取前K条记录（First K rows）的优化模式。然而，由于COUNT(1)的存在，优化器知道服务端与客户端之间只会交互一次，因此认为取前K条记录的优化模式对于这条语句是没有意义的。如果将COUNT移除，运行结果如下：

SQL代码
  1. HELLODBA.COM> Select 1 as rowcount  
  2.   2  from B2B_BIZ_KEY B  
  3.   3  WHERE EXISTS  
  4.   4  (SELECT ID FROM B2B_TRANSACTION T  
  5.   5    WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')))  
  6.   6    AND ROWNUM<=1;  
  7.   
  8. Elapsed: 00:00:00.43  
  9.   
  10. Execution Plan  
  11. ----------------------------------------------------------  
  12.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=71)  
  13.    1    0   COUNT (STOPKEY)  
  14.    2    1     NESTED LOOPS (SEMI) (Cost=8 Card=3 Bytes=213)  
  15.    3    2       TABLE ACCESS (FULL) OF 'B2B_BIZ_KEY' (TABLE) (Cost=2 Card=22996310 Bytes=551911440)  
  16.    4    2       INDEX (RANGE SCAN) OF 'B2B_TRANSACTION_IX2' (INDEX) (Cost=2 Card=5456419 Bytes=256451693)  
  17.   
  18. Statistics  
  19. ----------------------------------------------------------  
  20.           1  recursive calls  
  21.           0  db block gets  
  22.           7  consistent gets  
  23.           0  physical reads  
  24.           0  redo size  
  25.         350  bytes sent via SQL*Net to client  
  26.         435  bytes received via SQL*Net from client  
  27.           2  SQL*Net roundtrips to/from client  
  28.           0  sorts (memory)  
  29.           0  sorts (disk)  
  30.           1  rows processed  

这个结果正式我们期望的。不过，如果仅仅将语句修改成这样，那么会导致程序缺陷。因为在我们的Java代码中并没有处理NO_DATA_FOUND的异常。我们也不想再修改Java代码。那还有其他办法吗？我曾经考虑过，如果能将上述结果写入一张临时表，然后再对临时表计数，应该可以解决上述问题。因此，我尝试将语句写出以下形式：

SQL代码
  1. HELLODBA.COM> with V as  
  2.   2  (Select /*+ MATERIALIZE qb_name(wv)*/1  
  3.   3  from B2B_BIZ_KEY B  
  4.   4  WHERE EXISTS  
  5.   5  (SELECT ID FROM B2B_TRANSACTION T  
  6.   6    WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')))  
  7.   7    AND ROWNUM<=1)  
  8.   8  select count(1) from v;  
  9.   
  10. Elapsed: 00:00:15.84  
  11.   
  12. Execution Plan  
  13. ----------------------------------------------------------  
  14.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=165255 Card=1)  
  15.    1    0   TEMP TABLE TRANSFORMATION  
  16.    2    1     LOAD AS SELECT  
  17.    3    2       COUNT (STOPKEY)  
  18.    4    3         HASH JOIN (RIGHT SEMI) (Cost=165253 Card=35644057 Bytes=2530728047)  
  19.    5    4           INDEX (FAST FULL SCAN) OF 'B2B_TRANSACTION_UN' (INDEX (UNIQUE)) (Cost=15035 Card=8184629 Bytes=384677563)  
  20.    6    4           INDEX (FAST FULL SCAN) OF 'B2B_BIZ_KEY_UN' (INDEX(UNIQUE)) (Cost=65567 Card=36056379 Bytes=865353096)  
  21.    7    1     SORT (AGGREGATE)  
  22.    8    7       VIEW (Cost=2 Card=1)  
  23.    9    8         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9FC8C0_51579C6B'(TABLE (TEMP)) (Cost=2 Card=1 Bytes=13)  
  24.   
  25. Statistics  
  26. ----------------------------------------------------------  
  27.         520  recursive calls  
  28.          10  db block gets  
  29.       66112  consistent gets  
  30.       11595  physical reads  
  31.        1460  redo size  
  32.         350  bytes sent via SQL*Net to client  
  33.         435  bytes received via SQL*Net from client  
  34.           2  SQL*Net roundtrips to/from client  
  35.           0  sorts (memory)  
  36.           0  sorts (disk)  
  37.           1  rows processed  

单从执行计划看，似乎与我的想法比较吻合：SQL引擎先进子查询结果写入一直系统临时表，然后再扫描临时表计数。但是，这个两个过程并不是分开的，而属于同一执行计划————COUNT还是导致优化器没有采纳取前K条记录的优化模式。好吧，那就使用SQL技巧将COUNT消除掉吧：

SQL代码
  1. HELLODBA.COM> with V as  
  2.   2  (Select 1  
  3.   3  from B2B_BIZ_KEY B  
  4.   4  WHERE EXISTS  
  5.   5  (SELECT ID FROM B2B_TRANSACTION T  
  6.   6    WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')))  
  7.   7    AND ROWNUM<=1)  
  8.   8  select nvl2((select 1 from v),1,0) as aaa from dual;  
  9.   
  10. Elapsed: 00:00:00.21  
  11.   
  12. Execution Plan  
  13. ----------------------------------------------------------  
  14.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)  
  15.    1    0   VIEW (Cost=8 Card=1)  
  16.    2    1     COUNT (STOPKEY)  
  17.    3    2       NESTED LOOPS (SEMI) (Cost=8 Card=3 Bytes=213)  
  18.    4    3         TABLE ACCESS (FULL) OF 'B2B_BIZ_KEY' (TABLE) (Cost=2Card=22996310 Bytes=551911440)  
  19.    5    3         INDEX (RANGE SCAN) OF 'B2B_TRANSACTION_IX2' (INDEX)(Cost=2 Card=5456419 Bytes=256451693)  
  20.    6    0   FAST DUAL (Cost=2 Card=1)  
  21.   
  22. Statistics  
  23. ----------------------------------------------------------  
  24.           0  recursive calls  
  25.           0  db block gets  
  26.           7  consistent gets  
  27.           0  physical reads  
  28.           0  redo size  
  29.         345  bytes sent via SQL*Net to client  
  30.         435  bytes received via SQL*Net from client  
  31.           2  SQL*Net roundtrips to/from client  
  32.           0  sorts (memory)  
  33.           0  sorts (disk)  
  34.           1  rows processed  

Ok. 性能满足要求，也不存在 NO_DATA_FOUND 异常的问题了。

--- Fuyuncat ---

转自 http://blog.csdn.net/haoxiaoyan/article/details/14553047

posted @ 2014-03-26 01:46  princessd8251  阅读(243)  评论(0)  编辑  收藏  举报