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

TRUNCATE : Appel de Procédure Stockée via db link

Alors que le delete, lui, fonctionne sans problème, la commande truncate en tant que DDL ne passe pas au travers d'un db link.

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         1

Sur la base source :
 
SQL> DELETE FROM toto@versqua;

1 row deleted.

SQL> ROLLBACK;

Rollback complete.

SQL> TRUNCATE TABLE toto@versqua;
TRUNCATE TABLE toto@versqua
                    *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

Il existe une solution qui consiste à disposer une procédure stockée sur la base cible qui effectue le truncate. Cette procédure étant appelée depuis la base source via db link.
 

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         1
 

SQL> CREATE PROCEDURE p_u_truncate (p_table IN VARCHAR2)
  2     IS
  3     BEGIN
  4        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table;
  5     END;
  6  /

Procedure created.

Sur la base source :

SQL> EXEC p_u_truncate@versqua('TOTO');

PL/SQL procedure successfully completed.

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         0

Explication : la procédure stockée sur la base cible est appelée depuis la base source et s'exécute sur la base cible.