Oracle如何使用统计数据 [英] How Oracle uses statistics data

问题描述

在上一个问题中，我得到了有关Oracle统计信息的评论:

In previous question I got comment about Oracle statistics:

Oracle不知道50M是否大于行数.当然，它具有统计信息，但是它们可能是过时的和错误的-Oracle绝不会仅因为统计信息是错误的而自己提供错误的结果

Oracle doesn't know that 50M is greater than the number of rows. Sure, it has statistics, but they could be old and wrong - and Oracle would never allow itself to deliver an incorrect result only because the statistics are wrong

我非常确定Oracle在准备查询执行计划时依赖统计信息.在版本10之前，建议不定期刷新统计信息，从10g开始，Oracle会自动收集统计信息.

I was pretty sure that Oracle relies on statistics when preparing query execution plan. Before version 10 it was recommended to refresh statistics from time to time and from 10g Oracle gathers statistics automatically.

有人可以解释多少Oracle查询分析器依赖统计数据吗?

Can somebody explain how much Oracle query analyzer relies on statistics data?

推荐答案

Oracle大量使用统计信息来生成查询执行计划.它不会(也不应该)做的是使用那些统计信息，这种方式会影响查询结果，而这正是您试图对"ROWNUM <50000000"执行的操作.统计信息可能已过期或丢失.但是，这仅意味着Oracle生成正确结果的速度可能会变慢，并不意味着Oracle将返回错误的结果.

Oracle uses statistics a lot, to generate query execution plans. What it does not (and should not) do is use those statistics in a way that will affect query results, which is what you were trying to do with "ROWNUM < 50000000". The statistics may be out of date, or missing. However, this will only mean that Oracle may be slow to generate the correct result, it does not mean that Oracle will return an incorrect result.

如果Oracle按您希望的那样工作，那么即使表现在包含60,000,000行(但过时的统计数据说它仅包含49,000,000)，它可能会认为"ROWNUM <50000000"的意思是获取所有行".幸运的是没有.

If Oracle worked as you hoped, then it might decide that "ROWNUM < 50000000" meant "get all rows" even though the table now contained 60,000,000 rows (but had out of date stats saying it contained only 49,000,000). Fortunately it does not.

这篇关于Oracle如何使用统计数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！