Search My Oracle Blog

Custom Search

30 July, 2011

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

.
.
.

1 comment:

Anonymous said...

Thanks!

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016