"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
Received on Thu Jul 08 2004 - 07:45:40 CDT
