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) ?
.
.
.