differenct between count(0), count(1) and count(*)[哪个执行速度快？]
来源：互联网 发布：温州动车事故真相知乎 编辑：程序博客网 时间：2024/05/14 03:58
Counting records
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(*)---------- 283761Execution 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 processedSQL> select count(1) from PURCHASED_VEHICLES; COUNT(1)---------- 283761Execution 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 processedSQL> select count(rowid) from PURCHASED_VEHICLES;COUNT(ROWID)------------ 283761Execution 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 processedSQL> select count(id) from PURCHASED_VEHICLES; -- indexed colCOUNT(ID)--------- 283761Execution 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(*)---------- 283761Execution 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 processedSQL> select count(1) from PURCHASED_VEHICLES_JN; COUNT(1)---------- 478957Execution 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 processedSQL> select count(rowid) from PURCHASED_VEHICLES_JN; COUNT(ROWID)-------------- 478957Execution 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=========================================================================================================differenct between count(0), count(1) and count(*)...???
Hi,Please clarify the difference b/w count(0), count(1) and count(*)...???.SQL> set timing onSQL> select count(0) from trade2 /COUNT(0)----------112158506Elapsed: 00:00:03.08SQL> edWrote file afiedt.buf1* select count(1) from tradeSQL> /COUNT(1)----------112158506Elapsed: 00:00:02.01SQL> edWrote file afiedt.buf1* select count(*) from tradeSQL> /COUNT(*)----------112158506Elapsed: 00:00:02.03SQL> Is there any differences??ThanksSATHYA
-
1. Re: differenct between count(0), count(1) and count(*)...???
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
Message was edited by:SQL> select count(*) from scott.emp; COUNT(*)---------- 13SQL> select count(mgr) from scott.emp;COUNT(MGR)---------- 12SQL> select count(null) from scott.emp;COUNT(NULL)----------- 0SQL> select count(0) from scott.emp; COUNT(0)---------- 13SQL> select count(distinct mgr) from scott.emp;COUNT(DISTINCTMGR)------------------ 5SQL> select count(distinct 0) from scott.emp;COUNT(DISTINCT0)---------------- 1SQL> SQL> select count(distinct *) from scott.emp;select count(distinct *) from scott.emp *ERROR at line 1:ORA-00936: missing expression
ushitaki -
2. Re: differenct between count(0), count(1) and count(*)...???
This is a myth...
Better to runselect count('FAST') from your_table;;-)
Nicolas. -
3. Re: differenct between count(0), count(1) and count(*)...???
but it really works -
4. Re: differenct between count(0), count(1) and count(*)...???
It will run faster on subsequent runs due to data being held in the buffers... -
5. Re: differenct between count(0), count(1) and count(*)...???
hi,
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 -
6. Re: differenct between count(0), count(1) and count(*)...???
Looks the same to meadmin@10gR2> create table big_table as select * from all_objects;Table created.admin@10gR2> set autotrace traceonlyadmin@10gR2> alter system flush shared_pool 2 /System altered.admin@10gR2> select count(1) from big_table;Execution Plan----------------------------------------------------------Plan hash value: 599409829------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 185 (2)| 00:00:03 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| BIG_TABLE | 72970 | 185 (2)| 00:00:03 |------------------------------------------------------------------------Note----- - dynamic sampling used for this statementStatistics----------------------------------------------------- 322 recursive calls 0 db block gets 947 consistent gets 0 physical reads 0 redo size 413 bytes sent via SQL*Net to client 381 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processedadmin@10gR2> alter system flush shared_pool 2 /System altered.admin@10gR2> select count(*) from big_table;Execution Plan----------------------------------------------------------Plan hash value: 599409829------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 185 (2)| 00:00:03 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| BIG_TABLE | 72970 | 185 (2)| 00:00:03 |------------------------------------------------------------------------Note----- - dynamic sampling used for this statementStatistics---------------------------------------------------------- 322 recursive calls 0 db block gets 947 consistent gets 0 physical reads 0 redo size 413 bytes sent via SQL*Net to client 381 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed
-
7. Re: differenct between count(0), count(1) and count(*)...???
There is to be true no difference ... -
8. Re: differenct between count(0), count(1) and count(*)...???
any other differences?
For example:
count(1) - it will take the primay key and then count the records. I don't know this is true, but i am saying anything like this? -
9. Re: differenct between count(0), count(1) and count(*)...???
Nicolas, i recieve this output - what might be the reason according to you --
I'll be waiting for your reply. And, also input by others.satyaki>set timi onsatyaki>satyaki>select count(*) from psgr; COUNT(*)---------- 6153Elapsed: 00:00:03.09satyaki>satyaki>satyaki>select count(0) from psgr; COUNT(0)---------- 6153Elapsed: 00:00:07.07satyaki>satyaki>satyaki>select count(1) from psgr; COUNT(1)---------- 6153Elapsed: 00:00:00.07satyaki>satyaki>satyaki>select count('FAST') from psgr;COUNT('FAST')------------- 6153Elapsed: 00:00:02.08satyaki>satyaki>satyaki>satyaki>satyaki>select count('X') from psgr;COUNT('X')---------- 6153Elapsed: 00:00:01.00satyaki>select count('FAS') from psgr;COUNT('FAS')------------ 6153Elapsed: 00:00:00.08
Regards.
Satyaki De. -
10. Re: differenct between count(0), count(1) and count(*)...???
Count(*) will try to access all DB blocks of the
Any chance of getting additional information that'll back your statement?
retrieved data before returning the Count.
Count(<Literal>) will not do so. Hence it is faster
than count(*).
Otherwise it does sound like complete und utter bollocks to me.
C. -
11. Re: differenct between count(0), count(1) and count(*)...???
As i've tried to check it in this way but receive error --
Because, i don't have sufficient privs to check that. So, i've to rely on all of you.satyaki>satyaki>alter system flush shared_pool 2 /alter system flush shared_pool*ERROR at line 1:ORA-01031: insufficient privilegesElapsed: 00:00:00.07satyaki>
Regards.
Satyaki De. -
12. Re: differenct between count(0), count(1) and count(*)...???
Initially, from your post it seems that both are taking same time. Then why is it behabing such a strange way? Any explanation?
Regards.
Satyaki De. -
13. Re: differenct between count(0), count(1) and count(*)...???
Have you tried SELECT Count(*) twice in a row for example?
C. -
14. Re: differenct between count(0), count(1) and count(*)...???
Exactly my thoughts cd.Count(*) will try to access all DB blocks of the
faster
retrieved data before returning the Count.
Count(<Literal>) will not do so. Hence it isthan count(*).
Any chance of getting additional information that'll
back your statement?
Otherwise it does sound like complete und utter
bollocks to me.
C.
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.
-
15. Re: differenct between count(0), count(1) and count(*)...???
No differences at all.
But let me know why this question was posted....just out of curiosity.. -
16. Re: differenct between count(0), count(1) and count(*)...???
Exactly my thoughts cd.
Where's Billy with his trusty lead pipe, when you need him? ;-)
C. -
17. Re: differenct between count(0), count(1) and count(*)...???
I see no differences. Tired and Tested.
Adith -
18. Re: differenct between count(0), count(1) and count(*)...???
Hi,
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
----------------------------------------------------------
---------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FULL SCAN| TEST_PK | 3 | 1 (0)|
---------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
684 recursive calls
0 db block gets
112 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.01
SQL> select count(b) from test;
COUNT(B)
----------
3
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 2 (0)|
| 1 | SORT AGGREGATE | | 1 | 3 | |
| 2 | TABLE ACCESS FULL| TEST | 3 | 9 | 2 (0)|
----------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1101 recursive calls
0 db block gets
187 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
Now let me make the column B as NOT NULL as see what happens
SQL> set autotrace on
SQL> alter table test modify (b number not null);
Table altered.
Elapsed: 00:00:00.15
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.00
SQL> select count(b) from test;
COUNT(B)
----------
3
Elapsed: 00:00:00.11
Execution Plan
----------------------------------------------------------
---------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FULL SCAN| TEST_PK | 3 | 1 (0)|
---------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1101 recursive calls
0 db block gets
191 consistent gets
0 physical reads
0 redo size
411 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
The CBO knowing that the column B is not null uses the PK index to count the number of rows.
Regards -
19. Re: differenct between count(0), count(1) and count(*)...???
That is good examples.
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(*). -
20. Re: differenct between count(0), count(1) and 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 |--------------------------------------------------------------------------------
John -
21. Re: differenct between count(0), count(1) and count(*)...???
Hi,
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...... -
22. Re: differenct between count(0), count(1) and count(*)...???
> 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 搜索
0 0
- differenct between count(0), count(1) and count(*)[哪个执行速度快？]
- count(1) and count(*)
- count(),count(1),count(*)
- sql count执行速度测试
- count(*),count(1),count(0)效率
- sql中count(0)，count(1)，count()，count(列名)
- difference between count(*) and count(col) in the query
- count(1)、count(*)与count(列名)的执行区别
- Count
- COUNT
- count
- Count
- count
- count
- count
- count
- Count
- count
- 工具库篇
- HTTP关于表单请求的基础知识讲解
- 什么是MEAN全堆栈javascript开发框架
- Android 带清空按钮的EditText
- windows 驱动开发工具
- differenct between count(0), count(1) and count(*)[哪个执行速度快？]
- 第二章（线性表之顺序存储）
- Luci实现框架
- [LeetCode]Copy List with Random Pointer &Clone Graph 复杂链表的复制&图的复制
- 个性化控件(View)篇
- FragmentPagerAdapter.notifyDataSetChanged() 并不能更新其 Fragment？
- Regular Expression Matching
- （windows编程 学习笔记二） 结构化异常处理(异常处理程序和软件异常)
- 优秀项目篇
