从表中选择MIN和MAX都比预期的慢 [英] Selecting both MIN and MAX From the Table is slower than expected

问题描述

我有一个带有日期列SDATE的表MYTABLE，该列是该表的主键，并且具有唯一索引.

I have a table MYTABLE with a date column SDATE which is the primary key of the table and has a unique index on it.

当我运行此查询时:

SELECT MIN(SDATE) FROM MYTABLE

它立即给出答案.

SELECT MAX(SDATE) FROM MYTABLE

但是，如果我同时查询:

But, if I query both together:

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

需要更多的时间来执行.我分析了计划，发现查询最小值或最大值之一时，它使用INDEX FULL SCAN(MIN/MAX)，但同时查询两者时，它执行了FULL TABLE SCAN.

it takes much more time to execute. I analyzed the plans and found when one of min or max is queried, it uses INDEX FULL SCAN(MIN/MAX) but when both are queried at the same time, it does a FULL TABLE SCAN.

为什么?

测试数据:

版本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:

Plan2:

推荐答案

索引完全扫描只能访问索引的一侧.

The Index Full Scan can only visit one side of the index. When you are doing

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

您要访问2面.因此，如果您既要使用最小列值又要使用最大列值，则索引全扫描"不可行.

you are requesting to visit 2 sides. Therefore, if you want both the minimum and the maximum column value, an Index Full Scan is not viable.

要进行更详细的分析，可以在此处.

A more detailed analyze you can find here.

这篇关于从表中选择MIN和MAX都比预期的慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！