nonefficient count(*)

From: Vadim Grepan <kezal_at_mail.ru>
Date: Thu, 28 Jun 2001 18:55:00 +0400
Message-ID: <3B3B4544.89E4C660@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 - 09:55:00 CDT