ITPub博客

首页 > Linux操作系统 > Linux操作系统 > dbms_stats.gather_table_stats和analyze table的一点细小差别

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

原创 Linux操作系统 作者:babylonia 时间:2009-06-18 09:13:59 0 删除 编辑

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/12712263/viewspace-606653/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-31

  • 博文量
    44
  • 访问量
    41330