| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: how to deal with this situation? truncate table + statistics
Syed,
In this case I suggest you analyze the table once (just before you truncate it) then don't re-analyze it again (unless your system changes and you have a substantially different volume or distribution of data in this table).
The only reason for re-analyzing a table is to encourage the optimizer to use a more efficient execution plan. It does not matter if the stats differ from reality, so long as you are getting the most efficient execution plan. In your case, re-analyzing the table after a truncate will give the optimizer information that will probably result in an inefficient execution plan.
If the optimiser "thinks" there are 100,000 rows when there are actually 10 rows, I doubt you will have performance problems.
If you want to analyze daily, then do it just before you truncate.
Scott Hutchinson
Interact Analysis Ltd.
Quoting The Human Fly <sjaffarhussain_at_gmail.com>:
> Hello List,
>
> Well, I have a little bit tricky or I can say confused situation. We
> have database for shares tradining. Beginning of every day, a couple
> of tables will be truncted and we analyze the schema.
> The situation is, when we truncate and analzye those tables,
> statitiscs are updated as rum_rows=0, and blocks =0. The entire day,
> about 100,000 records comes in each of those truncated tables. But,
> for the optimizer, the data dictionary statistics provides nothing,
> when there are records coming every second, we dont re-analyze again,
> i.e. old statistics available to the optimizer.
> How can we deal with this situation? Is it better to collect the
> statistics before table get truncated, so that statistics available
> for those tables to help otpimizer? If so, lets say, if our num_rows
> says 100,000 and truncated the table, when records start coming in a
> table, optimizer assumed that this table has 100,000 records, may be
> the empty table starts filling from 10 records and keep growing.?
> What is the best way to deal this?
>
> --
> Best Regards,
> Syed Jaffar Hussain
> OCP 8i & 9i DBA,
> Banque Saudi Fransi,
> Saudi Arabia
> ------------------------------------------------------------------------------
::This message sent using the free Web Mail service from http://TheName.co.uk
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 13 2005 - 04:56:02 CDT
 |
 |