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 。