ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于global_stats(转)

关于global_stats(转)

原创 Linux操作系统 作者:linyuze 时间:2009-02-02 22:38:18 0 删除 编辑
Oracle reference上对于global_stats有一个这样的解释:

GLOBAL_STATS VARCHAR2(3)
-------------------------
For partitioned tables, indicates whether statistics were
collected by analyzing the table as a whole (YES) or
were estimated from statistics on underlying partitions
and subpartitions (NO)

USER_STATS VARCHAR2(3)
-----------------------
Indicates whether statistics were entered directly by the
user (YES) or not (NO)

但是在我们经常做表的分析时会看到使用dbms_stats和analyze在这个方面也会有点不同。
就是当使用dbms_stats来分析表的时候global_stats是YES,而用analyze来分析表的时候就是NO.
而且num_rows结果也会有些不同:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

SQL> create table test
  2  (id number(10),
  3   name varchar2(50));

Table created.

SQL> create index test_pk on test(id);

Index created.

SQL> begin
  2     for i in 1 .. 1000000
  3     loop
  4        insert into test values (i,'alan');
  5     end loop;
  6     commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select count(1) from test;

  COUNT(1)
----------
   1000000

尝试用dbms_stats来分析表:
--------------------------
SQL> select global_stats,user_stats,num_rows from user_tables
  2  where table_name='TEST';

GLO USE   NUM_ROWS
--- --- ----------
YESNO    1001204

SQL> select global_stats,user_stats,distinct_keys from user_indexes
  2  where table_name='TEST';

GLO USE DISTINCT_KEYS
--- --- -------------
YESNO        1000000

为什么num_rows是1001204呢?一直很疑惑......

SQL> exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'TEST');

PL/SQL procedure successfully completed.

SQL> select global_stats,num_rows from user_tables
  2  where table_name='TEST';

GLO   NUM_ROWS
--- ----------
NO

SQL> select global_stats,distinct_keys from user_indexes
  2  where table_name='TEST';

GLO DISTINCT_KEYS
--- -------------
NO

下面用analyze方法来分析一下:

SQL> select global_stats,num_rows from user_tables
  2  where table_name='TEST';

GLO   NUM_ROWS
--- ----------
NO     1000000

SQL> select global_stats,distinct_keys from user_indexes
  2  where table_name='TEST';

GLO DISTINCT_KEYS
--- -------------
NO        1000000

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

上一篇: 新年新气象
请登录后发表评论 登录
全部评论

注册时间:2009-02-01

  • 博文量
    19
  • 访问量
    20485