It is always a good idea to explicitly delete statistics before computing
(or estimating) it again.
Some versions of oracle did not remove old statistics, when new one not
successful (ran out of temp space etc).
Yes, if the tables are not that huge (tens of millions of rows), and compute
can be performed in an acceptable time frame, using it instead of estimate
is always a good idea.
BTW, there is no table or view that tells 'validity' of the statistics. As
the other reply indicated, the only thing you have is the last_analyzed date
in *_tables views. You should analyze table/index when a considerable amount
(10-15%) of the data is inserted or deleted. This figure is based on my
observations and experience with my databases. So your mileage may vary.
But, that's what I use.

HTH,

Regards,

- Kirti Deshpande
Verizon Information Services
http://www.superpages.com

> -----Original Message-----
> From: David Christopher [SMTP:[Email Address Removed] Sent: Thursday, June 28, 2001 5:01 PM
> To: LazyDBA.com Discussion
> Subject: Re: SQL Tuning
>
> I deleted the statistics on the table and then did a compute statistics
> again and now it uses the index. Now I have another question. Is there any
> table that tells the validity of the statistics. Do we have to delete the
> statistics before computing again or how often compute?
>
> ----- Original Message -----
> From: "Straub, Dan" <Dan.[Email Address Removed] To: "'David Christopher'" <[Email Address Removed] Sent: Thursday, June 28, 2001 5:25 PM
> Subject: RE: SQL Tuning
>
>
> > You might try an index on 'recd_dte' and/or 'status'.
> > Seems to me that finding the max of a column would require an full table
> > scan.
> > Just a thought.
> >
> > ~Dan.
> >
> > -----Original Message-----
> > From: David Christopher [mailto:[Email Address Removed] > Sent: Thursday, June 28, 2001 2:15 PM
> > To: LazyDBA.com Discussion
> > Subject: SQL Tuning
> >
> >
> > All,
> >
> > I am trying to tune the following simple select statement but in vain.
> > Please help me.
> >
> > select max(recd_dte), max(decode(status, 'A', 1, 0))
> > from addr
> > where empl_id = 1234
> >
> > The above statement does a full table scan even though there is a
> foreign
> > key non-unique index on the column empl_id. I also anayzed the table and
> > index but of no use. I also used the hint INDEX( ) but still doing full
> > table scan. There are about a million rows and so take a long time to
> run.
> > Any suggestions please?
> >
> > Thanks in Advance.
> > Dave
> >
> > --------
> > Oracle documentation is here:
> > http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> > To unsubscribe: send a blank email to oracledba-[Email Address Removed] > To subscribe: send a blank email to oracledba-[Email Address Removed] > Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> > Tell yer mates about http://www.farAwayJobs.com
> > By using this list you agree to these
> > terms:http://www.lazydba.com/legal.html
> >
>
> --------
> Oracle documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to oracledba-[Email Address Removed] To subscribe: send a blank email to oracledba-[Email Address Removed] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
> Tell yer mates about http://www.farAwayJobs.com
> By using this list you agree to these
> terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page