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

Give it a hint to use the index, and see what the CBO thinks the cost will be. This may give you a clue.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

tedchyn_at_yahoo.com wrote in message <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:32:46 CDT