RE: Dead slow count(*)

Or be a bitmap index, in which case the NOT NULL isn't required as the nulls
are indexed. I know you know that, but throwing it out there for others that
might not have worked much with BMI's.

SQL> create table foo_bmi as select * from dba_objects;

Table created.

SQL> desc foo_bmi
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)   <<<---
Nullable
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> create bitmap index ffo_bmi_ot_idx on foo_bmi (object_type);

Index created.

SQL> analyze table foo_bmi compute statistics;

Table analyzed.

SQL> set autotrace trace explain
SQL> select count(*) from foo_bmi;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3.3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT)
   3    2       BITMAP INDEX (FULL SCAN) OF 'FFO_BMI_OT_IDX'      <<<---

SQL> drop index ffo_bmi_ot_idx;

Index dropped.

  1* create index ffo_bmi_ot_idx on foo_bmi (object_type) compute statistics
SQL> /

Index created.

SQL> select count(*) from foo_bmi;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'FOO_BMI' (Cost=64 Card=31283)

SQL> alter table foo_bmi modify (object_type varchar2(18) not null);

Table altered.

SQL> select count(*) from foo_bmi;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'FFO_BMI_OT_IDX' (NON-UNIQUE)
          (Cost=14 Card=31283)

Regards,

Larry G. Elkins
elkinsl@xxxxxxxxx
214.954.1781

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
> Sent: Thursday, January 29, 2004 10:53 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Dead slow count(*)
>
>
> That, of course, is the crucial piece. The index must contain a
> "not null"
> column or else Oracle can not guarantee that every row will be
> indexed and
> that therefore the count of indexed entries will be equal to the
> count of rows.
>
> At 06:20 PM 1/29/2004, you wrote:
> >17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> alter table
> >count_test  modify( row_number not null)
>
>
> >17:19:19 rsysdevdb.radisys.com - jkstill@dv01 SQL> create index ct_idx_1
> >on count_test(row_number);
>
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: