alter table … move and table stats

Howard Rogers left a comment on my last blog, showing an example of using alter table … move on a 10gR2 database on Linux. In his example, unlike mine, the table rebuild did not nullify the table’s statistics. I admit I was surprised myself when I ran my example yesterday so I was concerned that I’d messed up my test somewhere, despite being careful.

This morning I’ve tried the same test by creating a script containing all of the commands from the previous blog and ran it on two different databases, one 9.2.0.6 and one 10.2.0.1, both running on Solaris 8.

Here’s the 9.2 output

SQL> create user test identified by test
  2  default tablespace data
  3  temporary tablespace temp;

User created.

SQL> grant connect, resource to test;

Grant succeeded.

SQL> alter user test quota unlimited on data;

User altered.

SQL> connect test/test
Connected.
SQL> create table test (long_string varchar2(2000));

Table created.

SQL> begin
  2  	     for ctr in 1 .. 10000 loop
  3  		     insert into test values (to_char(ctr));
  4  	     end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select table_name, num_rows, chain_cnt from user_tables;

TABLE_NAME                                           NUM_ROWS    CHAIN_CNT                            
------------------------------ ---------- ----------                            
TEST                                10000                   0                            

SQL> update test set long_string = lpad(long_string, 500);

10000 rows updated.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select table_name, num_rows, chain_cnt from user_tables;

TABLE_NAME                       NUM_ROWS  CHAIN_CNT                            
------------------------------ ---------- ----------                            
TEST                                10000       9983                            

SQL> alter table test move initrans 6;

Table altered.

SQL> select table_name, num_rows, chain_cnt from user_tables;

TABLE_NAME                       NUM_ROWS  CHAIN_CNT                            
------------------------------ ---------- ----------                            
TEST                                                                            

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select table_name, num_rows, chain_cnt from user_tables;

TABLE_NAME                       NUM_ROWS  CHAIN_CNT                            
------------------------------ ---------- ----------                            
TEST                                10000          0                            

Here’s the 10.2 output

SYS @ CMDBD > create user test identified by test
  2  default tablespace tools
  3  temporary tablespace temp;

User created.

SYS @ CMDBD > grant connect, resource to test;

Grant succeeded.

SYS @ CMDBD > alter user test quota unlimited on tools;

User altered.

SYS @ CMDBD > connect test/test
Connected.
TEST @ CMDBD > create table test (long_string varchar2(2000));

Table created.

TEST @ CMDBD > begin
  2          for ctr in 1 .. 10000 loop
  3                  insert into test values (to_char(ctr));
  4          end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

TEST @ CMDBD > commit;

Commit complete.

TEST @ CMDBD > analyze table test compute statistics;

Table analyzed.

TEST @ CMDBD > select table_name, num_rows, chain_cnt from user_tables;

TABLE_NAME                       NUM_ROWS  CHAIN_CNT
------------------------------ ---------- ----------
TEST                                10000          0

TEST @ CMDBD > update test set long_string = lpad(long_string, 500);

10000 rows updated.

TEST @ CMDBD > commit;

Commit complete.

TEST @ CMDBD > analyze table test compute statistics;

Table analyzed.

TEST @ CMDBD > select table_name, num_rows, chain_cnt from user_tables;

TABLE_NAME                       NUM_ROWS  CHAIN_CNT
------------------------------ ---------- ----------
TEST                                10000       9999

TEST @ CMDBD > alter table test move initrans 6;

Table altered.

TEST @ CMDBD > select table_name, num_rows, chain_cnt from user_tables;

TABLE_NAME                       NUM_ROWS  CHAIN_CNT
------------------------------ ---------- ----------
TEST                                10000       9999

TEST @ CMDBD > analyze table test compute statistics;

Table analyzed.

TEST @ CMDBD > select table_name, num_rows, chain_cnt from user_tables;

TABLE_NAME                       NUM_ROWS  CHAIN_CNT
------------------------------ ---------- ----------
TEST                                10000          0