database - 为什么我要使用聚合函数对覆盖查询进行索引扫描？

Question

我有一个问题:

select min(timestamp) from table

这个表有 60 多万行，我每天都会删除最后几行。为了确定是否有足够旧的数据删除我运行上面的查询。有一个时间戳升序的索引，只包含一列，而oracle中的查询计划导致这是一个全索引扫描。这不应该是搜索的定义吗？

编辑包括计划:

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

Answer 1

你能发布实际的查询计划吗？您确定它没有进行最小/最大索引全扫描吗？正如您在此示例中所见，我们使用最小/最大索引全扫描从 100,000 行表中获取 MIN 值，仅进行少量一致获取。

SQL> create table foo (
  2    col1 date not null
  3  );

Table created.

SQL> insert into foo
  2    select sysdate + level
  3      from dual
  4   connect by level <= 100000;

100000 rows created.

SQL> create index idx_foo_col1
  2      on foo( col1 );

Index created.

SQL> analyze table foo compute statistics for all indexed columns;

Table analyzed.

SQL> set autotrace on;

<<Note that I ran this statement once just to get the delayed block cleanout to 
  happen so that the consistent gets number wouldn't be skewed.  You could run a
  different query as well>>

  1* select min(col1) from foo
SQL> /

MIN(COL1)
---------
02-FEB-11


Execution Plan
----------------------------------------------------------
Plan hash value: 817909383

--------------------------------------------------------------------------------

-----------

| Id  | Operation                  | Name         | Rows  | Bytes | Cost (%CPU)|

 Time     |

--------------------------------------------------------------------------------

-----------

|   0 | SELECT STATEMENT           |              |     1 |     7 |     2   (0)|

 00:00:01 |

|   1 |  SORT AGGREGATE            |              |     1 |     7 |            |

          |

|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_FOO_COL1 |     1 |     7 |     2   (0)|

 00:00:01 |

--------------------------------------------------------------------------------

-----------


Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        532  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed