当我想在oracle中搜索min和max结果时哪种方法更好？

我有一个名为student的表,我想获得最大和最小分数,所以我以第一种方式编写sql：

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 |
------------------------------------------------------------------------------------------

（编辑：青岛站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!