关于global_stats

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
--- --- ----------
YES NO     1001204

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

GLO USE DISTINCT_KEYS
--- --- -------------
YES NO        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/12361284/viewspace-227922/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12361284/viewspace-227922/