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

From: Michael Bialik <michael_bialik_at_my-deja.com>
Date: Sat, 21 Oct 2000 06:33:15 GMT
Message-ID: <8srdbb$jd1$1@nnrp1.deja.com>

Hi.

What are your INIT.ORA params: db_block_size multiblock_io_count. If you increased your db_block_size when moving to 8i then try decreasing the multiblock_io_count parameter, because it affects CBO ( large value make CBO "think" that FULL tablse scan is cheap ).
Try setting following parameters: optimizer_index_cost_adj = 20 optimizer_index_caching(or cache) = 80
If you are going to use bind variables CBO wil not use histograms and it will assume that each value has .25M rows. Try using HINT : SELECT /*+ INDEX ( 1_MILLION_REC_TABLE index_name ) */ ... FROM 1_MILLION_REC_TABLE WHERE GL_COMPANY_NBR = '415A'; Of course there is no reason to use such hint when GL_COMPANY_NBR = '415G'.

HTH. Michael

In article <8snltv$k3b$1_at_nnrp1.deja.com>, Nhuan_at_Lieu_NoSpam.org <Nhuan_at_Lieu.org> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 21 2000 - 01:33:15 CDT