Reputation: 193311
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.
So if I want to check the number of rows in a table in Oracle, what is the fastest way to do that?
Upvotes: 76
Views: 231157
Reputation: 93
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
Upvotes: -1
Reputation: 1
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);
Upvotes: -1
Reputation: 767
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,
Upvotes: 75
Reputation: 67802
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.
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.
Bitmap index have entries for NULL rows so a COUNT(*) will use a bitmap index if there is one available.
Upvotes: 32
Reputation: 1315
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.
There are somethings you have not told us. Namely why do you think think this should be faster?
For example:
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.
Upvotes: 3
Reputation: 60312
If you want just a rough estimate, you can extrapolate from a sample:
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);
For even greater speed (but even worse accuracy) you can use block-wise sampling:
SELECT COUNT(*) * 100 FROM sometable SAMPLE BLOCK (1);
Upvotes: 69
Reputation: 7316
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.
Upvotes: 6
Reputation: 52396
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:
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.
Option 3: For a quick estimate on large tables invoke the SAMPLE clause ... for example ...
SELECT 1000*COUNT(*) FROM sometable SAMPLE(0.1);
Option 4: Use a materialized view to maintain the count(*). Powerful medicine though.
um ...
Upvotes: 8
Reputation: 146349
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.
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 ...
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 ....
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>
Upvotes: 16