Re: index not used after compute stats ?

jonathan,

1. this table has 65 blocks 65/16=4 db block gets(shown).
2. compute also show memory sort, 11 consistent gets why it is less expensive than index lookup.

thnx again
ted

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1013208853.1231.0.nnrp-07.9e984b29_at_news.demon.co.uk>...
> The actual number of CR and CUR gets
> is totally irrelevant.
>
> Your table appears to be about 11 blocks long,
> plus one for the segment header - if you have
> db_file_multiblock_read_count = 16, then this
> would explain the cost of 1 for scanning the
> table. To use the index, Oracle will estimate
> one block for the index (root only), plus one
> for accessing the correct block of the table.
>
> Total cost of using index = 2
> Total cost of table scan = 1
>
> Tablescan is the correct access path.
>
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Now running 3-day intensive seminars
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
>
> Ted Chyn wrote in message
> <44a19320.0202081404.16795c22_at_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
> > a. Is there a bug in optimizer in 8.1.6 ?
> > b. 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 - 23:20:58 CST