Re: query did a full table scan even with an index

for the session in which you are running the query, use alter session to set the optimizer mode to COST for that session and see what happens.

once you acieve the desired execution plan for the query, use plan stability to ensure that plan is always used for the query regardless of the instance environment.

"Nhuan_at_Lieu_NoSpam.org" <Nhuan_at_Lieu.org> wrote in message news:8snltv$k3b$1_at_nnrp1.deja.com...
> 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 Sat Oct 21 2000 - 08:50:43 CDT