问题同时取最大和最小导到效率变低
测试
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