[ORALCE]SQL 优化案例之 同时取最大和最小

问题同时取最大和最小导到效率变低

测试

1.分别取最大和最小

drop table TX1 purge;
create table TX1 as select * from dba_objects;
update TX1 set object_id=rownum;
commit;
alter table TX1 add constraint pk_object_id primary key(object_id);
set autotrace on
set linesize 150

取最大

select max(object_id) from TX1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2646677097

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |    1 |    13 |    2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE        |          |    1 |    13 |           |      |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID |    1 |    13 |    2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
      5  recursive calls
      0  db block gets
     86  consistent gets
      1  physical reads
      0  redo size
    558  bytes sent via SQL*Net to client
    392  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

取最小

select min(object_id) from TX1;
Plan hash value: 2646677097

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |    1 |    13 |    2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE        |          |    1 |    13 |           |      |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID |    1 |    13 |    2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
      4  recursive calls
      0  db block gets
     86  consistent gets
      1  physical reads
      0  redo size
    556  bytes sent via SQL*Net to client
    392  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

可以看到分别取最小和最大的COST都是2

等价改写,同时取最大和最小

set linesize 150
set autotrace on
select max(object_id),min(object_id) from TX1;
SQL> select max(object_id),min(object_id) from TX1;

MAX(OBJECT_ID) MIN(OBJECT_ID)
-------------- --------------
     73384            1


Execution Plan
----------------------------------------------------------
Plan hash value: 1265209789

--------------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |       1 |      13 |      51   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |       1 |      13 |          |         |
|   2 |   INDEX FAST FULL SCAN| PK_OBJECT_ID | 89350 |    1134K|      51   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
      4  recursive calls
      0  db block gets
    244  consistent gets
    150  physical reads
      0  redo size
    645  bytes sent via SQL*Net to client
    407  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

可以看到此时COST增加,变成全表扫描,再次改写

select max,min
from (select max(object_id) max from TX1) a,
    (select min(object_id) min from TX1) b;
Execution Plan
----------------------------------------------------------
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 |      1 |     13 |      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 |      1 |     13 |      2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
      7  recursive calls
      0  db block gets
    172  consistent gets
      0  physical reads
      0  redo size
    623  bytes sent via SQL*Net to client
    461  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed