在Oracle中更快的选择SELECT COUNT（*）FROM sometable [英] Faster alternative in Oracle to SELECT COUNT(*) FROM sometable

问题描述

我注意到在Oracle中，查询

I've notice that in Oracle, the query

SELECT COUNT(*) FROM sometable;

对于大型表而言非常慢。它看起来像数据库它实际上通过每一行，并增加一个计数器一次。我认为在表中有一个计数器表中有多少行。

is very slow for large tables. It seems like the database it actually going through every row and incrementing a counter one at a time. I would think that there would be a counter somewhere in the table how many rows that table has.

所以如果我想检查表中的行数在Oracle ，这是最快的方法是什么？

So if I want to check the number of rows in a table in Oracle, what is the fastest way to do that?

推荐答案

想想：数据库真的要去每一行那。 在多用户环境中，我的 COUNT（*）可能与您的 COUNT（*） / strong>。对于每个会话都有不同的计数器是不切实际的，所以你实际上要计算行数。大多数时候，无论如何你会在你的查询中有一个WHERE子句或JOIN，所以你的假设计数器将具有实用的价值。

Think about it: the database really has to go to every row to do that. In a multi-user environment my COUNT(*) could be different from your COUNT(*). It would be impractical to have a different counter for each and every session so you have literally to count the rows. Most of the time anyway you would have a WHERE clause or a JOIN in your query so your hypothetical counter would be of litte practical value.

有办法加快事情但是：如果在NOT NULL列上有INDEX，Oracle将计算索引的行而不是表。在一个合适的关系模型中，所有表都有一个主键，所以 COUNT（*）将使用主键的索引。

There are ways to speed up things however: if you have an INDEX on a NOT NULL column Oracle will count the rows of the index instead of the table. In a proper relational model all tables have a primary key so the COUNT(*) will use the index of the primary key.

位图索引具有NULL行的条目，因此如果有一个可用，COUNT（*）将使用位图索引。

Bitmap index have entries for NULL rows so a COUNT(*) will use a bitmap index if there is one available.

这篇关于在Oracle中更快的选择SELECT COUNT（*）FROM sometable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！