Counting the Rows in a Table

Tanel Poder points out how COUNT(*) and COUNT(column) are not necessarily the same. A COUNT on a Column ignores NULL values in the column !

Here, I conduct a few more tests.

SQL> create table test_count (col_1 varchar2(5), col_2 varchar2(5), col_3 varchar2(5));

Table created.

SQL> insert into test_count values ('a','first','1');

1 row created.

SQL> insert into test_count values ('b',null,'2');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test_count;

COUNT(*)
----------
   2

SQL> select count(col_1) from test_count;

COUNT(COL_1)
------------
     2

SQL> select count(col_2) from test_count;

COUNT(COL_2)
------------
     1

SQL> 

Although the table has 2 rows, since one of the rows is a NULL in COL_2, a count on COL_2 misses that row.

SQL> insert into test_count values ('',NULL,'');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from test_count;

COUNT(*)
----------
    3

SQL> select count(col_1) from test_count;

COUNT(COL_1)
------------
      2

SQL> select count(col_2) from test_count;

COUNT(COL_2)
------------
      1

SQL> 

I inserted a row with all NULLs. Oracle allows me to insert an ALL NULL row. Now, a count on any column returns incorrect results. Only a COUNT(*) is correct.


Now, I proceed to another test. Here I create a larger table.

SQL> select count(*) from dba_objects where object_id is null;

COUNT(*)
----------
     1

SQL> create table another_test_count as select owner, object_id, object_type from dba_objects;

Table created.

SQL> select count(*) from another_test_count;

COUNT(*)
----------
 50628

SQL> select count(owner) from another_test_count;

COUNT(OWNER)
------------
   50628

SQL> select count(object_id) from another_test_count;

COUNT(OBJECT_ID)
----------------
       50627

SQL> 

As I expected, a COUNT on OBJECT_ID is one row short as one of the OBJECT_IDs is NULL.

I now create an Index on the table.

SQL> create index another_test_count_ndx on another_test_count(object_id,owner);   

Index created.

