Oracle stop key算法

妖狐艹你老母 2022-09-02 00:49 107阅读 0赞

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 。