RE: Dead slow count(*)
- From: "Larry Elkins" <elkinsl@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Jan 2004 23:16:37 -0600
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 //www.freelists.org/archives/oracle-l/
> FAQ is at //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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
