oracle同时取最大值与最小值sql语句性能测试

我会带着你远行 2023-07-18 06:55 6阅读 0赞

同时取最大最小值
    --环境准备
    drop table t purge;
    create table t as select * from dba_objects;
    update t set object_id=rownum;
    commit;
    alter table t add constraint pk_object_id primary key (OBJECT_ID);
    set autotrace on
    set linesize 1000
    --看看执行计划是什么：
    set linesize 1000
    set autotrace on
    select max(object_id),min(object_id) from t;
    /*
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1265209789
    
    --------------------------------------------------------------------------------------
    | Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |              |     1 |    13 |    40   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |              |     1 |    13 |            |          |
    |   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 54956 |   697K|    40   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement
    
    
    统计信息
    ----------------------------------------------------------
            145  recursive calls
              0  db block gets
            224  consistent gets
            142  physical reads
              0  redo size
            494  bytes sent via SQL*Net to client
            416  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              4  sorts (memory)
              0  sorts (disk)
              1  rows processed
    */
    /*
    小结: 执行计划并没有走高效的index full scan (min/max)扫描方式，而是走index fast full scan模式。
    这是因为oracle不能同时在索引 相同的两段寻找最大值 和最小值 。
    以上语句修改成以下利用笛卡尔积的查询方式完成.
    */
    --同时取最大最小值的语句的改造写法
    select max, min
    from (select max(object_id) max from t ) a,
    (select min(object_id) min from t ) b;
    /*
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3319831621
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |              |     1 |    26 |     4   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS                |              |     1 |    26 |     4   (0)| 00:00:01 |
    |   2 |   VIEW                       |              |     1 |    13 |     2   (0)| 00:00:01 |
    |   3 |    SORT AGGREGATE            |              |     1 |    13 |            |          |
    |   4 |     INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 54956 |   697K|     2   (0)| 00:00:01 |
    |   5 |   VIEW                       |              |     1 |    13 |     2   (0)| 00:00:01 |
    |   6 |    SORT AGGREGATE            |              |     1 |    13 |            |          |
    |   7 |     INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 54956 |   697K|     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------
    
    Note
    -----
       - dynamic sampling used for this statement
    
    
    统计信息
    ----------------------------------------------------------
              7  recursive calls
              0  db block gets
            118  consistent gets
              0  physical reads
              0  redo size
            472  bytes sent via SQL*Net to client
            416  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    */
    --性能在 consistent gets上得到了提升.