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

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

------------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)| 
TQ    |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
|   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@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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

Other related posts: