count (*) and index use (was RE: is it possible in pl/sql?)
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Feb 2005 16:09:17 -0800
>-----Original Message-----
>Lex de Haan
>
> a column with a unique index is not good enough --
> otherwise it might be quicker, but also give different results ;-)
> it must be an index on a NOT NULL column, and it is actually irrelevant
> whether the index is unique; it's only the index *size* that counts.
Another wrinkle: in Oracle, the index does not always have to be on a not null
column. A bitmap index will index columns with null values and therefore can be
used for a count (*) on the table.
SQL> describe t
Nom NULL
? Type
------------------------------------------------------------------------
-------- -----------------
D
DATE
SQL> create bitmap index ti on t (d) ;
Index créé.
SQL> select count (*) from t where d is null ;
COUNT(*)
---------
33150
SQL> set autotrace on explain
SQL> select /*+ index (t ti) */ count (*) from t where d is null ;
COUNT(*)
---------
33150
Plan d'exécution
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'TI'
--
//www.freelists.org/webpage/oracle-l
Other related posts:
