| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: index not used after compute stats ?
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>...Received on Fri Feb 08 2002 - 16:54:26 CST
>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
 |
 |