Truncate Command and Table Statistics

Last week, we were discussing our Statistics Gathering strategy for our data-warehouse system. and one interesting question was raised: What happens to table Statistics when we truncate a table in database? Are Statistics get deleted with truncate command or are they maintained?

Personally I was hoping that Statistics of the table should be deleted with Truncate command after all Truncate is DDL command, but I had the doubt too and I never tested it before. In my views, it is always better to run and test the scenario than living on assumptions. So here is what I did and found:

1. Created my favorite table EMP

nimish@garg> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );
Table created.

2. Inserted 14 records in EMP Table

nimish@garg> insert into emp
  2  values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'),5000, null, 10);
1 row created.

nimish@garg> insert into emp
  2  values(7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
1 row created.

nimish@garg> insert into emp
  2  values(7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
1 row created.

nimish@garg> insert into emp
  2  values(7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
1 row created.

nimish@garg> insert into emp
  2  values(7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
1 row created.

nimish@garg> insert into emp
  2  values(7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20);
1 row created.

nimish@garg> insert into emp
  2  values(7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20);
1 row created.

nimish@garg> insert into emp
  2  values(7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30);
1 row created.

nimish@garg> insert into emp
  2  values(7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30);
1 row created.

nimish@garg> insert into emp
  2  values(7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
1 row created.

nimish@garg> insert into emp
  2  values(7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30);
1 row created.

nimish@garg> insert into emp
  2  values(7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20);
1 row created.

nimish@garg> insert into emp
  2  values(7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30);
1 row created.

nimish@garg> insert into emp
  2  values(7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10);
1 row created.

nimish@garg> commit;
Commit complete.

3. Checked Statistics for EMP in USER_TABLES , if Oracle has gathered them.

analytic_lat@bidatadv> select NUM_ROWS, SAMPLE_SIZE, to_char(LAST_ANALYZED,'dd-mon-yyyy hh24:mi:ss'), USER_STATS
  2  from user_tables where table_name = 'EMP';

  NUM_ROWS SAMPLE_SIZE TO_CHAR(LAST_ANALYZED,'DD- USE
---------- ----------- -------------------------- ---
                                                  NO

4. Statistics were not gathered by Oracle while Creating table and Inserting the records. So let's gather them.

nimish@garg> exec dbms_stats.gather_table_stats('NIMISH','EMP');
PL/SQL procedure successfully completed.

analytic_lat@bidatadv> select NUM_ROWS, SAMPLE_SIZE, to_char(LAST_ANALYZED,'dd-mon-yyyy hh24:mi:ss'), USER_STATS
  2  from user_tables where table_name = 'EMP';

  NUM_ROWS SAMPLE_SIZE TO_CHAR(LAST_ANALYZED,'DD- USE
---------- ----------- -------------------------- ---
        14          14 31-mar-2017 02:17:08       NO

5. Statistics looks good in USER_TABLES. Now let's try to TRUNCATE the table and see what happens to Statistics.

nimish@garg> TRUNCATE TABLE EMP;
Table truncated.

nimish@garg> select NUM_ROWS, SAMPLE_SIZE, to_char(LAST_ANALYZED,'dd-mon-yyyy hh24:mi:ss'), USER_STATS
  2  from user_tables where table_name = 'EMP';

  NUM_ROWS SAMPLE_SIZE TO_CHAR(LAST_ANALYZED,'DD- USE
---------- ----------- -------------------------- ---
        14          14 31-mar-2017 02:17:08       NO

6. Surprisingly Table Statistics were not updated/removed even after TRUNCATE command. I was excepting this behavior with DELETE (DML) but not with TRUNCATE (DDL). Let's gather the Statistics of EMP table one final time

nimish@garg> exec dbms_stats.gather_table_stats('NIMISH','EMP');
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.96
nimish@garg> select NUM_ROWS, SAMPLE_SIZE, to_char(LAST_ANALYZED,'dd-mon-yyyy hh24:mi:ss'), USER_STATS
  2  from user_tables where table_name = 'EMP';

  NUM_ROWS SAMPLE_SIZE TO_CHAR(LAST_ANALYZED,'DD- USE
---------- ----------- -------------------------- ---
         0           0 31-mar-2017 02:20:40       NO

Key Takeaways:
1. Oracle does not remove Table Statistics after TRUNCATE command
2. In starting, without gathering the Statistics, Table Statistics were NULL and now they are Zero (0).
3. Most Important One, always Run and Test your doubt, no matter how small. Don't live on assumptions :)

Related Posts:
- How to Get Execution Plan and Statistics of SQL Query
- Oracle: DBMS_STATS Gather Statistics of Schema, Tables, Indexes
- Oracle: DBMS_UTILITY.ANALYZE_SCHEMA
- Oracle: Analyze Table or Index