博客

  Oracle的stop key是一个很有用的算法，当获取满足条件的数据之后，sql就不往下执行。在实际工作中，我们会有这样的需求，只是判断查询条件是否有满足的数据，并不关心有多少条。下面来做一个例子：

drop table test;
create table test(id number ,name varchar2(100), age number);
insert into test select rownum, 'aaaaaaaaaaa'||rownum, 18  from dual connect by level <1000000;
commit;
create index ind_t_age on test(age);
exec dbms_stats.gather_table_stats(user,'test',cascade => true);
select count(age) from test where age=18;
select count(age) from test where age=18 and rownum=1;
SQL> select count(*) from test where age=18;
已用时间:  00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 1853573966
-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     3 |   537   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |     3 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IND_T_AGE |   999K|  2929K|   537   (2)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("AGE"=18)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1970  consistent gets
          0  physical reads
          0  redo size
        361  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> select count(*) from test where age=18 and rownum=1;
已用时间:  00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3902407824
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |     3 |            |          |
|*  2 |   COUNT STOPKEY    |           |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| IND_T_AGE |     1 |     3 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM=1)
   3 - access("AGE"=18)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        359  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

总结：看到stop key的效果了吧，consistent gets从1970降到了3 。