Path: news.easynews.com!easynews!hub1.nntpserver.com!nntp-relay.ihug.net!ihug.co.nz!logbridge.uoregon.edu!news-west.eli.net!not-for-mail Message-ID: <3C82C093.49EFE0D3@ci.seattle.wa.us> From: damorgan X-Mailer: Mozilla 4.79 [en] (Win95; U) X-Accept-Language: en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: analyze index and cost References: <3C7FAAE9.C3F26803@ci.seattle.wa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 53 Date: Mon, 04 Mar 2002 00:32:13 GMT NNTP-Posting-Host: 156.74.250.7 X-Complaints-To: yvonne.tracy@ci.seattle.wa.us X-Trace: news-west.eli.net 1015201933 156.74.250.7 (Sun, 03 Mar 2002 17:32:13 MST) NNTP-Posting-Date: Sun, 03 Mar 2002 17:32:13 MST Organization: City of Seattle NewsReader Service Xref: easynews comp.databases.oracle.server:137326 X-Received-Date: Sun, 03 Mar 2002 17:30:09 MST (news.easynews.com) Depends on many factors. It may well be that due to the data in the table(s), the cardinality, and the number of rows you are retrieving, it is faster for Oracle to ignore your indexes. Though that may well say something about the value of the indexes you have created. You thought it would be faster with indexes ... and I though you'd post the information required to actually give you a useful answer. Apparently we are both incorrect. Daniel Morgan achkar wrote: > 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