Oracle（三） - Oracle关于Max和Min函数的几种写法比较

实验环境：

SQL> conn /as sysdba

已连接。
SQL> grant select on dba_objects to scott;
授权成功。
SQL> conn scott/tiger;
已连接。
SQL> create table big_table as select * from dba_objects;

表已创建。

SQL> create index BT_IDX_CREATED on big_table(created);
索引已创建。

实验SQL

1）select min(created) from big_table;

      select max(created) from big_table;

2）select min(created),max(created) from big_table;

3）select min(created), max(created) from ( select min(created) created from big_table
union all select max(created) created from big_table)

4）SELECT /*+ INDEX (big_table BT_IDX_CREATED) */ min(created),max(created) from big_table;

总结：

select min(created) from big_table;
select max(created) from big_table;

两者性能是一样的，走全表索引，也就是索引中的所有记录都扫描一次，INDEX FULL SCAN (MIN/MAX)

select min(created),max(created) from big_table;和SELECT /*+ INDEX (big_table BT_IDX_CREATED) */ min(created),max(created) from big_table;

没有索引

select min(created), max(created) from ( select min(created) created from big_table
union all select max(created) created from big_table)

同样走 INDEX FULL SCAN (MIN/MAX)