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

7 comments:

  1. Yes Nimish Garg Oracle does not remove the statistics after Truncate... Only DDL that gathers Table staticstics is CTAS and that too in 12c.. Normally, this thing needs to be taken care with the tables which normally holds temporary/staging data like in Ware house systems and some time in ERP's like peoplesoft. There are couple of things that you can do to make the performance impact minimal ..1: If total count of rows remain almost same after truncate and reload, you can gather the stats after the load and then lock the statistics.. 2: Delete the statistics and do not gather and let dynamic sampling take care of this..

    ReplyDelete
    Replies
    1. Yeah! I personally like to lock the stats and gather stats for them once a month for such tables.

      Delete
  2. If stats were updated automatically after every DDL,DML then there would have been no requirement for AUTO STATS gathering and DBMS_STATS package. Its quiet logical.

    ReplyDelete
  3. sir pls help me to resolve ora-01115 ora-01110 when deleting or selecting record from the table. ... plz hlp

    ReplyDelete
    Replies
    1. looks like you have corrupted datafile, or any I/O issue. Suggest you to contact Oracle Support

      Delete
    2. Thank you so much for your reply sir

      Delete
  4. very good and interesting post,your website gives us best information. This is just the kind of information that i had been looking for.

    ReplyDelete