RE: Performance off "count(*)"

Thanks all for the examples. A lot (re-)think and I guess I will have to do some further testing.  

In case you wonder, I am working on a: XMLType table, based on Binary XML Securefile storage with has the compression parameter set to high.

Van: Gints Plivna [mailto:gints.plivna_at_gmail.com] Verzonden: vr 18-7-2008 16:25
Aan: Riyaj Shamsudeen
CC: Marco Gralike; oracle-l_at_freelists.org Onderwerp: Re: Performance off "count(*)"

2008/7/18, Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>:
> 1. From 10g onwards Cost based query transformation kicks in and rewrites
> count(col) to count(*).

A bit clarification - it is done only if col is not null, isn't it?

And speaking about bitmap indexes they also contain nulls so just a bit exhancing previous example:

SQL> create table t1 (a1 number , v1 varchar2(512) );

Table created.

SQL> insert into t1 select n1, lpad(n1, 512,'x') from (select level n1  from dual connect by level <
=10000);

10000 rows created.

SQL> desc t1

 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 A1                                                 NUMBER
 V1                                                 VARCHAR2(512)

SQL> create bitmap index t1_idx1 on t1 (a1);

Index created.

SQL> set autot on
SQL> insert into t1 values (null, null);

1 row created.

And this is really nice example because for each count(*), count(a1) and count(v1) I got different plan:
SQL> select count(*) from t1;

  COUNT(*)

     10001

Execution Plan

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=31 Card=1)    1 0 SORT (AGGREGATE)

   2    1     BITMAP CONVERSION (COUNT) (Cost=31 Card=10000)
   3    2       BITMAP INDEX (FAST FULL SCAN) OF 'T1_IDX1' (INDEX (BIT
          MAP))

 COUNT(A1)

     10000

Execution Plan

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=31 Card=1 Bytes=4)    1 0 SORT (AGGREGATE)

   2    1     BITMAP CONVERSION (TO ROWIDS) (Cost=31 Card=10000 Bytes=
          40000)

   3    2       BITMAP INDEX (FAST FULL SCAN) OF 'T1_IDX1' (INDEX (BIT
          MAP))

SQL> select count(v1) from t1;

 COUNT(V1)

     10000

Execution Plan

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=180 Card=1 Bytes=5
          13)

   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=180 Card=10000
           Bytes=5130000)

Gints Plivna
http://www.gplivna.eu <http://www.gplivna.eu/>

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