同时max(),min()取最大 最小值 在mysql和Oracle中的差别

mysql 可以同时取最大值和最小值:mysql> explain select max(uuid) from p300;+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+1 row in set (0.02 sec)mysql> explain select max(uuid),min(uuid) from p300;+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+1 row in set (0.00 sec)SQL> desc dba_objects; 名称   是否为空? 类型 ----------------------------------------- -------- ---------------------------- OWNER    VARCHAR2(30) OBJECT_NAME    VARCHAR2(128) SUBOBJECT_NAME     VARCHAR2(30) OBJECT_ID    NUMBER DATA_OBJECT_ID     NUMBER OBJECT_TYPE    VARCHAR2(19) CREATED    DATE LAST_DDL_TIME    DATE TIMESTAMP    VARCHAR2(19) STATUS     VARCHAR2(7) TEMPORARY    VARCHAR2(1) GENERATED    VARCHAR2(1) SECONDARY    VARCHAR2(1) NAMESPACE    NUMBER EDITION_NAME    VARCHAR2(30)SQL> create table t100 as select * from dba_objects;表已创建。SQL> insert into t100 select * from t100;已创建96143行。SQL> insert into t100 select * from t100;已创建192286行。SQL> insert into t100 select * from t100;已创建384572行。SQL> insert into t100 select * from t100;已创建769144行。SQL> insert into t100 select * from t100;已创建1538288行。SQL> commit;提交完成。BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SYS',                                tabname          => 'T100',                                estimate_percent => 100,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                cascade          => TRUE);END;/ SQL> create  index t100_idx1 on t100(object_id);索引已创建。SQL> explain plan for select max(object_id) from t100;已解释。SQL> select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1310084087----------------------------------------------------------------------------------------| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |       |     1 |     5 |     3 (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |       |     1 |     5 |    |       ||   2 |   INDEX FULL SCAN (MIN/MAX)| T100_IDX1 |     1 |     5 |     3 (0)| 00:00:01 |----------------------------------------------------------------------------------------已选择9行。SQL>  explain plan for select min(object_id) from t100;已解释。SQL>  select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1310084087----------------------------------------------------------------------------------------| Id  | Operation   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT   |       |     1 |     5 |     3 (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |       |     1 |     5 |    |       ||   2 |   INDEX FULL SCAN (MIN/MAX)| T100_IDX1 |     1 |     5 |     3 (0)| 00:00:01 |----------------------------------------------------------------------------------------已选择9行。SQL> explain plan for select max(object_id),min(object_id) from t100;已解释。SQL>  select * from table(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1916772590---------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |5 | 11737   (1)| 00:02:21 ||   1 |  SORT AGGREGATE    |  |1 |5 |       |  ||   2 |   TABLE ACCESS FULL| T100 |  3076K|    14M| 11737   (1)| 00:02:21 |---------------------------------------------------------------------------已选择9行。此时同时取最大值和最小值,在Oracle一次只能一个方向扫描索引,所以无法走索引