当前位置：theitroad>Oracle 中更快的替代 SELECT COUNT(*) FROM sometable

Oracle 中更快的替代 SELECT COUNT(*) FROM sometable

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/1840538/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 02:17:45 　来源:igfitidea点击:

Faster alternative in Oracle to SELECT COUNT(*) FROM sometable

oraclecount

提问by Eli Courtwright

I've notice that in Oracle, the query

我注意到在 Oracle 中，查询

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.

对于大表来说非常慢。似乎数据库实际上遍历每一行并一次增加一个计数器。我认为表中某处会有一个计数器该表有多少行。

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

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

采纳答案by Vincent Malgrat

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.

想一想：数据库真的必须去每一行才能做到这一点。在多用户环境中 myCOUNT(*)可能与您的COUNT(*). 为每个会话使用不同的计数器是不切实际的，因此您必须从字面上计算行数。大多数情况下，您的查询中都会有 WHERE 子句或 JOIN，因此您假设的计数器几乎没有实际价值。

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.

但是，有一些方法可以加快速度：如果您在 NOT NULL 列上有一个 INDEX，则 Oracle 将计算索引的行数而不是表的行数。在适当的关系模型中，所有表都有一个主键，因此COUNT(*)将使用主键的索引。

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

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

回答by Jeffrey Kemp

If you want just a rough estimate, you can extrapolate from a sample:

如果您只想粗略估计，您可以从样本中推断：

SELECT COUNT(*) * 100 FROM sometable SAMPLE (1);

SELECT COUNT(*) * 100 FROM sometable SAMPLE (1);

For greater speed (but lower accuracy) you can reduce the sample size:

为了提高速度（但降低精度），您可以减少样本量：

SELECT COUNT(*) * 1000 FROM sometable SAMPLE (0.1);

SELECT COUNT(*) * 1000 FROM sometable SAMPLE (0.1);

For even greater speed (but even worse accuracy) you can use block-wise sampling:

为了更快的速度（但更差的准确性），您可以使用按块采样：

SELECT COUNT(*) * 100 FROM sometable SAMPLE BLOCK (1);

SELECT COUNT(*) * 100 FROM sometable SAMPLE BLOCK (1);

回答by AMISH G SHAH

This works great for large tables.

这对大表很有用。

SELECT NUM_ROWS FROM ALL_TABLES WHERE TABLE_NAME = 'TABLE_NAME_IN_UPPERCASE';

For small to medium size tables, following will be ok.

对于中小型表，以下就可以了。

SELECT COUNT(Primary_Key) FROM table_name;

Cheers,

干杯，

回答by APC

If the table has an index on a NOT NULL column the COUNT(*) will use that. Otherwise it is executes a full table scan. Note that the index doesn't have to be UNIQUE it just has to be NOT NULL.

如果表在 NOT NULL 列上有索引，则 COUNT(*) 将使用该索引。否则执行全表扫描。请注意，索引不必是唯一的，它只需不是 NULL。

Here is a table...

这是一张桌子...

SQL> desc big23
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 PK_COL                                    NOT NULL NUMBER
 COL_1                                              VARCHAR2(30)
 COL_2                                              VARCHAR2(30)
 COL_3                                              NUMBER
 COL_4                                              DATE
 COL_5                                              NUMBER
 NAME                                               VARCHAR2(10)

SQL>

First we'll do a count with no indexes ....

首先，我们将在没有索引的情况下进行计数......

SQL> explain plan for
  2      select count(*) from big23
  3  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /
select * from table)dbms_xplan.display)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 983596667

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  1618   (1)| 00:00:20 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG23 |   472K|  1618   (1)| 00:00:20 |
--------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
   - dynamic sampling used for this statement

13 rows selected.

SQL>

No we create an index on a column which can contain NULL entries ...

不，我们在可以包含 NULL 条目的列上创建索引......

SQL> create index i23 on big23(col_5)
  2  /

Index created.

SQL> delete from plan_table
  2  /

3 rows deleted.

SQL> explain plan for
  2      select count(*) from big23
  3  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
Plan hash value: 983596667

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  1618   (1)| 00:00:20 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| BIG23 |   472K|  1618   (1)| 00:00:20 |
--------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
   - dynamic sampling used for this statement

13 rows selected.

SQL>

Finally let's build the index on the NOT NULL column ....

最后让我们在 NOT NULL 列上建立索引......

SQL> drop index i23
  2  /

Index dropped.

SQL> create index i23 on big23(pk_col)
  2  /

Index created.

SQL> delete from plan_table
  2  /

3 rows deleted.

SQL> explain plan for
  2      select count(*) from big23
  3  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 1352920814

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |   326   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| I23  |   472K|   326   (1)| 00:00:04 |
----------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
   - dynamic sampling used for this statement

13 rows selected.

SQL>

回答by David Aldridge

Option 1: Have an index on a non-null column present that can be used for the scan. Or create a function-based index as:

选项 1：存在可用于扫描的非空列上的索引。或者创建一个基于函数的索引：

create index idx on t(0);

this can then be scanned to give the count.

然后可以扫描它以给出计数。

Option 2: If you have monitoring turned on then check the monitoring view USER_TAB_MODIFICATIONS and add/subtract the relevant values to the table statistics.

