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: Query not using indexes

Re: Query not using indexes

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Fri, 09 Jul 2004 01:28:31 GMT
Message-ID: <3xmHc.45809$%_6.29163@attbi_s01>

"hastenthunder" <hastenthunder_at_hotmail.com> wrote in message news:LkbHc.580$Ny6.1576_at_mencken.net.nih.gov...
>
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message
> news:%%%Gc.42296$IQ4.3398_at_attbi_s02...
> >
> > "Dave" <david.sharples3_at_ntlXworld.com> wrote in message
> > news:jJ0Hc.558$em4.73_at_newsfe3-gui.ntli.net...
> > >
> > > "hastenthunder" <hastenthunder_at_hotmail.com> wrote in message
> > > news:3s%Gc.578$Ny6.1570_at_mencken.net.nih.gov...
> > > >
> > > > "Dave" <david.sharples3_at_ntlXworld.com> wrote in message
> > > > news:9g0Hc.495$em4.332_at_newsfe3-gui.ntli.net...
> > > > >
> > > > > "hastenthunder" <hastenthunder_at_hotmail.com> wrote in message
> > > > > news:kj%Gc.577$Ny6.1552_at_mencken.net.nih.gov...
> > > > > >
> > > > > > "Dave" <david.sharples3_at_ntlXworld.com> wrote in message
> > > > > > news:C20Hc.476$em4.117_at_newsfe3-gui.ntli.net...
> > > > > > >
> > > > > > > "hastenthunder" <hastenthunder_at_hotmail.com> wrote in message
> > > > > > > news:vL_Gc.576$Ny6.1485_at_mencken.net.nih.gov...
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > A query of mine in Oracle 9i R2 chooses to perform a full
> table
> > > scan
> > > > > of
> > > > > > 1
> > > > > > > > million row instead of using the index.
> > > > > > > > I'm using CBO and just computed the statistics for the
table.
> > > > > > > >
> > > > > > > > Can someone help?
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > > exec
> > > > >
> dbms_stats.gather_table_stats('SCHEMA_NAME','TABLE_NAME,casacde=true)
> > > > > > >
> > > > > > > are you sure a fts is a bad thing -
> > > > > > >
> > > > > > >
> > > > > >
> > > > > > Done that. Computed fresh statistics before sending the issue
out
> > via
> > > > > > analyze table command (its old, I know).
> > > > > > Just did it again using dbms_stats, still does not work...
> > > > > > Strange.., the query certainly access a substantial number of
> > rows....
> > > > > >
> > > > > >
> > > > >
> > > > > if you are getting most of the rows back, fts is good
> > > > >
> > > > > see
> > > > >
> > > >
> > >
> >
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4433887271030
> > > > >
> > > > >
> > > >
> > > > Thanks Dave, you are right.
> > > > However, I'm only getting a single row back (out of 1 million).
> > > >
> > > > SELECT fieldA
> > > > FROM tableA
> > > > WHERE fieldB = 'xxx'
> > > >
> > > > And fieldB is indexed.
> > > > Seems like a very simple query, with fresh statistis.
> > > > That's why this is puzzling me so much....
> > > >
> > > >
> > >
> > > try hinting thr query, make sure your index is valid and ok
> > >
> > > a well formed valid hint will be taken
> > >
> > >
> > What data type is fieldB? Is it a string? Is the 'xxx' the actual
value
> > you are searching on or is it something like '%xxx'?
> > jim
> >
> >
> fieldB is a string and 'xxx' is the actual value I'm searching for.
> I guess I could use a hint, but I think Oracle should be able to choose
the
> correct execution plan for this simple query
>
>

My guess is that 'xxx' has low cardinality and so it is cheaper to scan than read the index and the table. What does TKPROF turn up? Jim Received on Thu Jul 08 2004 - 20:28:31 CDT

Original text of this message

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