Path: news.easynews.com!easynews!hub1.nntpserver.com!headwall.stanford.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: kachkar@tablimited.com.au (achkar) Newsgroups: comp.databases.oracle.server Subject: Re: analyze index and cost Date: 3 Mar 2002 16:19:38 -0800 Organization: http://groups.google.com/ Lines: 40 Message-ID: References: <3C7FAAE9.C3F26803@ci.seattle.wa.us> NNTP-Posting-Host: 203.2.125.3 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1015201178 21209 127.0.0.1 (4 Mar 2002 00:19:38 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 4 Mar 2002 00:19:38 GMT Xref: easynews comp.databases.oracle.server:137323 X-Received-Date: Sun, 03 Mar 2002 17:22:10 MST (news.easynews.com) Hi , The optimizer does a full table scan when I compute statistics , but if there is no statistics ,the optimiser uses that index , now this is my point Why the optimizer does that , I though it would be faster when I compute statistics but it is not , Regards, Achkar damorgan wrote in message news:<3C7FAAE9.C3F26803@ci.seattle.wa.us>... > Excellent advise. If that doesn't solve it post the query, the explain plan, > a list of indexes on the table(s), and the number of rows. > > Are you using any hints? > > Daniel Morgan > > > > santosh sharma wrote: > > > did you compare the explain plan before and after compute stat ? It will > > point you why this is happening. > > > > HTh > > santysharma > > > > "achkar" wrote in message > > news:acb78eab.0202281707.34303a16@posting.google.com... > > > Hi All , > > > > > > I have a big query , it takes 70 min to run , cost is 94819 , when I > > > create and index ( XXX ) on one of the tables , the cost drops down to > > > 53770 and takes 30 min to run , but when I do this : analyze index XXX > > > compute statistics , the cost goes up to 94819 again , I dont > > > understand why? > > > > > > thanks in advance