Sunday, March 23, 2014

Moving a Table Deletes its Statistics

Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:

SQL> create table object_list
  2  as select * from dba_objects
  3  /

Table created.

SQL>

When you create a table it has no statistics so the num_rows column is null:

SQL> select nvl(to_char(num_rows), 'NULL')
  2  as row_count
  3  from dba_tables
  4  where table_name = 'OBJECT_LIST'
  5  /

ROW_COUNT
----------------------------------------
NULL
                                                  
SQL>

When you calculate statistics, the num_rows column is updated:

SQL> analyze table object_list
  2  compute statistics
  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')
  2  as row_count
  3  from dba_tables
  4  where table_name = 'OBJECT_LIST'
  5  /

ROW_COUNT
----------------------------------------
7932

SQL>

Moving the table deletes the statistics so num_rows is null afterwards:

SQL> alter table object_list move
  2  /

Table altered.

SQL> select nvl(to_char(num_rows), 'NULL')
  2  as row_count
  3  from dba_tables
  4  where table_name = 'OBJECT_LIST'
  5  /

ROW_COUNT
---------------------------------------- NULL                                       

SQL>

To reinstate the statistics, simply analyze the table again:

SQL> analyze table object_list
  2  compute statistics
  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')
  2  as row_count
  3  from dba_tables
  4  where table_name = 'OBJECT_LIST'
  5  /

ROW_COUNT
----------------------------------------
7932

SQL>

No comments: