Oracle 11g: Unexpected Difference Between count(*) and count(1)

Oct 3, 2007 / By Grégory Guillou

Tags: ,

It seems Oracle 11g introduces a difference between count(*) and count(1). The way this happens is just the opposite of what I was thinking would happen. NB: I ran my test using “11.1.0.6 32bits” on Ubuntu Linux 7.04 (Feisty) which is not officially supported1, and which has already lead me to some unexpected behaviors. If this difference with count() is really the 11g way and not buggy behavior related to the Ubuntu install, I’m glad to have found it.

Here’s what you can do to observe (or confirm or dispute) this.

1. Reproduce the test

I’ll need the simplest table possible:

create table gg(
   col1 number);

I’ll create two distinct views of this table as below :

  1. gg_v1 makes use of a count(1)
  2. gg_v2 makes use of a count(*)
create view gg_v1 as 
 select count(1) num from gg;

create view gg_v2 as 
 select count(*) num from gg;

I’ll let you check that these two views are valid. And then I’ll let you add a column:

alter table gg add (col2 number);

2. Display the result

The query below displays the status of the two views after the change on the table:

col object_name format a10
col object_type format a5
col status format a8

select object_name, object_type, status
  from user_objects
 where (object_name='GG_V1'
        or object_name='GG_V2')
   and object_type = 'VIEW'
 order by object_name, object_type;

OBJECT_NAM OBJEC STATUS
---------- ----- --------
GG_V1      VIEW  INVALID
GG_V2      VIEW  VALID

So the view with count(1) has been invalidated, while the other with count(*) has not!

I’ve read this section of the 11g Concept Guide that explains the dependencies when you add a column to a table, but I have absolutely no idea why the count(1) view is invalidated. I’ll be very interested if one of you knows.

1. For reference, see also Augusto’s item, Installing Oracle 11g on Ubuntu Linux 7.04.

5 Responses to “Oracle 11g: Unexpected Difference Between count(*) and count(1)”

  • Shakespeare says:

    Just for completeness: could you check what happens to select count(null) ? We have had a disussion in a newsgroup about count(1) vs. count(null).

  • Eric says:

    I just verified this is indeed different from behaviour in 10.2.0.3 on Solaris 10:
    (9.2.0.8 as well)

    SQL*Plus: Release 10.2.0.3.0 – Production on Thu Oct 4 17:38:11 2007

    Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
    With the Partitioning, OLAP and Data Mining options

    SQL>
    SQL> — create simple table
    SQL>
    SQL> create table gg(
    2 col1 number);

    Table created.

    SQL>
    SQL> — create two distinct views of this table as below :
    SQL> — gg_v1 makes use of a count(1)
    SQL> — gg_v2 makes use of a count(*)
    SQL>
    SQL> create view gg_v1 as
    2 select count(1) num from gg;

    View created.

    SQL>
    SQL> create view gg_v2 as
    2 select count(*) num from gg;

    View created.

    SQL>
    SQL> — check that these two views are valid.
    SQL>
    SQL> col object_name format a10
    SQL> col object_type format a5
    SQL> col status format a8
    SQL>
    SQL> select object_name, object_type, status
    2 from user_objects
    3 where (object_name=’GG_V1′
    4 or object_name=’GG_V2′)
    5 and object_type = ‘VIEW’
    6 order by object_name, object_type;

    OBJECT_NAM OBJEC STATUS
    ———- —– ——–
    GG_V1 VIEW VALID
    GG_V2 VIEW VALID

    SQL>
    SQL> — And then add a column:
    SQL>
    SQL> alter table gg add (col2 number);

    Table altered.

    SQL>
    SQL> — 2. Display the result
    SQL>
    SQL> — The query below displays the status of the two views after the change on the table:
    SQL>
    SQL> col object_name format a10
    SQL> col object_type format a5
    SQL> col status format a8
    SQL>
    SQL> select object_name, object_type, status
    2 from user_objects
    3 where (object_name=’GG_V1′
    4 or object_name=’GG_V2′)
    5 and object_type = ‘VIEW’
    6 order by object_name, object_type;

    OBJECT_NAM OBJEC STATUS
    ———- —– ——–
    GG_V1 VIEW INVALID
    GG_V2 VIEW INVALID

    SQL>
    SQL>
    SQL> drop table gg;

    Table dropped.

    SQL>
    SQL> drop view gg_v1;

    View dropped.

    SQL>
    SQL> drop view gg_v2;

    View dropped.

    SQL>

  • Shakespeare says:

    Sorry for that count(null), different discussion, should return 0 at any time… was a discussion about select null from dual.

  • [...] Grégory Guillou discovered an unexpected difference between count(*) and count(1). Also here at home, Robert Hamel gives a bad rating to 11g’s new pivot table feature. [...]

  • Virgile Crevon says:

    Hi Greg,

    Check about improvement concerning 11G, this is the key. I’ve found that behavior is call “Fine grained dependency tracking”.

    While certain things require view to be invalidated, somes do not. For example with DDL “ADD COLUMN” :
    count(*), select *, select col1 (must exist before the add). And so on.

    Check this one http://laurentschneider.com/wordpress/2006/10/11g-new-features.html

    I quote Laurent Schneider :

    Fine grained dependency tracking. Currently, if you have a view or a function based on a table, and if you modify that table, the view is getting invalid. This is what Bryn called : aggressive object invalidation. Now in 11g it has improved, so if you modify an element of the table which is not included in that view (ex: you add a column), the view is not invalidated. This is wise.

    Regards,

    Virgile CREVON

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>