Oracle Count 函数的实验

sqysl

抱歉，我这里不该用类似的简称，我这里的FTS=full table scan，就是全表扫描的意思。

棉花糖ONE

原帖由 sqysl 于 2009-10-9 09:05 发表
这个话题以前记得讨论过，您这里的SELECT COUNT (b.PLANRMK) 查询的不是行数，而是这个字段里值的个数，由于null不是任何值，所以在计数时不作为一个数据来计数，因此产生了您说说的结果，而select(PK),select count(*),select count(1)中，select(PK)是查询的表的主键字段值的个数，因为主键是不重复而且不为空的，所以它和表里记录的个数是一样的；select count(*)就不用说了，就是查询的表的行数；select count(1)这里是查询的第一个字段的值的个数，其实，如果第一个字段存在空值也统计不出行数，甚至会产生您说的结果。至于您说的select(not null column)也是求得的是表的非空字段值的个数，如果不加DISTINCT关键字，也可以是等于表的行数。而且，您例子中的查询走的是FTS。

你怎么知道楼主例子里的走的是fts ???

棉花糖ONE

oracle的聚合函数会忽略null

棉花糖ONE

原帖由 Kenniu 于 2009-10-9 08:15 发表
以下一点小经验，希望对你们有所帮助。
在做项目的时候,有修改到的procedure中写了类似这样一句
SELECT COUNT (b.PLANRMK) INTO varplantmkcount FROM  smtbcrud b WHERE ...
If varplantmkcount > 0
    SELECT b.PLANRMK into varplantmk FROM  smtbcrud b WHERE ...
    If varplantmk is null
        ...
    End If;
End If;
本意是想如果smtbcrud里面如果找到了匹配的行的话才继续做If里面的东西，而我的If里面做的是判断如果PLANRMK为空的话则update为***.
用意很简单，但是却出现了问题，PLANRMK 为空的话update不了。

大家可以试一下，如果TableA的栏位a的值全部为Null的话，大家用这句Sql试一下：
select count(a) from TableA
结果是多少？TableA的行数(我以前是这样认为的)？错了，结果为0.

原来Count会忽略null的行。
可以再试一个例子，还是TableA的栏位a, 除了一行是有值之外，其他行都是为Null，再用这句Sql：
select count(a) from TableA
想必大家都知道结果了，结果为1，Null的行被忽略掉了。

怎么解决？用select(PK),select count(*),select count(1)或者select(not null column)代替就好了。
当然这些方式在效率上会有一点点差异，但如果不是数据量很大的话，基本上我们可以忽略。如果有可能数据量很大的话，我的愚见是select(PK)>select count(*)>select count(1)>select(not null column), 大家可以上网找一些资料，或者自己做一些测试。
当然你硬要用这个栏位的话，又想不忽略null行的话，你可以试一下
select count(nvl(a,0)) from TableA

count(pk),count(*),count(1)这几个在执行计划一样的情况下,并没有什么区别,至少在现在的版本里,可能历史版本有些区别

如果存在pk,count(*)，count(1)一般也会选择index fast full scan(pk index)

sqysl

因为这个列可以为空，即使上面建有索引且每行有值，在进行COUNT统计时，也不会走这个索引的，除非该列非空。

棉花糖ONE

原帖由 sqysl 于 2009-10-9 13:19 发表
因为这个列可以为空，即使上面建有索引且每行有值，在进行COUNT统计时，也不会走这个索引的，除非该列非空。

因为这个count(object_id)本身是忽略空的,索引直接读索引是满足这个的,count(OBJECT_ID)相当于已经隐含了一个object_id not null的条件,聚合函数不统计null,所以恰好构成了index fast full scan的前提


SCOTT@oracle10g>create table kk as select * from dba_objects;

Table created.

SCOTT@oracle10g>create index i_kk_1 on kk(object_id);

Index created.

SCOTT@oracle10g>set autot trace
SCOTT@oracle10g>select count(OBJECT_ID) FROM KK;


Execution Plan
----------------------------------------------------------
Plan hash value: 3759150837

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    13 |    30   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_KK_1 | 50592 |   642K|    30   (4)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        190  consistent gets
        112  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SCOTT@oracle10g>SET AUTOT OFF
SCOTT@oracle10g>/

COUNT(OBJECT_ID)
----------------
           50482

SCOTT@oracle10g>INSERT INTO KK(object_id) values(null);

1 row created.

SCOTT@oracle10g>commit;

SCOTT@oracle10g>set autot trace
SCOTT@oracle10g>select count(*) from kk;


Execution Plan
----------------------------------------------------------
Plan hash value: 148281040

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   171   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| KK   | 55735 |   171   (2)| 00:00:03 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        717  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SCOTT@oracle10g>select count(object_id) from kk;


Execution Plan
----------------------------------------------------------
Plan hash value: 3759150837

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    13 |    30   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |    13 |            |          |
|   2 |   INDEX FAST FULL SCAN| I_KK_1 | 55735 |   707K|    30   (4)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        119  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SCOTT@oracle10g>select count(*) from kk where object_id is not null;


Execution Plan
----------------------------------------------------------
Plan hash value: 3759150837

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |     1 |    13 |    30   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE       |        |     1 |    13 |            |          |
|*  2 |   INDEX FAST FULL SCAN| I_KK_1 | 55735 |   707K|    30   (4)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_ID" IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        193  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

sqysl

嗯，你说的是对的。
其实，即使你不往KK里插入OBJECT_ID=NULL数据行，SELECT COUNT(*) FROM KK也会走全表扫的。
SELECT DISTINCT OBJECT_ID FROM KK也是一样。

棉花糖ONE

原帖由 sqysl 于 2009-10-9 14:36 发表
嗯，你说的是对的。
其实，即使你不往KK里插入OBJECT_ID=NULL数据行，SELECT COUNT(*) FROM KK也会走全表扫的。
SELECT DISTINCT OBJECT_ID FROM KK也是一样。

yes,这时候oracle无法获得隐式的not null条件,就必须通过not null约束或者object_id is not null条件,才能保证iffs得到正确的结果

Yong Huang

> SELECT COUNT (b.PLANRMK) INTO varplantmkcount FROM  smtbcrud b WHERE ...
> If varplantmkcount > 0

This may be a typical question in an interview: How do you improve the above PL/SQL code, suppose smtbcrud is a fairly large table? The answer is simple: add "rownum = 1" to the where clause. (After that, the "If varplantmkcount" check can be either "> 0" or "= 1".) Have you wondered why "grep -l pattern file" is faster than "grep pattern file" if all you want is to know if pattern exists in file and pattern is not near the end of file? It's the same idea.

> 用select(PK),select count(*),select count(1)或者select(not null column)代替就好了。
> 当然这些方式在效率上会有一点点差异

There shouldn't be any difference in performance.

> select count(nvl(a,0)) from TableA

That will be slightly slower because it uses CPU to process nvl.

> select count(1)这里是查询的第一个字段的值的个数

That's not right. select count(1) is actually still counting all rows. If you really want, you can say it's counting how many times each row can be represented by the number 1. Of course every row can. It's like select 1 from mytable. How many rows will that return? All rows in mytable.

Also, when we say an index ignores null, we should qualify that with "B*Tree index", because a bitmap index indexes null values. And in case of a composite B*Tree index, it skips the row only if *all* columns in the index are null.

Yong Huang

[ 本帖最后由 Yong Huang 于 2009-10-9 20:23 编辑 ]

sqysl

谢谢YONGHUANG的回复，学习了。