Import parameter: STATISTICS

 What is the new value SAFE of the STATISTICS parameter at IMPORT ?
------------------------------------------------------------------

The 4 possible values for this parameter are :
    
  -> ALWAYS : Always imports database optimizer statistics regardless of whether or not they are questionable.
                         無論是否有可疑資訊，都會匯入最佳化器的統計資訊

  -> NONE : Does not import or recalculate the database optimizer statistics.
                     不匯入或重新計算最佳化器的統計資訊

  -> SAFE : Imports database optimizer statistics back only if they are not questionable.
                    If they are questionable, recalculates the optimizer statistics.
                    如果無可疑資訊，則匯入原最佳化器統計資訊
                    如果有可疑資訊，則重新計算最佳化器的統計資訊

  -> RECALCULATE : Does not import the database optimizer statistics. Instead, recalculates them on import.
                                       不匯入最佳化器的統計資訊，在imp的時候重新收集最佳化器的統計資訊
---------------------------------------------------------------------

When are statistics questionable ?
(什麼是統計資訊可疑)
----------------------------------

The precalculated optimizer statistics are flagged as questionable at export
time if:
在exp時，如果出現以下幾種情況，那麼預先計算好的最佳化器的統計資訊被標記為可疑
   -> There are row errors while exporting
       exp時有行的錯誤
   -> The client character set or NCHAR character set does not match the server character set or NCHAR character set
      客戶端和server端的字符集不一致
   -> A QUERY clause is specified
       有查詢語句
   -> Only certain partitions or subpartitions are exported
        exp只匯出某一個分割槽或者子分割槽

e.g.
 1. 匯出使用者 ecc_view2 的dmp檔案 (製造統計資訊不是最新的情況)

 a. ECC_VIEW2 檢查統計資訊
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 -----------------   -------------------------  ---------   ---------------  ----------  -----------------------
       TEST                             USERS     VALID                  2              5  2009-6-23 13:47:05
 
 b. 重新收集統計資訊
 EXEC DBMS_STATS.gather_table_stats(ownname => 'ECC_VIEW2',tabname => 'TEST',cascade => TRUE);
 
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 -----------------   -------------------------  ---------   --------------  ---------  -----------------------
       TEST                             USERS   VALID                   5           5    2009-6-24 14:35:40
      
 c. 插入5條記錄，exp
 insert into test   
 select * from TEST      
select count(*) from test  -- 10 rows

 d. exp ecc_view2
 [oracle@rac1 ~]$ exp ecc_view2/ecc@devdb1 file=./ecc_view2.dmp ;
 . about to export ECC_VIEW2's tables via Conventional Path ...
. . exporting table                           TEST         10 rows exported
Export terminated successfully without warnings.

 2. 建立使用者 ecc_view3

create user ecc_view3
identified by ecc
default tablespace DATA03

grant connect, resource to ecc_view3

 3. 匯入資料到ecc_view3
 a. statistics = ALWAYS (default)
[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                         "TEST"         10 rows imported
Import terminated successfully without warnings.
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 ------------------   -----------------------     ------             --------     ------    -----------------------
      TEST                            USERS       VALID                5           5      2009-6-24 14:42:44
 
 b. statistics = none      
 drop table test
 [oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3 statistics=none
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 ----------   ------------------------------       ------       -------------    ------  -----------------------
 TEST                           USERS           VALID   
 
 c. statistics = safe      
 drop table test
 [oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3 statistics=safe
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks               last_analyzed
 ---------------   ---------------------------  ------               --------       ------          -----------------------
      TEST                         USERS     VALID                    10          5        2009-6-24 14:54:51

 d. statistics = RECALCULATE      
 drop table test
 [oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view2.dmp fromuser=ecc_view2 touser=ecc_view3 statistics=RECALCULATE;
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name   status   num_rows  blocks  last_analyzed
 -----------------               ----------------  --------        ------------      ------  ---------------------------
     TEST                                USERS  VALID                    10         5   2009-6-24 14:56:03

 

參考文獻：
Subject:  Import parameter: STATISTICS=SAFE
  Doc ID:  159787.1 Type:  BULLETIN
  Modified Date :  11-NOV-2008 Status:  PUBLISHED