ANALYZE TABLE OR INDEX

dbms_stats.gather_table_stats和analyze table的一点细小差别

2008-03-27 15:02

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production PL/SQL Release 9.2.0.4.0 - Production CORE    9.2.0.3.0       Production TNS for Solaris: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production SQL> create table t_test3 as select * from dba_tables; Table created. SQL> analyze table t_test3 compute statistics; Table analyzed. SQL> select avg_row_len,num_rows,blocks from user_tables where table_name = 'T_TEST3'; AVG_ROW_LEN   NUM_ROWS     BLOCKS ----------- ---------- ----------         188       5502        146 SQL> analyze table t_test3 delete statistics; Table analyzed. SQL> exec dbms_stats.gather_table_stats(user,'T_TEST3'); PL/SQL procedure successfully completed. SQL> select avg_row_len,num_rows,blocks from user_tables where table_name = 'T_TEST3'; AVG_ROW_LEN   NUM_ROWS     BLOCKS ----------- ---------- ----------         183       5502        146 SQL> 默认dbms_stats.gather_table_stats分析表得到平均行长貌视少一些。 SQL> create table t_test4 as select * from t_test3; Table created. SQL> exec dbms_stats.gather_table_stats(user,'T_TEST4',estimate_percent=>100); PL/SQL procedure successfully completed. SQL> select avg_row_len,num_rows,blocks from user_tables where table_name = 'T_TEST4'; AVG_ROW_LEN   NUM_ROWS     BLOCKS ----------- ---------- ----------         183       5502        146 SQL> dbms_stats.gather_table_stats的输入参数estimate_percent为100（即100%评估），分析表得到平均行长就是准确了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8102208/viewspace-293484/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8102208/viewspace-293484/