query did a full table scan even with an index

Hello, I have an interesting performance tuning issue in Oracle8i and am wonder if someone can provide some insights.

Issue: A Select query did a full table scan (expecting it using the index scan) even with an index on the simple WHERE clause.

System: NT4 & Oracle8i Enterprise Edition Release 8.1.6.0.0

Select Statement:
SELECT GL_COMPANY_NBR,... + ABOUT 30 COLUMNS FROM 1_MILLION_REC_TABLE
WHERE GL_COMPANY_NBR = '415A'; Query count result: 28 rows selected.

What have been done before the select statement: 1. The table has 8 indexes, one of them was created using:

   CREATE INDEX index_name ON 1_MILLION_REC_TABLE(GL_COMPANY_NBR) NOLOGGING COMPUTE STATISTICS; 2. ANALYZE TABLE 1_MILLION_REC_TABLE COMPUTE STATISTICS; DATA distribution on the 1_MILLION_REC_TABLE table: GL_COMPANY_NBR COUNT(GL_COMPANY_NBR)

---------------- ---------------------
TEST                               366
407G                                 1
415A                                28
415G                            981796

Data from SET TIMING ON & EXPLAIN PLAN:
Elapsed: 00:01:119.20
Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8514 Card=327397 Bytes=127357433)

   1 0 TABLE ACCESS* (FULL) OF '1_MILLION_REC_TABLE' (Cost=8514 :Q178000 Card=327397 Bytes=127357433)

Statistics

        787  recursive calls
         69  db block gets
      56224  consistent gets
      56099  physical reads
        684  redo size
      13504  bytes sent via SQL*Net to client
        696  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
         28  rows processed

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 19 2000 - 15:35:11 CDT