为什么Oracle会忽略“完美”的指数？ [英] Why would Oracle ignore a "perfect" index?

问题描述

我有这张表：

create table demo (
    key number(10) not null,
    type varchar2(3) not null,
    state varchar2(16) not null,
    ... lots more columns ...
)

和此索引：

create index demo_x04 on demo(key, type, state);

当我运行此查询时

select * from demo where key = 1 and type = '003' and state = 'NEW'

EXPLAIN PLAN 显示它执行全表扫描。所以我放弃了索引并再次创建它。 EXPLAIN PLAN 仍然表示全表扫描。怎么会这样？

EXPLAIN PLAN shows that it does a full table scan. So I dropped the index and created it again. EXPLAIN PLAN still says full table scan. How can that be?

一些背景：这是历史数据，所以我会查找状态为 CLEARED 并插入一个状态为 NEW 的新行（另外我从旧行复制一些值）。然后将旧行更新为 USED 。因此桌子总是在增长。我注意到的是索引的基数为0（尽管事实上我有数千个不同的值）。在重新创建之后，基数增长但是CBO不喜欢索引更好。

Some background: This is historical data, so what happens is that I look up a row with state CLEARED and insert a new row with state NEW (plus I copy a few values from the old row). The old row is then updated to USED. So the table always grows. What I did notice is that the cardinality of the index is 0 (despite the fact that I have thousands of different values). After recreating, the cardinality grew but the CBO didn't like the index any better.

第二天早上，Oracle突然喜欢索引（可能已经睡过了它）并开始了使用但不是很久。过了一会儿，处理从50行/秒下降到3行/秒，我再次看到FULL TABLE SCAN。发生了什么？

The next morning, Oracle suddenly liked the index (probably slept over it) and started to use it but not for long. After a while, the processing dropped from 50 rows/s to 3 rows/s and I saw again "FULL TABLE SCAN". What is going on?

在我的情况下，我需要处理大约一百万行。我批量提交变更。 50.在提交更新/重组索引之后我应该运行一些命令吗？

In my case, I need to process about a million rows. I commit the changes in batches of ca. 50. Is there some command which I should run after a commit to update/reorg the index or something like that?

我使用的是Oracle 10g。

I'm on Oracle 10g.

我在这个表中有969'491个不同的键，3种类型和3种状态。

I have 969'491 distinct keys in this table, 3 types and 3 states.

推荐答案

如果指定索引提示会发生什么？试试这个：

What happens if you specify an index hint? Try this:

SELECT /*+ INDEX (demo demo_x04) */ * 
  FROM demo 
 WHERE key = 1 
   AND type = '003' 
   AND state = 'NEW';

这听起来像是隔夜发生的事情，表格得到了分析。然后，当您对表运行处理时，更新了足够的索引以使oracle表的统计信息再次失效并且优化器停止使用索引。

It sounds like what happened overnight was that the table got analyzed. Then, as you ran your processing against the table, enough of the index was updated to cause oracle's table's statistics to go stale again and the optimizer stopped using the index.

添加提示并查看EXPLAIN PLAN是否为您提供了不同的计划，并且查询效果更好。

Add the hint and see if EXPLAIN PLAN gives you a different plan and the query performs better.

哦，Tony关于分析表格的答案是一般的良好做法，尽管10g数据库在这方面做自我维护非常好。如果您的进程正在进行大量更新，则索引可能会很快失效。如果在流程开始进入沟渠时运行分析会改善情况一段时间，那么您就会知道这是问题所在。

Oh, and Tony's answer regarding analyzing the table is a general good practice, although with 10g the database is pretty good about doing self-maintenance in that regard. If your process is doing a lot of updates the index can go stale quickly. If running analyze when your process starts going in the ditch improves the situation for a while, you would then know this is the problem.

要更新表的统计信息，请使用 dmbs_stats.gather_table_stats 包。

To update statistics for the table, use the dmbs_stats.gather_table_stats package.

例如：

exec dbms_stats.gather_table_stats（'the owner'，'DEMO'）;

exec dbms_stats.gather_table_stats('the owner','DEMO');

这篇关于为什么Oracle会忽略“完美”的指数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！