Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Query problem

Re: SQL Query problem

From: <jdarrah_co_at_my-deja.com>
Date: Thu, 09 Nov 2000 19:54:28 GMT
Message-ID: <8uevdg$bp8$1@nnrp1.deja.com>

You may want to double check that there are statistics on that index. Last year I had a similar query that was doing the exact same thing it turned out that we had only analyzed the table. We spent the better part of a morning looking at everything we could thing of before someone decided to double check. If you give it the rule hint and it follows the correct nested loops path I'd be willing to bet there are no/bad stats on the index.

In article <8uesfe$94a$1_at_nnrp1.deja.com>,   kal121_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 09 2000 - 13:54:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US