select max(score),min(score) from student;
第二种方式：
select max(score) from student; select min(score) from student;
我从互联网上搜索,他们说第二种方式更好,因为oracle不能同时扫描索引.但第二种方式无法确保相同的数据,因为它会进行两次搜索.如何解决？
解决方法
select
(select max(score) from student),(select min(score) from student)
from dual;
该解决方案使用两个快速索引扫描.它应该比选项1或2运行得更快并且也将保持一致.
为什么最简单的解决方案不起作用？
看起来甲骨文似乎应该有办法以最佳方式运行：
select max(score),min(score) from student;
之前我见过这个查询,见过人们讨论它,而且Oracle甚至还有特殊的访问路径来获取最大值和最小值：INDEX FULL SCAN(MIN / MAX).但它似乎无法同时做到最小值和最大值,我不知道为什么.
很难证明Oracle无法做某事.也许有人会在以后进来并证明我错了.我的回答是基于Richard Foote的this article,他可能是世界顶级Oracle索引专家.我在下面列出了一些简单的测试.示例模式看起来像Oracle在一次查询中两次自动使用INDEX FULL SCAN(MIN / MAX)的理想情况,但事实并非如此.我的结果是使用最新版本12.2生成的.
示例模式
--Create STUDENT table with 1.6 million rows,an index on score,and fresh statistics. --drop table student; create table student(name varchar2(100),score number not null); insert into student select lpad('A',20,'A'),level from dual connect by level <= 100000; insert into student select * from student; insert into student select * from student; insert into student select * from student; insert into student select * from student; begin dbms_stats.gather_table_stats(user,'STUDENT'); end; / create index student_idx on student(score);
选项1：最小和最大的最简单查询 – 不起作用
最简单的查询使用INDEX FAST FULL SCAN.这可能比全表扫描更好,但对于大型索引来说仍然很昂贵.
explain plan for select max(score),min(score) from student; select * from table(dbms_xplan.display); Plan hash value: 4052181173 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 972 (2)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | INDEX FAST FULL SCAN| STUDENT_IDX | 1600K| 7812K| 972 (2)| 00:00:01 | -------------------------------------------------------------------------------------
选项2 – 在一个查询中仅MIN或MAX
一次运行一次可以产生最佳计划,成本超低.它具有INDEX FULL SCAN(MIN / MAX)操作.这可能和它一样快,尽管它只返回了答案的一半.使用MIN而不是MAX返回相同的计划.
--MIN works the same way explain plan for select max(score) from student; select * from table(dbms_xplan.display); Plan hash value: 3501948619 ------------------------------------------------------------------------------------------ | 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)| STUDENT_IDX | 1 | 5 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------
选项3 – 将MIN和MAX与子查询相结合
将两者与子查询相结合需要更多代码,但结果将比选项1中的简单查询快得多.成本看起来略高于选项2成本的两倍,但是当您考虑额外的往返时间数据库,选项3将是最快的.
在一个查询中还有其他方法可以执行此操作,例如使用UNION ALL.
explain plan for
select
(select max(score) from student),(select min(score) from student)
from dual;
select * from table(dbms_xplan.display);
Plan hash value: 661746414
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| STUDENT_IDX | 1 | 5 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 5 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| STUDENT_IDX | 1 | 5 | 3 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
