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

From: <tedchyn_at_yahoo.com>
Date: Tue, 05 Oct 1999 18:31:01 GMT
Message-ID: <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_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 - 13:31:01 CDT