本文通过实验环境展示了在Oracle中查询最小值和最大值的不同SQL写法，包括单列查询与多列查询，并分析了各种写法的性能，特别是索引对查询性能的影响。实验结果显示，部分查询方式会导致全表索引扫描。
实验环境：
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)
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
