主页 / dba / 问题 / 138028
Accepted
Joishi Bodio
Asked: 2016-05-10 09:28:29 +0800 CST

不使用索引查询历史表（Oracle 12c）

  • 772

我有一个历史表，它有一个“ID”和“TIMESTAMP”列

CREATE TABLE hist (
  HIST_ID INTEGER,
  HIST_TIMESTAMP TIMESTAMP,
  ID INTEGER, -- this is the id of the table that is being tracked
  --OTHER COLS
);

我在这张表上也有一个索引

CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);

该表有很多针对它的插入，目前有大约 3000 万行。

当我尝试运行以下查询时，oracle 执行全表扫描而不是使用索引（索引……至少我相信……它应该能够使用）。

SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= <<A TIMESTAMP>> GROUP BY ID;

在我看来，Oracle 应该能够使用索引通过查看 id-by-id 上的 id/timestamp 索引来快速识别哪个 id/timestamp 对位于特定时间点的“左侧”基础，但它坚持全表扫描。

如果能更快地运行此查询，我们将不胜感激。

我运行了以下命令以确保统计数据是最新的

EXEC DBMS_STATS.GATHER_TABLE_STATS('<meh>','hist');

此外，历史表中大约有 1k 个不同的 ID 值。

关于数据分布……在大约 1k 个 ID 中，50 个在表中的条目少于 100 个，70 个在 100 到 1000 个条目之间，146 个在 1000 到 10000 个条目之间，其余的条目在 10k 到 60k 之间。超过一半的条目至少有 30k 条记录。

oracle oracle-12c

2 个回答

  1. Best Answer

    索引的使用显然是可能的，但是是可选的。

    CREATE TABLE hist (
  HIST_ID INTEGER,
  HIST_TIMESTAMP TIMESTAMP,
  ID INTEGER -- this is the id of the table that is being tracked
  --OTHER COLS
);

CREATE INDEX hist_ix ON hist (ID, HIST_TIMESTAMP);

explain plan for SELECT ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= :B1 GROUP BY ID;
select * from table(dbms_xplan.display);


Plan hash value: 1027924405

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|         |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN    | HIST_IX |     1 |    26 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))
       filter("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

    索引只能用于前导列是不正确的：

    explain plan for SELECT /*+ INDEX_SS(hist hist_ix) */ ID, MAX(HIST_TIMESTAMP) FROM hist WHERE HIST_TIMESTAMP <= :B1 GROUP BY ID;
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2669193891

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    26 |     1   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|         |     1 |    26 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN    | HIST_IX |     1 |    26 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))
       filter("HIST_TIMESTAMP"<=TO_TIMESTAMP(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

    另一种方法是索引快速全扫描（INDEX_FFS 提示）。

    如果您强制使用带有提示的索引，则比较带有全表扫描的计划和带有索引访问路径的计划的成本。这只是一个基于成本的决定，有一个像这样的简单例子。

    如果您甚至不能强制使用索引，我会在其他地方搜索问题。例如，您的索引处于UNUSABLE状态（检查USER_INDEXES.STATUS）或已创建INVISIBLE（USER_INDEXES.VISIBILITY）。

    • 1

  2. 您的索引是 hist_id 和 hist_timestamp 的复合索引。由于 hist_id 是该索引的前导部分，因此当您的 WHERE 谓词仅位于尾随的 hist_timestamp 部分时，无法使用该索引。

    • 0

相关问题