This content has been marked as final. Show 6 replies
homerj wrote:post complete results from SQL below
This is probably a stupid question, but I've searched high and low and haven't found a suitable answer to the following: When do stats get dropped?
I've read that table and index stats get dropped whenever you truncate a table, but the (minimal) testing I've done doesn't bear this out. By "testing", I mean I've tried truncating a table then exporting and viewing the table stats via DBMS_STATS.EXPORT_TABLE_STATS (well I did say 'minimal')... The table stats don't appear to have been dropped following truncation.
Do table / index stats get dropped when you truncate / drop / delete all records from a table? Or do they not get dropped at all?
SELECT * FROM V$VERSION;
How do I ask a question on the forums?
SQL and PL/SQL FAQ
Ah sorry... duly noted.
Version output is below:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected.
In my opinion : Table/ Index stats get collect into oracle data dictionary views (i.e. DBA_ or ALL_ or USER_). if your database's GATHER_STATS_JOB is updating statistics automatically or you have any other scheduled DBMS scheduled job (updating statistics manually) all the changes will effectively populated into the database from immedate effect and table/index stats related with truncated/dropped/deleted records/tables will get update.
otherwise you can drop table/index stats related to dropped/deleted/truncated tables manually using DELETE_TABLE_STATS Procedure
Statistics are stored into the Oracle Data Dictionary, in tables owned by SYS. Views are created on these tables to retrieve data more easily.
These views are prefixed with DBA_ or ALL_ or USER_. For ease of reading, we will use DBA_% views, but ALL_% views or USER_% views could be used as well.
Finally Its : No Table/index stats do not drop when you truncate/drop/delete tables unless it is not happening through GATHER_STATS_JOB automatically or we do it manually or we have any manual schedulded DBMS scheduled jobs.
table stats are deleted when you call dbms_stats.delete_table_stats or when you drop the table. Why would you think that Oracle would secretly delete table stats behind your back without you asking it to? If you read it somewhere then you should post a link to that statement -- we cannot judge it without the context, but these days there are lots of stupid things posted on the internet, this could be one of them.
You should trust what you see (your own tests) over what you hear, especially from unknown people on the internet.