与dbms_xplan.display混淆 [英] Confused with dbms_xplan.display

问题描述

我正在使用oracle 11g.刚开始练习索引.为此，我刚刚创建了一个具有10000000行的表.没有创建索引，我搜索了第4568754条记录，在解释计划中，它仅显示28行.

I'm using oracle 11g. Just started practicing index. For this i just created a table with 10000000 rows. With out creating index i searched for 4568754th record, in the explain plan it was displaying 28 rows only.

我的疑问:

如果我们不在表上创建任何索引，Oracle将进行顺序搜索.在上面的示例中，搜索的行应为4568743，但为什么只显示28?

Oracle will do sequential search if we wont' create any index on the table. In the above example searched rows should be 4568743 but why it was showing 28 only?

创建唯一索引后，它仅搜索1行.我可以看到％CPU的差异和执行所花费的时间，但行数仅为我扫清了困惑.

After creating unique index it has searched only 1 row. I can see the diff in %CPU and time taken to execute but number of rows scanned only the confusion for me.

任何人都可以解释一下.如果我的理解是错误的?

Can anyone please explain..If my understanding was wrong?

推荐答案

执行计划仅显示估计的行数，这是基于收集到的统计信息.优化器统计信息很复杂；没有确切的执行信息，就无法解释ROWS值.

Execution plans only show the estimated number of rows, which is based on gathered statistics. Optimizer statistics are complicated; without precise information about what was executed it's not possible to explain the ROWS value.

该表很可能具有过时的统计信息或缺少统计信息，并且FULL TABLE SCAN的估计值不准确.索引统计信息在创建时自动收集.创建索引并更改计划后，它可以使用准确的索引统计信息来预测仅返回一行.

Most likely the table has stale or missing statistics and the estimate for a FULL TABLE SCAN is inaccurate. Index statistics are automatically gathered on creation. When the index was created and the plan changed it was able to use the accurate index statistics to predict only one row is returned.

这篇关于与dbms_xplan.display混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！