oracle中搜索min和max结果时哪种方法更好?

摘要:我有一个名为student的表,我想获得最大和最小分数,所以我以第一种方式编写sql:selectmax(score),min(score)fromstudent第二种方式:selectmax(score)fromstudentselectmin(score)fromstudent我从互联网上搜索,他们说第二种方式更好

摘要:我有一个名为student的表,我想获得最大和最小分数,所以我以第一种方式编写sql:selectmax(score),min(score)fromstudent第二种方式:selectmax(score)fromstudentselectmin(score)fromstudent我从互联网上搜索,他们说第二种方式更好。

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


总结

以上是为你收集整理的当我想在oracle中搜索min和max结果时哪种方法更好?全部内容,希望文章能够帮你解决当我想在oracle中搜索min和max结果时哪种方法更好?所遇到的程序开发问题。

0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论