Re: count (*) and index use (was RE: is it possible in pl/sql?)

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Thu, 17 Feb 2005 01:12:01 +0100
Message-ID: <002901c51485$4f034f30$3c02a8c0@JARAWIN>

Hi Jacques,

> A bitmap index will index columns with null values and therefore can be > used for a count (*) on the table.

A bitmap index makes the count(*) very performant indeed, but on the other side (as usual there is no free lunch) a bitmap index is usually defined on not very volatile tables, so you probably don't need the count(*) very frequently:)

Btw. there is a nice little bug/feature that makes the dispute between count(*) and count(<column_name>) even more interesting. See the difference in the execution plans for count(*) and count(b) below.

Note that count(1) and count(a) behave same as count(*) - use bitmap index. (column a is not nullable)

If the DOP of the table is lowered to 4 or below, count(b) switch to bitmap index as well.

Regards,

Jaromir Nemec

http://www.db-nemec.com

SQL> @bitmap
SQL> select * from v$version where rownum = 1;

BANNER

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

SQL> select num_rows, blocks, degree
2 from dba_tables
3 where table_name = 'T';

NUM_ROWS BLOCKS DEGREE
---------- ---------- ----------

7171775 62996 5

SQL> --
SQL> select column_name, nullable, NUM_DISTINCT 2 from dba_tab_columns where table_name = 'T';

COLUMN_NAME                    N NUM_DISTINCT
------------------------------ - ------------
A                              N           41
B                              Y      7171775

SQL> --
SQL> select index_name, index_type, BLEVEL, LEAF_BLOCKS, 2 AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR 3 from dba_indexes
4 where table_name = 'T';

INDEX_NAME                     INDEX_TYPE                      BLEVEL

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR

------------------------------ --------------------------- ---------- ----------- 
 ----------------------- ----------------------- -----------------
I1                             BITMAP                               2 
1122                      28                      56              2244
I2                             BITMAP                               2 
1356                       1                       1            185941

SQL> --
SQL> EXPLAIN PLAN set statement_id = 'N8' into lab.plan_table 2 FOR
3 select count(*) from t;

Explained.

SQL> --
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY('lab.plan_table', 'N8','ALL'));

PLAN_TABLE_OUTPUT

| Id  | Operation                     |  Name       | Rows  | Bytes | Cost

(%CPU)|

| 0 | SELECT STATEMENT | | 1 | | 1123 (1)|

|   1 |  SORT AGGREGATE               |             |     1 |       | 
|
|   2 |   BITMAP CONVERSION COUNT     |             |       |       | 
|
|   3 |    BITMAP INDEX FAST FULL SCAN| I1          |       |       | 
|


----------------------------------------------------------------------------------

9 rows selected.

SQL> --
SQL> EXPLAIN PLAN set statement_id = 'N9' into lab.plan_table 2 FOR
3 select count(b) from t;

Explained.

SQL> --
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY('lab.plan_table', 'N9','ALL'));

PLAN_TABLE_OUTPUT

|   0 | SELECT STATEMENT     |             |     1 |    69 |  1241   (2)| 
|      |            |
|   1 |  SORT AGGREGATE      |             |     1 |    69 |            | 
|      |            |
|   2 |   SORT AGGREGATE     |             |     1 |    69 |            | 
11,00  | P->S | QC (RAND)  |
|   3 |    TABLE ACCESS FULL | T           |  7171K|   471M|  1241   (2)| 
11,00  | PCWP |            |


------------------------------------------------------------------------------------------------------

PX Slave SQL Information (identified by operation id):

2 - SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(A1.C0)) FROM (SELECT /*+ NO_EXPAND ROWID(A2) */ A2."B"
C0 FROM "T" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A2) A1 16 rows selected.

SQL> spool off

Original Message ----- From: "Jacques Kilchoer" <Jacques.Kilchoer_at_quest.com> To: <oracle-l_at_freelists.org> Sent: Wednesday, February 16, 2005 1:09 AM Subject: count (*) and index use (was RE: is it possible in pl/sql?)

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 16 2005 - 19:16:51 CST