同时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一次只能一个方向扫描索引,所以无法走索引