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