Re: nonefficient count(*)

I'm sure that I must be missing the point here but..... if you select count (*) from table_name surely a full table scan must be performed?

Andy.

Vadim Grepan <kezal_at_mail.ru> wrote in message news:3B3B4544.89E4C660_at_mail.ru...
> Hello All!
>
> I have the table with PK (pk_liquid_product on VARCHAR(12)),
> non-unique index (ndx_liquid_product__artist_id on NUMBER(11)) and
> around 30 non-indexed fields
> (VARCHAR2, NUMBER etc). Total records are around 150.000.
>
> I've got following observation, optimizer doesn't use PK-index
> preferring full-scan.
> Moreover I cannot make to use PK-index even by hint. It's true for
> analyzed and non-analyzed table.
> Are there any explanation and solutions? TFHAOT
>
> Oracle 8.1.7.1, Sun SPARC
>
> ### 1 ######
> SQL> select count(*) from liquid_product;
>
> Elapsed: 00:00:03.03
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 SORT (AGGREGATE)
> 2 1 TABLE ACCESS (FULL) OF 'LIQUID_PRODUCT'
>
> Statistics
> ----------------------------------------------------------
> 3197 consistent gets
> 3132 physical reads
>
>
> ### 2 ######
> SQL> select /*+ INDEX (pk_liquid_product) */ count(*) from
> liquid_product;
>
> Elapsed: 00:00:01.92
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'NDX_LIQUID_PRODUCT__ARTIST_ID
> ' (NON-UNIQUE) (Cost=4 Card=255332)
>
> Statistics
> ----------------------------------------------------------
> 344 consistent gets
> 345 physical reads
>
> ### 3 ########
> SQL> select /*+ INDEX (ndx_liquid_product__artist_id) */ count(*) from
> liquid_product;
>
> Elapsed: 00:00:01.30
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'NDX_LIQUID_PRODUCT__ARTIST_ID
> ' (NON-UNIQUE) (Cost=4 Card=255332)
>
> Statistics
> ----------------------------------------------------------
> 344 consistent gets
> 345 physical reads
>
>
> Rgds, Vadim Grepan
> --------------------------------
> Moscow, Russia
>
>
>
Received on Thu Jun 28 2001 - 14:36:52 CDT