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

From: <michael_bialik_at_my-deja.com>
Date: Tue, 05 Oct 1999 22:03:57 GMT
Message-ID: <7tdsk8$kkh$1@nnrp1.deja.com>

Hi.

1. Why index is not used ? Depends on statistics. Check num_rows field in dba/all/user_tables for your table. Check the number of distinct keys in index as well. If these numbers are low compared with your actual data - you got your answer.
2. Would compute statistics improve this situation? It may. If it does not help - try using histograms : ANALYZE TABLE <tab_name> COMPUTE STATISTICS FOR COLUMNS;

 HTH. Michael.

In article <7tdg55$bae$1_at_nnrp1.deja.com>,   tedchyn_at_yahoo.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 05 1999 - 17:03:57 CDT