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(*) 不同.为每个会话使用不同的计数器是不切实际的，因此您必须从字面上计算行数.无论如何，大多数情况下，您的查询中都会有 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屋！