SQL> exec dbms_stats.gather_table_stats('','ANOTHER_TEST_COUNT',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> 

Now, I attempt to use the Index to count the number of rows. The index is smaller than the table so an INDEX FAST FULL SCAN should be preferred over a FULL TABLE SCAN.

SQL> select count(*) from another_test_count;

COUNT(*)
----------
   50628


Execution Plan
----------------------------------------------------------
Plan hash value: 3536081126

---------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |    52   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |                    |     1 |            |          |
|   2 |   TABLE ACCESS FULL| ANOTHER_TEST_COUNT | 50628 |    52   (2)| 00:00:01 |
---------------------------------------------------------------------------------


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

SQL> alter table another_test_count modify (owner not null);

Table altered.

SQL> select count(*) from another_test_count;

 COUNT(*)
----------
    50628


Execution Plan
----------------------------------------------------------
Plan hash value: 227569207

----------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |     1 |    43   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                        |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| ANOTHER_TEST_COUNT_NDX | 50628 |    43   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


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

SQL>  

In my first pass at a COUNT(*), Oracle does not use the Index even though it is smaller than the table. Why not ? Because Oracle cannot be sure that the Index captures every ROWID from the table. This is the case where, for a concatenated index, every column has a NULL for a particular row, resulting in that row being "excluded" from the Index.

As soon as I change the OWNER column (which isn't even the leading column of the Index) to a NOT NULL, the Optimizer is assured that an Index on this column *will* capture every ROWID. At the next COUNT(*), the Optimizer prefers to do an INDEX FAST FULL SCAN !

(Note : I had run the "SELECT COUNT(*) FROM ANOTHER_TEST_COUNT" twice before altering OWNER to NOT NULL and twice again after altering it to NOT NULL. I have not reported the autotrace results of each first run as it includes Parse overheads -- the recursive calls inflating the 'consistent gets' count).

.
.
.

Using an Index created by a different user

Can a query by user "C" on a table owned by user "A" actually use an Index owned and created by "B", although on "A"'s table ?
YES. "C" needs the SELECT privilege on "A"'s table as would be necessary to run the query but does not need any reference/privilege on "B"'s index.


Here's an example :

Table owner : HEMANT
Index owner : AN_INDEX_OWNER
Query run by : A_QUERY_USER

Account "A_QUERY_USER" has SELECT on the table "MY_COPY_OF_OBJECTS" in HEMANT's schema but no privileges on the index in AN_INDEX_OWNER's schema (there's no such thing as granting SELECT on an Index)
Account "AN_INDEX_OWNER" has SELECT on "MY_COPY_OF_OBJECTS" in HEMANT's schema and the privilege to create an Index

When "A_QUERY_USER" runs a query on HEMANT's table, the query does use the Index created by "AN_INDEX_OWNER" !

SQL> connect hemant/hemant
Connected.
SQL> create table my_copy_of_objects as select * from dba_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats('','MY_COPY_OF_OBJECTS',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> 
SQL> create user a_query_user identified by a_query_user;

User created.

SQL> grant create session to a_query_user;

Grant succeeded.

SQL> grant plustrace to a_query_user;

Grant succeeded.

SQL> grant select on my_copy_of_objects to a_query_user;

Grant succeeded.

SQL> 
SQL> create user an_index_owner identified by an_index_owner;

User created.

SQL> -- needs "CREATE ANY INDEX" and  SELECT  to be able to create an index
SQL> grant create session,create any index to an_index_owner;

Grant succeeded.

SQL> grant select on my_copy_of_objects to an_index_owner;

Grant succeeded.

SQL> alter user an_index_owner default tablespace users;

User altered.

SQL> alter user an_index_owner quota unlimited on users;

User altered.

SQL> 
SQL> connect an_index_owner/an_index_owner
Connected.
SQL> create index hemant_m_c_o_o_ndx_1 on hemant.my_copy_of_objects(object_id);

Index created.

SQL> create index hemant_m_c_o_o_ndx_2 on hemant.my_copy_of_objects(owner);

Index created.

SQL> 
SQL> -- Verify indexes owned by AN_INDEX_OWNER
SQL> select index_name, table_owner, table_name from user_indexes order by 1;

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
HEMANT_M_C_O_O_NDX_1           HEMANT                         MY_COPY_OF_OBJECTS
HEMANT_M_C_O_O_NDX_2           HEMANT                         MY_COPY_OF_OBJECTS

SQL> 
SQL> -- Verify that indexes on HEMANT's tables are owned by AN_INDEX_OWNER
SQL> connect / as sysdba
Connected.
SQL> select owner, index_name, table_owner, table_name from dba_indexes where table_name = 'MY_COPY_OF_OBJECTS' order by 1,2;

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
AN_INDEX_OWNER                 HEMANT_M_C_O_O_NDX_1           HEMANT                         MY_COPY_OF_OBJECTS
AN_INDEX_OWNER                 HEMANT_M_C_O_O_NDX_2           HEMANT                         MY_COPY_OF_OBJECTS

SQL> 
SQL> REM REM REM ################
SQL> 
SQL> -- now we query the table
SQL> connect a_query_user/a_query_user
Connected.
SQL> select owner, object_name, object_id from hemant.my_copy_of_objects where object_id > 54087;

OWNER                          OBJECT_NAME                     OBJECT_ID
------------------------------ ------------------------------ ----------
HEMANT                         MY_COPY_OF_OBJECTS                  54123

SQL> select count(*) from hemant.my_copy_of_objects where owner = 'HEMANT';

  COUNT(*)
----------
        17

SQL> 
SQL> -- rerun the queries, we avoid parse overheads now
SQL> 
SQL> set autotrace on
SQL> select owner, object_name, object_id from hemant.my_copy_of_objects where object_id > 54087;

OWNER                          OBJECT_NAME                     OBJECT_ID
------------------------------ ------------------------------ ----------
HEMANT                         MY_COPY_OF_OBJECTS                  54123


Execution Plan
----------------------------------------------------------
Plan hash value: 2159204631

----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |    34 |  1224 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_COPY_OF_OBJECTS   |    34 |  1224 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | HEMANT_M_C_O_O_NDX_1 |    34 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID">54087)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        676  bytes sent via SQL*Net to client
        492  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(*) from hemant.my_copy_of_objects where owner = 'HEMANT';

  COUNT(*)
----------
        17


Execution Plan
----------------------------------------------------------
Plan hash value: 360370019

------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      |     1 |     6 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                      |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| HEMANT_M_C_O_O_NDX_2 |  1876 | 11256 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("OWNER"='HEMANT')


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

SQL> 

.
.
.

A PACKT book on Oracle Database Utilities

A few days ago, PACKT Publishing invited me to review the book "Oracle 10g/11g Data and Database Management Utilities" by Hector R. Madrid.

If you visit their website, you will notice that they publish books which are "off the mainstream". Their collection of Oracle Books isn't what you see from, say, Oracle Pubishing or McGrawHill or Apress. Obviously, they aren't looking at very high volume sales but want to attract buyers of books on specific or even specialised topics.

I have being reading the "Oracle 10g/11g Data and Database Management Utilities" eBook. I appreciate the chapters on
SQL Loader,
External Tables,
RMAN,
Session Management (locking and waiting for locks, killing sessions, resource manager, ASH, even Service Registration) {never before have I seen all these topics put together in a Chapter, although I wish that this chapter could be expanded, even doubled in size},
Scheduler,
DBCA, OUI, EMCA and OPatch -- each being a seperate Chapter.
(there are other chapters as well).

All in all, this is a book worth having available on one's desktop (either in hard copy or soft copy), as a Reference.

Hector Madrid maintains an Oracle Blog.
Hans Forbrich (many of us know him on forums.oracle.com) is a Reviewer.

.
.
.