Re: Optimizer question(index not used on compute stat) ?

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_at_yahoo.com> wrote in message news:7tdg55$bae$1_at_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_at_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.
Received on Tue Oct 05 1999 - 14:33:39 CDT