从表中选择MIN和MAX都比预期的慢

本文介绍了从表中选择MIN和MAX都比预期的慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个带有日期列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.