今天在itpub看到一个问题 说是使用dbms_stats.import_table_stats并不会导入统计信息 然后做了一个实验如下 SQL> create table test as select * from dba_objects; Table created. SQL> create index idx_test on test(object_id); Index created. SQL> begin dbms_stats.gather_table_stats(ownname          => 'SCOTT', tabname          => 'TEST', estimate_percent => 100, degree           => 2, cascade          => true); end;  2    3    4    5    6    7 8  / PL/SQL procedure successfully completed. SQL> create table test_temp as select * from test; Table created. SQL> create index idx_test_temp on test_temp(object_id); Index created. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, sample_size from user_tables where table_name in ('TEST', 'TEST_TEMP');  2    3    4    5    6    7    8    9 TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE ------------------------------ ---------- ---------- ------------ ---------- AVG_ROW_LEN SAMPLE_SIZE ----------- ----------- TEST                                    75253        1098                0           0 97          75253 TEST_TEMP SQL> begin -- dbms_stats.drop_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); dbms_stats.create_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); dbms_stats.export_table_stats(ownname=>'scott',tabname=>'test',stattab=>'stat_test_temp',cascade => true); dbms_stats.delete_table_stats(ownname=>'scott',tabname=>'test_temp'); dbms_stats.import_table_stats(ownname=>'scott',tabname=>'test_temp',stattab=>'stat_test_temp'); end;  2    3    4    5    6    7 8  / PL/SQL procedure successfully completed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, sample_size from user_tables where table_name in ('TEST', 'TEST_TEMP');  2    3    4    5    6    7    8    9 TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE ------------------------------ ---------- ---------- ------------ ---------- AVG_ROW_LEN SAMPLE_SIZE ----------- ----------- TEST                                    75253        1098                0           0 97          75253 TEST_TEMP 以上是你的实验 再往下看 SQL> begin dbms_stats.drop_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); dbms_stats.create_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); dbms_stats.export_table_stats(ownname=>'scott',tabname=>'test',stattab=>'stat_test_temp',cascade => true); dbms_stats.delete_table_stats(ownname=>'scott',tabname=>'test'); --  dbms_stats.import_table_stats(ownname=>'scott',tabname=>'test',stattab=>'stat_test_temp'); end;  2    3    4    5    6    7 8  / PL/SQL procedure successfully completed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, sample_size from user_tables where table_name in ('TEST', 'TEST_TEMP');  2    3    4    5    6    7    8    9 TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE ------------------------------ ---------- ---------- ------------ ---------- AVG_ROW_LEN SAMPLE_SIZE ----------- ----------- TEST TEST_TEMP SQL> begin -- dbms_stats.drop_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); --  dbms_stats.create_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); --dbms_stats.export_table_stats(ownname=>'scott',tabname=>'test',stattab=>'stat_test_temp',cascade => true); --  dbms_stats.delete_table_stats(ownname=>'scott',tabname=>'test'); dbms_stats.import_table_stats(ownname=>'scott',tabname=>'test',stattab=>'stat_test_temp'); end;  2    3    4    5    6    7 8  / PL/SQL procedure successfully completed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, sample_size from user_tables where table_name in ('TEST', 'TEST_TEMP');  2    3    4    5    6    7    8    9 TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE ------------------------------ ---------- ---------- ------------ ---------- AVG_ROW_LEN SAMPLE_SIZE ----------- ----------- TEST                                    75253        1098                0           0 97          75253 TEST_TEMP 由此可见这个功能不是用于把一张表的统计信息给别的表 而是用于发生了进行不同统计信息的性能测试 我们再看一个实验 先备份 SQL> begin dbms_stats.drop_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); dbms_stats.create_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); --dbms_stats.export_table_stats(ownname=>'scott',tabname=>'test',stattab=>'stat_test_temp',cascade => true); --  dbms_stats.delete_table_stats(ownname=>'scott',tabname=>'test'); --dbms_stats.import_table_stats(ownname=>'scott',tabname=>'test',stattab=>'stat_test_temp'); end;  2    3    4    5    6    7 8  / PL/SQL procedure successfully completed. SQL> drop table test purge; Table dropped. SQL> create table test as select * from dba_objects; Table created. SQL> create index idx_test on test(object_id); Index created. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, sample_size from user_tables where table_name in ('TEST', 'TEST_TEMP');  2    3    4    5    6    7    8    9 TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE ------------------------------ ---------- ---------- ------------ ---------- AVG_ROW_LEN SAMPLE_SIZE ----------- ----------- TEST TEST_TEMP SQL> begin --dbms_stats.drop_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); -- dbms_stats.create_stat_table(ownname=>'scott',stattab=>'stat_test_temp'); --dbms_stats.export_table_stats(ownname=>'scott',tabname=>'test',stattab=>'stat_test_temp',cascade => true); --  dbms_stats.delete_table_stats(ownname=>'scott',tabname=>'test'); dbms_stats.import_table_stats(ownname=>'scott',tabname=>'test',stattab=>'stat_test_temp'); end;  2    3    4    5    6    7 8  / PL/SQL procedure successfully completed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, sample_size from user_tables where table_name in ('TEST', 'TEST_TEMP');  2    3    4    5    6    7    8    9 TABLE_NAME                         NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE ------------------------------ ---------- ---------- ------------ ---------- AVG_ROW_LEN SAMPLE_SIZE ----------- ----------- TEST TEST_TEMP