Re: count (*) and index use (was RE: is it possible in pl/sql?)
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <Jacques.Kilchoer@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Feb 2005 01:12:01 +0100
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?)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
