index not used after compute stats ?

From: Ted Chyn <tedchyn_at_yahoo.com>
Date: 8 Feb 2002 14:04:28 -0800
Message-ID: <44a19320.0202081404.16795c22@posting.google.com>

all:
1. hpux 11.0 oracle 8.1.6 32 bits
2. when I delete stats index get used (rule based come into play).

   after compute stat on the table index is no longer used even there are    more consistent get/block get and sort in the autotrace after compute. 3. my question

1. Is there a bug in optimizer in 8.1.6 ?
2. my compatibility in ini.ora is set at 8.1.6, would this cause problem.

thnx a lot
ted chyn     

SQL> set autotrace on
SQL> select * from sda_webxml.company_info where user_id='zeni2718';

USER_ID                  ISP_NAME                       ISP_ID
------------------------ ------------------------------ --------------------
CONTACT_PERSON                 PHONE_NUMB I PAYMENT_PLAN         REGISTRAT
------------------------------ ---------- - -------------------- ---------
PASSWORD                 S
------------------------ -
zeni2718                 zenith information systems     705
Jeff Sesar                     3108268634 B
                         Y

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'COMPANY_INFO'    2 1 INDEX (UNIQUE SCAN) OF 'XPKCOMPANYINFO' (UNIQUE) Statistics

---

          0  recursive calls
          0  db block gets
          3  consistent gets
          3  physical reads
          0  redo size
        568  bytes sent via SQL*Net to client
        306  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> analyze table sda_webxml.company_info compute statistics;

Table analyzed.

SQL> select * from sda_webxml.company_info where user_id='zeni2718';

USER_ID                  ISP_NAME                       ISP_ID
------------------------ ------------------------------ --------------------
CONTACT_PERSON                 PHONE_NUMB I PAYMENT_PLAN         REGISTRAT
------------------------------ ---------- - -------------------- ---------
PASSWORD                 S
------------------------ -
zeni2718                 zenith information systems     705
Jeff Sesar                     3108268634 B
                         Y

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=67)    1 0 TABLE ACCESS (FULL) OF 'COMPANY_INFO' (Cost=1 Card=1 Bytes

          =67)

Statistics

---

          0  recursive calls
          4  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        588  bytes sent via SQL*Net to client
        306  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Received on Fri Feb 08 2002 - 16:04:28 CST