Is the site_id declared as a varchar2 or char
by any chance - if so then Oracle will coerce
from character to numeric, thus disabling the
index.
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Practical Oracle 8i: Building Efficient Databases
Publishers: Addison Wesley Longman
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
kal121_at_my-deja.com wrote in message <8uesfe$94a$1_at_nnrp1.deja.com>...
>Can anyone please tell me why the CBO is choosing to do a full table
>scan on this query?
>
>1. There is a PRIMARY KEY on site_id
>2. The table and primary key are both recently analyzed
>3. Optimizer is CBO
>4. Every single site_id is UNIQUE
>5. 1,420,000 rows in the table
>
>I tried forcing the using of the primary key using a hint. Well, it
>used the primary key, but it did a FULL SCAN on the index, not a UNIQUE
>SCAN. This took even longer than the full table scan (9 seconds)!
>
>(I am baffled, since I have a UNIQUE key on another column in this same
>table and the CBO is doing UNIQUE SCANS on that index. That query
>returns in a fraction of a second.)
>
>
>SQL> select site_url from tab1 where site_id = 384579;
>
>SITE_URL
>------------------------------------------------------------------------
>--------------
>http://www.usernm384579.com
>
> real: 8812
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10412 Card=14200 Byt
> es=2385600)
>
> 1 0 TABLE ACCESS (FULL) OF 'TAB1' (Cost=10412 Card=14200
> Bytes=2385600)
>
>
>
>
>
>Statistics
>----------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 68601 consistent gets
> 68529 physical reads
> 0 redo size
> 619 bytes sent via SQL*Net to client
> 497 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Nov 09 2000 - 14:20:32 CST
