analye tables after truncate

[matthew00]

Hi all,

we already run dbms_stats on all of our tables, the developer clean up data using TRUNCATE. the question is "do you have to update stats again???"

I don't thiink we have but I would like to be sure and I would like to know why.

thanks

[davey23uk]

yes it makes sense to tell the optimizer there are now no rows in there and a full table scan would be better than an index scan maybe.

there is no need to if you are going to reload it with the same data afterwards or not query it

[gandolf989]

Why don't you find the answer on your own. Create a table, load it with data, do a analyze compute on the table, check for stats, truncate the table and recheck for stats?

[davey23uk]

I think you missed the point? if you truncate the table the stats will remain

i think his question is whether we need to from maintenance perspective - from which the answer should be yes as the stats could make a difference if you still query the table

Unless the question was whether a truncate will clear down the stats to which the answer is no

[matthew00]

Grandof989,

thanks for answering my question, I have tested it out myself before I raised the question in here. here is what I wasn't sure: neither TRUNCATE or DELETE won't change the STATS of the table; however, I am not sure how Oracle will handle HWM on TRUNCATE versus DELETE.

I know DELETE won't reduce the HWM unless rebuild the table, and TRUNCATE won't have this problem. I am not sure how the optimizer will handle in these two scenarios so that I raised the question.

any input???

[gandolf989]

If you truncate a table, what difference does it make if there are stats? You will eventually load data and want to generate stats again. You could easily delete the stats on the table, but why bother?

[davey23uk]

because you have no intention of reloading the table to the same loads?

[matthew00]

thanks so much for both of you guys