当我想在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 |
------------------------------------------------------------------------------------------

（编辑：湖南网）

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