选项 2：如果您打开了监控，则检查监控视图 USER_TAB_MODIFICATIONS 并将相关值添加/减去表统计信息。

Option 3: For a quick estimate on large tables invoke the SAMPLE clause ... for example ...

选项 3：要对大表进行快速估计，请调用 SAMPLE 子句...例如...

SELECT 1000*COUNT(*) FROM sometable SAMPLE(0.1);

Option 4: Use a materialized view to maintain the count(*). Powerful medicine though.

选项 4：使用物化视图来维护计数（*）。虽药力强。

um ...

嗯...

回答by tuinstoel

You can create a fast refresh materialized view to store the count.

您可以创建一个快速刷新的物化视图来存储计数。

Example:

例子：

create table sometable (
id number(10) not null primary key
, name varchar2(100) not null);

create materialized view log on sometable with rowid including new values;

create materialized view sometable_count
refresh on commit
as
select count(*) count
from   sometable;

insert into sometable values (1,'Raymond');
insert into sometable values (2,'Hans');

commit;

select count from sometable_count;

It will slow mutations on table sometable a bit but the counting will become a lot faster.

它会稍微减慢桌子上的突变，但计数会变得更快。

回答by David

The fastest way to get a count of a table is exactly what you did. There are no tricks you can do that Oracle doesn't already know about.

获得表计数的最快方法正是您所做的。您可以使用 Oracle 不知道的任何技巧。

There are somethings you have not told us. Namely why do you think think this should be faster?

有些事情你没有告诉我们。即为什么你认为这应该更快？

For example:

例如：

  1. Have you at least done an explain plan to see what Oracle is doing?
  2. How many rows are there in this table?
  3. What version of Oracle are you using? 8,9,10,11 ... 7?
  4. Have you ever run database statistics on this table?
  5. Is this a frequently updated table or batch loaded or just static data?
  6. Is this the only slow COUNT(*) you have?
  7. How long does SELECT COUNT(*) FROM Dual take?
  1. 你至少做了一个解释计划来看看 Oracle 在做什么？
  2. 这个表有多少行？
  3. 您使用的是哪个版本的 Oracle？8、9、10、11……7？
  4. 你曾经在这张表上运行过数据库统计吗？
  5. 这是一个经常更新的表或批量加载还是只是静态数据？
  6. 这是您唯一的慢 COUNT(*) 吗？
  7. SELECT COUNT(*) FROM Dual 需要多长时间？

I'll admit I wouldn't be happy with 41 seconds but really WHY do you think it should be faster? If you tell us the table has 18 billion rows and is running on the laptop you bought from a garage sale in 2001, 41 seconds is probably not that far outside "good as it will get" unless you get better hardware. However if you say you are on Oracle 9 and you ran statistics last summer well you'll probably get a different suggestions.

我承认我不会对 41 秒感到满意，但你真的认为它为什么应该更快？如果您告诉我们该表有 180 亿行并且在您 2001 年从车库销售中购买的笔记本电脑上运行，那么 41 秒可能离“它会得到的好”并不遥远，除非您获得更好的硬件。但是，如果您说您使用的是 Oracle 9 并且您在去年夏天很好地运行了统计数据，那么您可能会得到不同的建议。

回答by m.r226

There was a relevant answer from Ask Tompublished in April 2016.

Ask Tom于 2016 年 4 月发布了相关答案。

If you have sufficient server power, you can do

select /*+ parallel */ count(*) from sometable

If you are just after an approximation, you can do :

select 5 * count(*) from sometable sample block (10);

Also, if there is

  1. a column that contains no nulls, but is not defined as NOT NULL, and
  2. there is an index on that column

you could try:

select /*+ index_ffs(t) */ count(*) from sometable  t where indexed_col is not null

如果你有足够的服务器能力，你可以做

select /*+ parallel */ count(*) from sometable

如果您只是在近似值之后，您可以执行以下操作：

select 5 * count(*) from sometable sample block (10);

另外，如果有

  1. 不包含空值但未定义为 NOT NULL 的列，并且
  2. 该列上有一个索引

你可以试试：

select /*+ index_ffs(t) */ count(*) from sometable  t where indexed_col is not null

回答by Harry L

This worked well for me

这对我很有效

select owner, table_name, nvl(num_rows,-1) 
from all_tables 
--where table_name in ('cats', 'dogs')
order by nvl(num_rows,-1) desc

from https://livesql.oracle.com/apex/livesql/file/content_EPJLBHYMPOPAGL9PQAV7XH14Q.html

来自https://livesql.oracle.com/apex/livesql/file/content_EPJLBHYMPOPAGL9PQAV7XH14Q.html

回答by Akil Romauld Yesudhas

You can have better performance by using the following method:

您可以使用以下方法获得更好的性能：

SELECT COUNT(1) FROM (SELECT /*+FIRST_ROWS*/ column_name 
FROM table_name 
WHERE column_name = 'xxxxx' AND ROWNUM = 1);

相关推荐

oracle ORA-00604 ORA-12705

oracle ORA-00604 ORA-12705

Oracle - 授予所有用户权限

Oracle - 授予所有用户权限

oracle 任何像样的weblogic教程？

oracle 任何像样的weblogic教程？

oracle ORA-1555: 快照太旧：回滚段号

oracle ORA-1555: 快照太旧：回滚段号

相关推荐
最近更新
标签