Combine two requests from the second method into one request:
select (select max(score) from student), (select min(score) from student) from dual;
This solution uses two quick index scans. It should work faster than option 1 or 2, and will also be consistent.
Why doesn't the simplest solution work?
Of course, it looks like Oracle should have an optimal way:
select max(score),min(score) from student;
I saw this query before, I saw people discussing it, and Oracle even has special access paths to get max and min: INDEX FULL SCAN (MIN/MAX) . But he just can’t do both mines and maximum at the same time, and I’m not sure why.
It is hard to prove that Oracle cannot do something. Maybe someone will come later and prove that I'm wrong. My answer is based on this article by Richard Foote, who is arguably the world expert on Oracle index. And I included some simple tests below. The sample schema looks like an ideal case for Oracle, which automatically uses the INDEX FULL SCAN (MIN/MAX) twice in the same query, but it is not. My results were generated using the latest version, 12.2.
Circuit example
--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);
Option 1: Simple query with minimum and maximum - does not work
The simplest query uses INDEX FAST FULL SCAN . This is probably better than a full table scan, but it can be expensive for large indexes.
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 | -------------------------------------------------------------------------------------
Option 2 - Only MIN or MAX in one query
Running one at a time leads to an optimal plan with an ultra-low cost of 3. It has an INDEX FULL SCAN (MIN/MAX) operation. This is probably as fast as it gets, although it only returns half of the answer. Using MIN instead of MAX returns the same plan.
--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 | ------------------------------------------------------------------------------------------
Option 3 - Combining MIN and MAX with Subqueries
Combining the two with the subqueries requires a bit more code, but the result will be much faster than the simple query in option 1. The cost looks a little higher than two times the cost of option 2, but when you take into account the additional round of -trip to the database, option 3 will be the fastest.
There are other ways to do this in a single query, for example using 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 | ------------------------------------------------------------------------------------------