CBO question. Urgent

[jlakhani]

Guys,
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?

thanks

[davey23uk]

if you dont touch the stats, the cbo can only go on what it has. it will think it has the same data as before

If you export and import the same stats then you will have the same thing - will think it is the same data.

if you d a major change in your data you must regenerate your stats

[slimdave]

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.

[abhaysk]

Originally posted by slimdave
number of empty blocks etc., and both of these will make a difference to the stats.

Dave :

Dbms_Stats always updates this col with zero if Null.. & oracle suggests to use Dbms_Stats instead of Analyze ( which will fill in the number of blocks that are empty ).. Do u think its important??

Abhay.

[slimdave]

Originally posted by abhaysk
Dave :

Dbms_Stats always updates this col with zero if Null.. & oracle suggests to use Dbms_Stats instead of Analyze ( which will fill in the number of blocks that are empty ).. Do u think its important??

Abhay.

I'm not sure i understand your question here.

[abhaysk]

Originally posted by slimdave
I'm not sure i understand your question here.

Well, my point was.. " is 'Empty Blocks' a really important fig? "

coz if u use DBMS_STATS package to gather Table Stats, the column Empty_Blocks will be zero.. So my Q "is it a really important stat value to be considered"

Abhay.

[slimdave]

Maybe not