STATISTIQUES : effet du truncate

Une question que je me pose souvent : le TRUNCATE invalide-t'il les statistiques d'une table ? Réponse : non.

Démonstration :
 

  

SQL> CREATE TABLE toto (a number);

Table created.

SQL> INSERT INTO toto VALUES (999);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';

    BLOCKS AVG_ROW_LEN
---------- -----------


SQL> exec dbms_stats.gather_table_stats(USER,'TOTO');

PL/SQL procedure successfully completed.

SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';

    BLOCKS AVG_ROW_LEN
---------- -----------
         5           4

SQL> TRUNCATE TABLE toto;

Table truncated.

SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';

    BLOCKS AVG_ROW_LEN

---------- -----------

         5           4

SQL> exec dbms_stats.gather_table_stats(USER,'TOTO');

PL/SQL procedure successfully completed.

SQL> SELECT blocks, avg_row_len FROM USER_TAB_STATISTICS WHERE TABLE_NAME='TOTO';

    BLOCKS AVG_ROW_LEN
---------- -----------
         0           0