| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_flash.net
214.954.1781
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
> Sent: Thursday, January 29, 2004 10:53 PM
> To: oracle-l_at_freelists.org
> 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_at_dv01 SQL> alter table
> >count_test modify( row_number not null)
>
>
> >17:19:19 rsysdevdb.radisys.com - jkstill_at_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_at_freelists.org
> 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
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Jan 29 2004 - 23:16:37 CST
 |
 |