Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!cyclone1.gnilink.net!gnilink.net!wn14feed!worldnet.att.net!attbi_s02.POSTED!53ab2750!not-for-mail
Reply-To: "Jim Kennedy" <kennedy-downwithspammersfamily@attbi.net>
From: "Jim Kennedy" <kennedy-downwithspammersfamily@attbi.net>
Newsgroups: comp.databases.oracle.server
References: <vL_Gc.576$Ny6.1485@mencken.net.nih.gov> <C20Hc.476$em4.117@newsfe3-gui.ntli.net> <kj%Gc.577$Ny6.1552@mencken.net.nih.gov> <9g0Hc.495$em4.332@newsfe3-gui.ntli.net> <3s%Gc.578$Ny6.1570@mencken.net.nih.gov> <jJ0Hc.558$em4.73@newsfe3-gui.ntli.net>
Subject: Re: Query not using indexes
Lines: 82
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
Message-ID: <%%%Gc.42296$IQ4.3398@attbi_s02>
NNTP-Posting-Host: 24.20.5.156
X-Complaints-To: abuse@comcast.net
X-Trace: attbi_s02 1089244283 24.20.5.156 (Wed, 07 Jul 2004 23:51:23 GMT)
NNTP-Posting-Date: Wed, 07 Jul 2004 23:51:23 GMT
Organization: Comcast Online
Date: Wed, 07 Jul 2004 23:51:23 GMT
Xref: core-easynews comp.databases.oracle.server:225181
X-Received-Date: Wed, 07 Jul 2004 16:50:32 MST (news.easynews.com)


"Dave" <david.sharples3@ntlXworld.com> wrote in message
news:jJ0Hc.558$em4.73@newsfe3-gui.ntli.net...
>
> "hastenthunder" <hastenthunder@hotmail.com> wrote in message
> news:3s%Gc.578$Ny6.1570@mencken.net.nih.gov...
> >
> > "Dave" <david.sharples3@ntlXworld.com> wrote in message
> > news:9g0Hc.495$em4.332@newsfe3-gui.ntli.net...
> > >
> > > "hastenthunder" <hastenthunder@hotmail.com> wrote in message
> > > news:kj%Gc.577$Ny6.1552@mencken.net.nih.gov...
> > > >
> > > > "Dave" <david.sharples3@ntlXworld.com> wrote in message
> > > > news:C20Hc.476$em4.117@newsfe3-gui.ntli.net...
> > > > >
> > > > > "hastenthunder" <hastenthunder@hotmail.com> wrote in message
> > > > > news:vL_Gc.576$Ny6.1485@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