analye tables after truncate
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: analye tables after truncate

  1. #1
    Join Date
    Mar 2006
    Posts
    176

    analye tables after truncate

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    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?
    this space intentionally left blank

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  5. #5
    Join Date
    Mar 2006
    Posts
    176
    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???

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    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?
    this space intentionally left blank

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    because you have no intention of reloading the table to the same loads?

  8. #8
    Join Date
    Mar 2006
    Posts
    176
    thanks so much for both of you guys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width