Xref: alice comp.databases.oracle.server:68565
Path: alice!news-feed.fnsi.net!news.idt.net!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsgate.cistron.nl!het.net!bullseye.news.demon.net!demon!news.demon.nl!demon!sybrandb.demon.nl!not-for-mail
From: "Sybrand Bakker" <postmaster@sybrandb.demon.nl>
Newsgroups: comp.databases.oracle.server
Subject: Re: Optimizer question(index not used on compute stat) ?
Date: Tue, 5 Oct 1999 21:33:39 +0200
Message-ID: <939152111.23419.0.pluto.d4ee154e@news.demon.nl>
References: <7tdg55$bae$1@nnrp1.deja.com>
Reply-To: "Sybrand Bakker" <postmaster@sybrandb.nospam.demon.nl>
X-Trace: news.demon.nl 939152111 pluto:23419 NO-IDENT sybrandb.demon.nl:212.238.21.78
X-Complaints-To: abuse@demon.net
X-Newsreader: Microsoft Outlook Express 5.00.2014.211
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211
Lines: 94

This depends on how the data is distributed.
Too often the first inital 10 percent is not representative of the rest.
I never use estimate, I always use compute.
The index would not be used if all the two leading columns have a constant
value for all records in the table
(I have that situation). I assume your data is distributed less uniformly.
You could force histograms to be created.
Also: how big is that table? What is your db_file_multiblock read.
Apparently the optimizer calculates less IO for a full table scan than for
an index scan.
Probably your num_rows of that table is not correct because you have been
using estimate.
At least that happened in earlier versions, it simply extrapolated rows per
block times blocks  to num_rows.

Hth,


--
Sybrand Bakker, Oracle DBA

<tedchyn@yahoo.com> wrote in message news:7tdg55$bae$1@nnrp1.deja.com...
> sir, I have a table with 300000 rows and index is defined on
> mid,oid,odate as primary index and table is analyzed use estimating
> statistics. oracle 8.0.4 sun solaris.
> question,
> 1. why index is not used ?
> 2. would compute statistics improve this situation?
>
>
> Thanks in advance
> Ted Chyn(tedchyn@yahoo.com)
>
> ## TABLE and Index layout
> desc batch_transactions
>  Name                            Null?    Type
>  ------------------------------- -------- ----
>  MID                             NOT NULL NUMBER(38)
>  OID                             NOT NULL VARCHAR2(100)
>  ODATE                           NOT NULL NUMBER(38)
>  STATUS                          NOT NULL CHAR(1)
>  BATCH_TIME                               NUMBER(38)
>
> SQL> select column_name,column_position from dba_ind_columns where
> index_name='BATCH_TRANSACTIONS_PK';
>
> COLUMN_NAME
> ------------------------------------------------------------------------
> --------
> COLUMN_POSITION
> ---------------
> MID
>               1
>
> OID
>               2
>
> ODATE
>               3
>
> *************************************
>   1  SELECT oid, odate FROM batch_transactions WHERE mid=305625 AND
>   2* batch_time=939138489
> SQL> /
>
> OID
> ------------------------------------------------------------------------
> --------
>      ODATE
> ----------
>
>
> 11 rows selected.
> ### NO INDEX is used here even there are only 11 rows are selected out
> ### of 300000 rows.
>
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=113 Card=1 Bytes=44)
>    1    0   TABLE ACCESS (FULL) OF 'BATCH_TRANSACTIONS' (Cost=113 Card
>           =1 Bytes=44)
>
> ###USE hint: rule or index will force query to use index
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=HINT: RULE
>    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BATCH_TRANSACTIONS'
>    2    1     INDEX (RANGE SCAN) OF 'BATCH_TRANSACTIONS_PK' (UNIQUE)
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.