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