=================================
count(*),count(1),count(col)区别
=================================
count(1)和count(*)都是统计表的总行数，两者执行结果相同。
表上没有主键或者唯一键索引，两者都走全表扫描；
表上有主键或者唯一键索引，那么走主键或者唯一键索引。
count(col)则是统计col列中不为空的总行数,如果该列存在索引，那么自动走索引（INDEX FULL SCAN）；
否则走全表扫描。
验证：
--count(1)和count(*)都是统计表的总行数，两者执行结果相同。
SQL> set autot on
SQL> select count(*) from count_t;
COUNT(*)
----------
20
Execution Plan
----------------------------------------------------------
Plan hash value: 711883932
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_ID | 20 | 1 (0)| 00:00:01 |
------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 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 count_t;
COUNT(1)
----------
20
Execution Plan
----------------------------------------------------------
Plan hash value: 711883932
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| PK_ID | 20 | 1 (0)| 00:00:01 |
------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--count(col)则是统计col列中不为空的总行数。如果该列存在索引，那么自动走索引（INDEX FULL SCAN）；否则走全表扫描。
--没有索引走全表扫描
SQL> select count(name) from count_t;
COUNT(NAME)
-----------
5
Execution Plan
----------------------------------------------------------
Plan hash value: 1307538749
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | TABLE ACCESS FULL| COUNT_T | 20 | 240 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
132 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
--创建索引
SQL> create index ind_name on count_t(name);
Index created.
--有索引走索引
SQL> select count(name) from count_t;
COUNT(NAME)
-----------
5
Execution Plan
----------------------------------------------------------
Plan hash value: 2640505279
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | INDEX FULL SCAN| IND_NAME | 20 | 240 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
**另外附tom说的关于count(*)和count(1)完全相同的回答
You Asked
What is the difference between count(1) and count(*) in a sql query
eg.
select count(1) from emp;
and
select count(*) from emp;
and we said...
nothing, they are the same, incur the same amount of work -- do the same thing, take the
same amount of resources.
You can see this via:
ops$tkyte@ORA817.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA817.US.ORACLE.COM> select count(*) from all_objects;
COUNT(*)
----------
27044
ops$tkyte@ORA817.US.ORACLE.COM> select count(1) from all_objects
2 /
COUNT(1)
----------
27044
and the tkprof will show:
select count(*)
from
all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.56 5.56 0 234998 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.58 5.58 0 234998 4 1
select count(1)
from
all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.46 5.47 0 234998 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.48 5.49 0 234998 4 1
Same number of blocks read/written/processed, same cpu times (basically) same elapsed
times (basically).
they are identical.
Anyone who thinks different (and I know you are out there) will have to post a test case
like the above or some scientific proof otherwise to be taken seriously....
阅读(878) | 评论(0) | 转发(0) |
