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: David Fitzjarrell <oratune_at_aol.com>
Date: Thu, 09 Nov 2000 21:11:18 GMT
Message-ID: <8uf3te$g41$1@nnrp1.deja.com>

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.
Received on Thu Nov 09 2000 - 15:11:18 CST

Original text of this message

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