Truncate table - are the stats gone?

Dudes:

When you truncate a table are the performance tuning stats removed from the data dictionary?

My guess is not - they would remain and be inaccurate until analyze was run again?

Thanks - rev

Why don't you try it for yourself, it is trivial to test it. Just truncate the table and verify what happens with NUM_ROWS (or any other statistics related columns) in USER_TABLES for that table.

You'll find out that statistics remain unchanged.

What jmodic said... you only learn by trying this yourself and the test is TRIVIAL.


SQL> create table test(a number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select * from user_tables where table_name = 'TEST';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CLUSTER_NAME IOT_NAME PCT_FREE
------------------------------ ------------------------------ ----------
PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
---------- ---------- ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS
----------- ------------ ---------- --------------- --- - ---------- ----------
EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS
------------ ---------- ---------- ----------- -------------------------
NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL
------------------- ---------- ---------- ----- -------- ----------- ---------
PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE DURATION SKIP_COR MON
--- ------------ - - --- ------- -------- --- --- --------------- -------- ---
CLUSTER_OWNER DEPENDEN
------------------------------ --------
TEST USERS
10
1 255 1048576 1
2147483645 YES N 1 61
3 16265 0 6 0
0 1 1 N ENABLED 1 27-SEP-02
NO N N NO DEFAULT DISABLED NO NO DISABLED NO
DISABLED


SQL> truncate table test;

Table truncated.

SQL> select * from user_tables where table_name = 'TEST';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CLUSTER_NAME IOT_NAME PCT_FREE
------------------------------ ------------------------------ ----------
PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
---------- ---------- ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS
----------- ------------ ---------- --------------- --- - ---------- ----------
EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS
------------ ---------- ---------- ----------- -------------------------
NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL
------------------- ---------- ---------- ----- -------- ----------- ---------
PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE DURATION SKIP_COR MON
--- ------------ - - --- ------- -------- --- --- --------------- -------- ---
CLUSTER_OWNER DEPENDEN
------------------------------ --------
TEST USERS
10
1 255 1048576 1
2147483645 YES N 1 61
3 16265 0 6 0
0 1 1 N ENABLED 1 27-SEP-02
NO N N NO DEFAULT DISABLED NO NO DISABLED NO
DISABLED


And there you go...