On our datawarehouse we have this montly load process. I just inherited this system so I am not a dw dba.
For Oracle 817
Question1. You have collected stats (estimate with 30% sample) on 400
tables. Lets say I have a 1million row table and I truncate the whole table and reload the same data back in. What does CBO think of the stats? Is the stats old and I need to reanalyze it or would it be current?
Question2. Lets say I just analyzed Table A which is 1 million. Exported the stats then truncated the table, reloaded the table with same data then reimported the stats back in. what does CBO think?
I would recalc the stats -- it's only in very particular circumstances that you could expect to have exactly the same physical order of rows, number of empty blocks etc., and both of these will make a difference to the stats.