Re: SQL Query problem

From: <kal121_at_my-deja.com>
Date: Thu, 09 Nov 2000 22:36:09 GMT
Message-ID: <8uf8sp$kn4$1@nnrp1.deja.com>

Boy, do I feel like a moron :-)

In article <8uf3te$g41$1_at_nnrp1.deja.com>,   David Fitzjarrell <oratune_at_aol.com> wrote:
> In article <8uf2kl$esu$1_at_nnrp1.deja.com>,
> kal121_at_my-deja.com wrote:
> > 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.
> >

>

> Try this version of your query:

>

> select site_url from tab1 where site_id = '384579';

>

> This should pick up your index and forego a full table scan.

>

> --
> David Fitzjarrell
> Oracle Certified DBA

>

> 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 - 16:36:09 CST