表的 Oracle 行计数(*)与来自 DBA_TABLES 的 NUM_ROWS [英] Oracle row count of table by count(*) vs NUM_ROWS from DBA_TABLES
问题描述
看起来 count(*) 比 NUM_ROWS 慢.这方面的专家能否对此有所了解.
Looks like count(*) is slower than NUM_ROWS. Can experts in this area throw some light on this.
推荐答案
根据 文档 NUM_ROWS 是表中的行数"，所以我可以看出这可能会令人困惑.但是，这两种方法之间存在重大差异.
According to the documentation NUM_ROWS is the "Number of rows in the table", so I can see how this might be confusing. There, however, is a major difference between these two methods.
此查询从系统视图中选择 MY_TABLE 中的行数.这是 Oracle 之前收集和存储的数据.
This query selects the number of rows in MY_TABLE from a system view. This is data that Oracle has previously collected and stored.
select num_rows from all_tables where table_name = 'MY_TABLE'
这个查询计算MY_TABLE中的当前行数
This query counts the current number of rows in MY_TABLE
select count(*) from my_table
根据定义，它们是不同的数据.关于 NUM_ROWS，您还需要另外两条信息.
By definition they are difference pieces of data. There are two additional pieces of information you need about NUM_ROWS.
在文档中，列名旁边有一个星号，导致此注释:
In the documentation there's an asterisk by the column name, which leads to this note:
标有星号 (*) 的列仅在您收集使用 ANALYZE 语句或 DBMS_STATS 对表进行统计包.
Columns marked with an asterisk (*) are populated only if you collect statistics on the table with the ANALYZE statement or the DBMS_STATS package.
这意味着除非您收集了表格的统计信息，否则此列将没有任何数据.
This means that unless you have gathered statistics on the table then this column will not have any data.
使用默认 estimate_percent 或 100% 估计值在 11g+ 中收集的统计数据将返回该时间点的准确数字.但是在 11g 之前收集的统计数据，或者自定义 estimate_percent 小于 100%，使用动态采样并且可能不正确.如果您收集了 99.999%，则可能会遗漏一行，这反过来意味着您得到的答案是错误的.
Statistics gathered in 11g+ with the default estimate_percent, or with a 100% estimate, will return an accurate number for that point in time. But statistics gathered before 11g, or with a custom estimate_percent less than 100%, uses dynamic sampling and may be incorrect. If you gather 99.999% a single row may be missed, which in turn means that the answer you get is incorrect.
如果您的表从不更新，那么当然可以使用 ALL_TABLES.NUM_ROWS 来找出表中的行数.然而，但是这是一个很大的问题，如果任何进程从您的表中插入或删除行，它充其量只是一个很好的近似值，并且取决于您的数据库是否自动收集统计信息，这可能是非常错误的.
If your table is never updated then it is certainly possible to use ALL_TABLES.NUM_ROWS to find out the number of rows in a table. However, and it's a big however, if any process inserts or deletes rows from your table it will be at best a good approximation and depending on whether your database gathers statistics automatically could be horribly wrong.
一般来说，实际计算表中的行数总是比依赖系统表更好.
Generally speaking, it is always better to actually count the number of rows in the table rather then relying on the system tables.
这篇关于表的 Oracle 行计数(*)与来自 DBA_TABLES 的 NUM_ROWS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
