同时选择MIN和MAX从表比预期的慢(Selecting both MIN and MAX From

我有一个表MYTABLE与日期列SDATE这是表的主键上有一个唯一索引。

当我运行此查询：

SELECT MIN(SDATE) FROM MYTABLE

它给了回答瞬间。 同样的情况发生了：

SELECT MAX(SDATE) FROM MYTABLE

但是，如果我查询都在一起：

SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE

它需要更多的时间来执行。 我分析了计划，并在最小或最大的一个查询发现，它使用索引全扫描（MIN / MAX），但是当两者都在同一时间查询，它全表扫描。

为什么？

测试数据：

版本11g

create table MYTABLE
(
  SDATE  DATE not null,
  CELL   VARCHAR2(10),
  data NUMBER
)
tablespace CHIPS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

alter table MYTABLE
  add constraint PK_SDATE primary key (SDATE)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

负载表：

declare 
  i integer;
begin
  for i in 0 .. 100000 loop
     insert into MYTABLE(sdate, cell, data)
     values(sysdate - i/24, 'T' || i, i);     
     commit;
  end loop;
end;

收集相关统计数据：

begin
  dbms_stats.gather_table_stats(tabname => 'MYTABLE', ownname => 'SYS');
end;

计划1：

计划2：

Answer 1:

该指数全扫描只能访问索引的一侧。 当你正在做

SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE

您请求访问两面。 因此，如果你想最小和最大列值，索引全扫描是不可行的。

更详细的分析，你可以找到这里 。

Answer 2:

在解释计划不同：一个MIN或MAX将产生一个INDEX FULL SCAN (MIN/MAX)而当两个都存在，你会得到一个INDEX FULL SCAN或FAST FULL INDEX SCAN 。

理解上的差异，我们要寻找的情况进行说明FULL INDEX SCAN ：

在全索引扫描，数据库按顺序读取整个索引。

换句话说，如果该索引是一个VARCHAR2领域，甲骨文将获取将包含例如以字母“A”开头的条目，并将读取的块中的所有条目按字母顺序块，直到最后一个条目索引的第一个块（ “A” 到 “Z”）。 因为条目在二进制树索引排序甲骨文可以以这种方式处理。

当你看到INDEX FULL SCAN (MIN/MAX)在解释计划，这是使用的事实，因为条目的排序，你可以在读完后，第一个如果你只由有关停止优化的结果MIN 。 如果你有兴趣在MAX只，Oracle可以使用相同的访问路径，但这次通过的最后一项开始，并从“Z”到“A”反向读取。

截至目前，一个FULL INDEX SCAN只有一个方向（向前或向后），不能启动从两端同时，这也就是为什么当你问的最小值和最大值都，你会得到一个低效率的访问方法。

正如其他的答案提出，如果查询需要关键的效率，你可以通过搜索分钟，在两个不同的查询的最大运行自己的优化。

Answer 3:

尝试在一个查询没有选择索引的两边，在这样的不同的方式访问查询：

select max_date, min_date
from (select max(sdate) max_date from mytable),
       (select min(sdate) min_date from mytable)

会导致优化访问索引INDEX_FULL_SCAN（MIN / MAX）在嵌套循环（在我们的情况下，两次）。

Answer 4:

我不得不说，我没有看到在11.2相同的行为

如果我成立了一个测试案例如下，并响应从10K至1M行更新，文森特的评论

set linesize 130
set pagesize 0
create table mytable ( sdate date );

Table created.

insert into mytable
 select sysdate - level
   from dual
connect by level <= 1000000;
commit;

1000000 rows created.


Commit complete.

alter table mytable add constraint pk_mytable primary key ( sdate ) using index;

Table altered.

begin
dbms_stats.gather_table_stats( user, 'MYTABLE' 
                             , estimate_percent => 100
                             , cascade => true
                               );
end;
/

PL/SQL procedure successfully completed.

然后，在执行您的疑问，我得到几乎相同的期待解释计划（注意不同类型的索引全扫描的）

explain plan for select min(sdate) from mytable;

Explained.

select * from table(dbms_xplan.display);
Plan hash value: 3877058912

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE        |        |     1 |     8 |        |      |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_MYTABLE |     1 |     8 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

9 rows selected.

explain plan for select min(sdate), max(sdate) from mytable;

Explained.

select * from table(dbms_xplan.display);
Plan hash value: 3812733167

-------------------------------------------------------------------------------
| Id  | Operation    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     8 |   252   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE  |        |     1 |     8 |        |          |
|   2 |   INDEX FULL SCAN| PK_MYTABLE |  1000K|  7812K|   252   (0)| 00:00:04 |
-------------------------------------------------------------------------------

9 rows selected.

从我的前面的回答引用：

为查询不使用索引的两种最常见的原因是：

1. 这是更快地做一个全表扫描。
2. 可怜的统计数据。

除非有什么东西你没有在这个问题张贴我直接的答案是，你还没有收集有关表的统计信息，你还没有足够高的估计％的收集他们或者您已使用analyze ，这将不利于基于成本的优化，不像dbms_stats.gather_table_stats 。

要在文档引述analyze ：

对于大多数统计数据的收集，使用DBMS_STATS包，它可以让你收集的统计数据并行，收集全球统计分区对象，并微调统计信息收集的其他方式。 查看Oracle数据库PL / SQL程序包和类型参考有关DBMS_STATS包的更多信息。

使用统计信息收集不相关的基于成本的优化Analyze语句（而不是DBMS_STATS）：

文章来源: Selecting both MIN and MAX From the Table is slower than expected