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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 15 Feb 2005 16:09:17 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87C65@irvmbxw02>

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

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

Received on Tue Feb 15 2005 - 19:12:12 CST