More on COUNT()s -- 2

Continuing with my previous posts "Running a COUNT(Column) versus COUNT(*)" and"More on COUNT()s", I now go on to demonstrate some more "twists" :

SQL> drop table COUNT_ROWS_TBL ;

Table dropped.

SQL> create table COUNT_ROWS_TBL (Column_1 number, Column_2 number, Column_3 varchar2(100)) ;

Table created.

SQL> insert into COUNT_ROWS_TBL
  2  select decode(mod(rownum,10),0,NULL,rownum) as Column_1,
  3         rownum as Column_2,
  4         'Column_3_Values' || dbms_random.string('X',80) as Column_3
  5  from dual
  6  connect by level < 1001
  7  /

1000 rows created.

SQL> -- remember that Column_2 has values 1 to 1000 and has no NULLs
SQL> -- note how count(Column_1) excludes rows with a NULL
SQL> select count(Column_2) from COUNT_ROWS_TBL where Column_2 > 400;

COUNT(COLUMN_2)
---------------
            600

SQL> select count(Column_1) from COUNT_ROWS_TBL where Column_2 > 400;

COUNT(COLUMN_1)
---------------
            540

SQL> -- this is the count of rows that the query for count(Column_1) excludes
SQL> select count(*) from COUNT_ROWS_TBL where Column_2 > 400 and Column_1 is NULL;

  COUNT(*)
----------
        60

SQL> 
SQL> -- Bitmap Index example : If we have a Bitmap index on Column_1
SQL> create bitmap index COUNT_ROWS_TBL_BMP_1 on COUNT_ROWS_TBL(Column_1);

Index created.

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

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select count(Column_1) from COUNT_ROWS_TBL;

COUNT(COLUMN_1)
---------------
            900


Execution Plan
----------------------------------------------------------
Plan hash value: 1724349832

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     1 |     4 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                      |     1 |     4 |            |          |
|   2 |   BITMAP CONVERSION TO ROWIDS |                      |  1000 |  4000 |     4   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| COUNT_ROWS_TBL_BMP_1 |       |       |            |          |
------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        419  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 COUNT_ROWS_TBL;

  COUNT(*)
----------
      1000


Execution Plan
----------------------------------------------------------
Plan hash value: 2301416134

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     1 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                      |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |                      |  1000 |     4   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| COUNT_ROWS_TBL_BMP_1 |       |            |          |
----------------------------------------------------------------------------------------------


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

SQL> -- Now it could use a Bitmap index even if the column contains NULLs !
SQL> -- BUT !  The Count for the column excluded the NULLs ! 

So, we can have a COUNT of the rows returned from the WHERE clause (or all rows of the table if there is no WHERE clause) executed by :
1. A FULL TABLE SCAN
2. An INDEX [FAST] FULL SCAN
3. A BITMAP Index

In any case, if you specify a Column as your COUNT target and the Column does contain a NULL in one or more rows, the rows with the NULL are excluded.
On the other hand, if you specify a constaint (e.g. a "1") or a * as your COUNT target, no rows are excluded (other than rows that may have been excluded by filters specified in the WHERE clause).

Notice how I have NOT demonstrated a Primary Key index (or a Unique Index) being used for a Count. It is not necessary for an Index to be a Primary Key index for it to be usable by Oracle for this purpose. There are a number of Internet posts that suggest that Oracle can use a Primary Key to execute a count. The truth of the matter is that Oracle can use any Index if the index contains elements that are NOT NULL *OR* it can use a Bitmap Index because a Bitmap Index also captures NULLs.

Would anyone want to extent the test case to an Index Organized Table (an IOT) ?

.
.
.