Re: Analyze table problem

That depends. When doing an estimate statistics only 1064 rows are looked analyzed. If these rows are skewed then the statistics may or may not be very good. If possible do a "Compute" statistics. If the table is too large try estimating 20%. You'll have to experiment with the numbers during an estimate and find out what is best for you.

Also if any table in a query has statistics make sure they all do. If a table in a query has statistics the Cost based optimizer will be used. If no statistics are found the Rule based optimizer will be used.

If you can't get the query to use the proper plan with statistics you might have to add a hint /*+ rule */ or maybe a index hint.

HTH
Steve...

"Tobie Berthon" <tberthon_at_adapsys.ca> wrote in message news:e9946f43.0208271058.3781065e_at_posting.google.com...
> I ran an "analyze table .. estimate statistics" command against one
> table to find the average row length of the data in the table. Later
> that day, I received complaints that a number of our batch jobs had
> slowed to a crawl (2 min job running for an hour). I knew that it
> wasn't the programs at fault and we were able to determine that the
> joins to that particular table weren't working. It was like the
> primary key had become invalid. Once I deleted the statistics the jobs
> all ran fine again. What happened here? By the way, I'm running Oracle
> 8.0.5 on AIX 4.3.3. Thanks
Received on Tue Aug 27 2002 - 14:59:28 CDT