Over the years, there have been many debates regarding what is the optimal wayto count a selection of records from a table. Without going into the largehistory of this topic, different versions of Oracle mandated different approachesto best counting records.
However, in the more recent releases of Oracle, the fast full index scan (bitmapespecially) has generally become the most prudent way to count the records. Thankfully, under the cost optimiser, Oracle now performs this for most of combinations of "count(*)"that DBA's have advocated in the past, thus any of them will perform equivalently as canbe seen from the examples below
Example with indexed table
SQL> select count(*) from PURCHASED_VEHICLES;
COUNT(*)
----------
283761
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=247 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PUVE_VEFE_MODEL_I' (NON-UNIQU
E) (Cost=247 Card=283761)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2583 consistent gets
4 physical reads
0 redo size
369 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(1) from PURCHASED_VEHICLES;
COUNT(1)
----------
283761
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=247 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PUVE_VEFE_MODEL_I' (NON-UNIQU
E) (Cost=247 Card=283761)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2583 consistent gets
4 physical reads
0 redo size
369 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(rowid) from PURCHASED_VEHICLES;
COUNT(ROWID)
------------
283761
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=247 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PUVE_VEFE_MODEL_I' (NON-UNIQU
E) (Cost=247 Card=283761 Bytes=1986327)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2583 consistent gets
3 physical reads
0 redo size
373 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(id) from PURCHASED_VEHICLES; -- indexed col
COUNT(ID)
---------
283761
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=247 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'PUVE_VEFE_MODEL_I' (NON-UNIQU
E) (Cost=247 Card=283761 Bytes=1986327)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2583 consistent gets
3 physical reads
0 redo size
373 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Example with unindexed table
SQL> select count(*) from PURCHASED_VEHICLES;
COUNT(*)
----------
283761
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11952 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'PURCHASED_VEHICLES_JN' (Cost=119
52 Card=459871)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
126734 consistent gets
126569 physical reads
0 redo size
369 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(1) from PURCHASED_VEHICLES_JN;
COUNT(1)
----------
478957
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11952 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'PURCHASED_VEHICLES_JN' (Cost=119
52 Card=459871)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
126734 consistent gets
126569 physical reads
0 redo size
369 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(rowid) from PURCHASED_VEHICLES_JN;
COUNT(ROWID)
--------------
478957
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11952 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'PURCHASED_VEHICLES_JN' (Cost=119
52 Card=459871)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
126734 consistent gets
126569 physical reads
0 redo size
369 bytes sent via SQL*Net to client
424 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
参见:http://oracledba.co.uk/tips/count_speed.htm
=========================================================================================================
Hi,
Please clarify the difference b/w
count(0), count(1) and count(*)...???.
SQL> set timing on
SQL> select
count(0) from trade
2 /
COUNT(0)
----------
112158506
Elapsed: 00:00:03.08
SQL> ed
Wrote file afiedt.buf
1* select
count(1) from trade
SQL> /
COUNT(1)
----------
112158506
Elapsed: 00:00:02.01
SQL> ed
Wrote file afiedt.buf
1* select
count(*) from trade
SQL> /
COUNT(*)
----------
112158506
Elapsed: 00:00:02.03
SQL>
Is there any differences??
Thanks
SATHYA
count(*) is counting up all of hitting. count(expression) is counting up not null of expression. 0 is not null, 1 is not null. So, we can get same results. If you had not special reasion, you had better use count(*). Additional examples
SQL> select count(*) from scott.emp;
COUNT(*)
----------
13
SQL> select count(mgr) from scott.emp;
COUNT(MGR)
----------
12
SQL> select count(null) from scott.emp;
COUNT(NULL)
-----------
0
SQL> select count(0) from scott.emp;
COUNT(0)
----------
13
SQL> select count(distinct mgr) from scott.emp;
COUNT(DISTINCTMGR)
------------------
5
SQL> select count(distinct 0) from scott.emp;
COUNT(DISTINCT0)
----------------
1
SQL>
SQL> select count(distinct *) from scott.emp;
select count(distinct *) from scott.emp
*
ERROR at line 1:
ORA-00936: missing expression
As i've tried to check it in this way but receive error --
satyaki>
satyaki>alter system flush shared_pool
2 /
alter system flush shared_pool
*
ERROR at line 1:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.07
satyaki>
Because, i don't have sufficient privs to check that. So, i've to rely on all of you.
Count(*) will try to access all DB blocks of the
retrieved data before returning the Count.
Count(<Literal>) will not do so. Hence it is
faster
than count(*).
Any chance of getting additional information that'll
back your statement?
Otherwise it does sound like complete und utter
bollocks to me.
C.
Exactly my thoughts cd.
How can counting using a literal actually count all the rows without actually accessing all the rows in all the data blocks?
This has been answered so many times before that I can't actually be bothered to search out the links on this forum and AskTom that
prove there is no difference.
A good discussion which has many myths attached to it.
Well, count(*), count(1), count(0), count('FAST') are all the same performance wise. You can enable the autotrace and get the proof on screen.
But the performance will differ under some situations. Like, I have a TEST table, when I use COUNT(*) you can notice the consistent gets, recursive calls are low when compared to COUNT(B).
This is because when I issue COUNT(*), the CBO intelligently use the index on Primary key to count the rows whereas when I issue COUNT(B) the CBO has to count the rows using a Full table scan. ("B is a nullable column"). This brings the difference.
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B NUMBER
SQL> alter table test add constraint test_pk primary key (a);
Table altered.
Elapsed: 00:00:00.25
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.01
SQL> set autotrace on
SQL> select count(*) from test;
COUNT(*)
----------
3
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
On CBO, count(*) has performance's advantage from count(any_column),
but there is no difference among count(*), count(constant) and count(rowid).
I have forgotten how act on RBO. But I think also there is no difference between count(*) and count(constant). By the way, sometime count(primary_key) or count(indexed_column) has disadvantage from count(*).
Although changing b to not null in your example does allow the use of the PK index for COUNT(b), it is not absolutely necessary to have a not null constraint on the column to allow index usage. An index on the column, even if there are NULL values in the column is sufficient to allow index access. Since COUNT(expression) counts the non-null occurences of expression in the table, and Oracle does not index entirely NULL index keys, COUNT(indexed_column) will almost always use an index.
SQL> CREATE TABLE t AS
2 SELECT rownum id, TO_CHAR(TO_DATE(rownum, 'J'),'Jsp')
3 FROM all_objects
4 where rownum < 501;
Table created.
SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
DESCR VARCHAR2(78)
SQL> CREATE INDEX t_desc on t(descr);
Index created.
SQL> analyze table t compute statistics for table for all indexes
Table analyzed
SQL> select count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1842905362
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 500 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
SQL> select count(descr) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2760923846
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
| 2 | INDEX FAST FULL SCAN| T_DESC | 500 | 10000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Actually, my post might mislead readers that its ALWAYS true if you have a not null column then your PK index would be used instead of Full table scan. It is not necessarily true.
Thanks Spencer for continuing the case and completing it......
> Count(*) will try to access all DB blocks of the retrieved data before returning the Count. >
Count(<Literal>) will not do so. Hence it is faster than count(*).
Regards
K.Rajkumar
What is strange is that such an obvious myth that is so easy to prove wrong (explain plan, autotrace, tkprof etc, as posted above) should have stayed around for so long. I remember being told that COUNT(primary key column) was faster way back in Oracle 6 days, and I actually went around believing it for a couple of weeks until I happened to try it on a large table and noticed it made no difference.
The real difference is that using COUNT(1) makes you look like an idiot.
参见 https://community.oracle.com/thread/523654?tstart=0 count(0) count(*) 区别 谷歌 oracle count 0 谷歌 count 0 vs count - Google 搜索
differenct between count(0), count(1) and count(*)[哪个执行速度快?]
Counting recordsOver the years, there have been many debates regarding what is the optimal wayto count a selection of records from a table. Without going into the largehistory of this topic, dif