Re: SQL Query problem

That's the thing! I analyzed the index this morning. Of this I am 100% sure.

INDEX_NAME                     LAST_ANAL
------------------------------ ---------
PK_TAB1                        09-NOV-00

(don't you love that abbreviation?)

In response to the previous poster, yes this column is a varchar2(20).

However, the other column (the one with the UNIQUE key) is also a varchar2(1024). And that one performs fine. That one does a UNIQUE SCAN of the unique key.

I also tried converting the primary key on site_id to a unique key, but I am having the same problem with full scanning.

Does this make any sense?

In article <8uevdg$bp8$1_at_nnrp1.deja.com>,   jdarrah_co_at_my-deja.com wrote:
> 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.
>

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