alter table … move and table stats (part II)

Following up on another comment from Howard, I took the initrans change off the alter table … move so that it was the most basic variation and then ran it on 8i, 9i and 10g. I’ve trimmed lots of the output this time, but I have the log files if anyone wants a copy.

It looks like 10g that behaves differently.

8.1.7.4

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; 

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 

9.2.0.7

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       9999 

SQL> alter table test move;

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 

10.2.0.1

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;

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