使用sample优化SQL

开发同学想使用以下的SQL随机取出状态为0的一条CODE用于业务上发奖品ID，但又事先不知道这个code，只能从这个cord表中找状态为0的记录。
select *
  from bb.b_card
 where code in (select code
                  from (select *
                          from bb.b_card
                         where rownum = 10
                         and status = 0
                           and expire_date >
                               to_date('2015-08-20', 'yyyy-mm-dd hh24:mi:ss')
                         order by dbms_random.random)
                 where rownum = 1);
执行计划如下：
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 754878421
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    70 |  1619   (7)| 00:00:20 |
|   1 |  NESTED LOOPS                |              |     1 |    70 |  1619   (7)| 00:00:20 |
|   2 |   VIEW                       | VW_NSO_1     |     1 |    27 |  1618   (7)| 00:00:20 |
|*  3 |    COUNT STOPKEY             |              |       |       |            |          |
|   4 |     VIEW                     |              |   986K|    15M|  1618   (7)| 00:00:20 |
|*  5 |      SORT ORDER BY STOPKEY   |              |   986K|    40M|  1618   (7)| 00:00:20 |
|   6 |       COUNT                  |              |       |       |            |          |
|*  7 |        FILTER                |              |       |       |            |          |
|*  8 |         TABLE ACCESS FULL    | NOVICE_CARD  |   986K|    40M|  1618   (7)| 00:00:20 |
|   9 |   TABLE ACCESS BY INDEX ROWID| NOVICE_CARD  |   986K|    40M|     1   (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN         | SYS_C0062248 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(ROWNUM=1)
   5 - filter(ROWNUM=1)
   7 - filter(ROWNUM=10)
   8 - filter("STATUS"=0 AND "EXPIRE_DATE">TO_DATE('2015-08-20','yyyy-mm-dd
              hh24:mi:ss'))
  10 - access("CODE"="$nso_col_1")
可以看到由于无法使用索引，这条SQL使用的是TABLE ACCESS FULL，后期如果随着表数据量的上升，此SQL会越来越慢。
我的优化如下：
select code
  from bb.bb_card sample(1)
 where rownum < 2
   and status = 0 and expire_date >to_date('2015-08-20', 'yyyy-mm-dd hh24:mi:ss');
执行计划如下：
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1489415315
------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    38 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY       |             |       |       |            |          |
|*  2 |   TABLE ACCESS SAMPLE| NOVICE_CARD |     1 |    38 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(ROWNUM<2)
   2 - filter("STATUS"=0 AND "EXPIRE_DATE">TO_DATE('2015-08-20','yyyy-mm-dd
              hh24:mi:ss'))
这里使用到了Oracle 的sample随机取样，使用sample选项的意思是指定Oracle从表中随机选择记录样本，这样比从整个表中选择更高效。
sample(1)是指Oracle从表中读取特定百分比的记录（1%），并判断是否满足WHERE子句以返回结果。
优化完成后执行时间从4.17秒降低到0.015毫秒。

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

转载于:http://blog.itpub.net/26390465/viewspace-1777450/