Faster alternative in Oracle to SELECT COUNT(*) FROM sometable
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>
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.
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,
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);